Product Revenue Rankings: Identifying Winners & Losers Using RANK()

Business Context

ShelfSmart Retail is a national retail chain operating 62 stores across the US, carrying over 200 active SKUs across categories including Electronics, Apparel, Groceries, and Home & Kitchen. With $5.8M in monthly revenue, the merchandising team runs a quarterly portfolio review to decide which products deserve more shelf space, promotional spend, and reorder priority — and which products should be discontinued or replaced. For the current review covering Q1 2024 (January through March), the Head of Merchandising needs a ranked list of products sorted by total revenue generated, with clear identification of the Top 10 revenue drivers and the Bottom 10 underperformers.

The SQL Challenge

The core technique here is the RANK() window function combined with PARTITION BY and ORDER BY inside an OVER() clause to assign revenue-based rankings across all products. Because RANK() is a window function, it cannot be filtered directly in a WHERE clause — you'll need to wrap the ranking logic inside a CTE (Common Table Expression) or subquery, then filter on the rank value in an outer query. Supporting skills include SUM() aggregation to compute total revenue per product, GROUP BY on product dimensions, and combining two filtered result sets using UNION ALL to produce the final top-and-bottom output in one clean result.

Your Role and Deliverable

You are the data analyst supporting the merchandising team. Your task is to write a SQL query that returns exactly 20 rows — the Top 10 and Bottom 10 products by Q1 2024 revenue — each labeled with their rank, a performance_tier flag ('Top 10' or 'Bottom 10'), and key metrics including total revenue, units sold, and transaction count. The output will be reviewed directly by the Head of Merchandising in the quarterly portfolio meeting.