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 |