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.
Requirements
- Join
service_usage to digital_services and aggregate revenue by region, month, and service.
- Use
DENSE_RANK to rank services by monthly revenue within each region and month, preserving ties.
- Use
ROW_NUMBER to provide a deterministic position within each region and month, ordering tied services by service name.
- Return services in the top two dense-rank levels, ordered by region, month, and row number. Treat missing revenue as zero.
Output details
- Return columns in this order:
region, month_start, service_name, total_revenue, dense_rank, row_number.
- Represent
month_start as the first calendar day of each month.
- Rank higher
total_revenue values first, with descending order for dense_rank.
- Order
row_number by descending total_revenue, then ascending service_name, then ascending service_id.
- Sort final results by ascending
region, ascending month_start, then ascending row_number.
