iOS Version Adoption
ASKED IN INTERVIEWApple
Problem Statement
Apple software engineers need to know the adoption rate of various iOS versions to plan the rollout of new features.
Write an SQL query to find each iOS version and the percentage of the total fleet running that version.
Rules:
- The percentage should be calculated as: (Number of devices on version / Total number of devices) * 100.
- Round the percentage to 1 decimal place.
- Output columns: ios_version, adoption_rate.
- Results must be sorted by adoption_rate in descending order, then ios_version in descending order.
Tests your understanding of
Aggregation, Window Functions and Percentages
Input Tables
OS_Stats
| device_id(INTEGER) | ios_version(VARCHAR) |
|---|---|
| 1 | 17.0 |
| 2 | 17.0 |
| 3 | 16.5 |
| 4 | 17.0 |
| 5 | 15.0 |
Expected Output
| ios_version(VARCHAR) | adoption_rate(DECIMAL) |
|---|---|
| 17.0 | 60 |
| 16.5 | 20 |
| 15.0 | 20 |
Tags
MediumASKED IN INTERVIEWAggregationWindow FunctionsPercentages
10-15 min
75%