Window Functions for Top Records

Virtusa's delivery portfolio team needs to identify the highest-performing services within selected categories. Write a PostgreSQL query that aggregates completed sales and ranks services independently within the Cloud and Data & AI categories.

Requirements

1. Include every service in the selected categories, including services with no completed sales.
2. Calculate total completed sales revenue per service, treating missing revenue as zero and excluding cancelled sales.
3. Use a window function to rank services within each category and return services with rank 1 or 2. Preserve ties at the rank boundary.
4. Sort the output by category, rank, total revenue descending, and service name.

Output details

• Return columns in this order: category, product_name, total_revenue, and performance_rank.
• Use competition ranking with gaps, assigning equal revenues the same performance_rank.
• Sort category, performance_rank, and product_name ascending, while sorting total_revenue descending.
• Represent services without completed sales with numeric total_revenue equal to 0.00.