The Leaderboard Problem: Ranking Top Sellers by GMV, Orders & Units Using SQL Window Functions

Business Context

PeakCart is a multi-category e-commerce marketplace operating across five product categories — Electronics, Apparel, Home & Garden, Beauty & Personal Care, and Sports & Outdoors — with over 320,000 monthly active buyers and approximately $8.2M in monthly GMV. The merchandising team runs a quarterly product review where category managers need to identify their top-performing products across three distinct performance dimensions: Gross Merchandise Value (GMV), number of orders, and units sold. These three rankings often tell very different stories — a high-ticket Electronics item may dominate GMV while a low-cost Apparel item leads on units. Without a unified ranked view, category managers are stitching together separate reports and missing cross-metric insights. The analysis period is Q2 2024 (April 1 – June 30).

The SQL Challenge

The database stores order line items in an order_items table, capturing the product, category, quantity, and revenue per line. Your task is to first aggregate total GMV, total orders, and total units per product per category, and then apply window functions — specifically RANK() OVER (PARTITION BY category ORDER BY metric DESC) — to assign independent rank numbers within each category for each of the three metrics. This allows a single query to return every product's rank across all three dimensions simultaneously, without requiring three separate queries or self-joins.

Your Role and Deliverable

You are the data analyst supporting PeakCart's Merchandising team. The Head of Merchandising has asked you to produce a query that outputs one row per product, showing its category, total GMV, total orders, total units sold, and its rank within its category for each of the three metrics. The final output should be filtered to show only products ranked in the top 5 within their category on at least one metric, sorted by category and GMV rank. This will feed directly into the quarterly product review deck used by all five category managers.