Objective

Write a SQL query to find the top 2 highest-grossing products within each category for the year 2022.

Table Schema:

• product_spend

Task Requirements:

• Filter transactions for the year 2022
• Calculate total spend for each product within each category
• Identify the top 2 products by total spend in each category
• Order results by category and total spend (descending)
  
  

Output columns: category, product, total_spend

SELECT 
    category,
    product,
    total_spend
FROM (
    SELECT 
        category,
        product,
        SUM(spend) AS total_spend,
        RANK() OVER (PARTITION BY category ORDER BY SUM(spend) DESC) AS ranking
    FROM product_spend
    WHERE EXTRACT(YEAR FROM transaction_date) = 2022
    GROUP BY category, product
) AS ranked_products
WHERE ranking <= 2
ORDER BY category, total_spend DESC;

Explanation

Step 1: Filtering for 2022 Transactions

WHERE EXTRACT(YEAR FROM transaction_date) = 2022

Filters to only transactions that occurred in 2022.

Step 2: Aggregating Spend by Product

SUM(spend) AS total_spend
GROUP BY category, product

Groups transactions by category and product; SUM(spend) calculates total revenue per product in 2022.

Step 3: Ranking Products Within Categories

RANK() OVER (PARTITION BY category ORDER BY SUM(spend) DESC) AS ranking

RANK() assigns ranks within each category by total spend descending. Products with identical spend receive the same rank.

Step 4: Filtering for Top 2

WHERE ranking <= 2

Keeps only the top 2 highest-grossing products per category.

Step 5: Ordering the Final Output

ORDER BY category, total_spend DESC

Sorts alphabetically by category, then by total spend descending within each category.