Your question is Top Digital Services Per Region. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Apple wants to compare the monthly performance of digital services such as Apple Music, Apple TV+, and iCloud+ across customer regions. Write a PostgreSQL query that aggregates service revenue by month and identifies the highest-performing services within each region and month.
service_usage to digital_services and aggregate revenue by region, month, and service.DENSE_RANK to rank services by monthly revenue within each region and month, preserving ties.ROW_NUMBER to provide a deterministic position within each region and month, ordering tied services by service name.| Column | Type | Description |
|---|---|---|
| service_idPK | INT | Unique Apple digital service identifier |
| service_name | VARCHAR(100) | Apple digital service name |
| service_category | VARCHAR(50) | Category of the digital service |
| Column | Type | Description |
|---|---|---|
| usage_idPK | INT | Unique usage record identifier |
| service_id | INT | References digital_services.service_id |
| region | VARCHAR(50) | Customer geographic region |
| usage_date | DATE | Date associated with usage revenue |
| revenue | NUMERIC(12,2) | Revenue attributed to the service usage |