Write a query to find the top-selling product in each store location for the current quarter.

Answer

Sum sales per store and product for the current quarter, then rank products within each store and keep the top-ranked one.

Solution

SELECT store_id, product_id, total_sales
FROM (
  SELECT store_id, product_id, SUM(amount) AS total_sales,
         RANK() OVER (PARTITION BY store_id ORDER BY SUM(amount) DESC) AS rnk
  FROM Sales
  WHERE sale_date >= DATE_TRUNC('quarter', CURRENT_DATE)
  GROUP BY store_id, product_id
) x
WHERE rnk = 1;

Step by Step

1. WHERE sale_date >= DATE_TRUNC('quarter', CURRENT_DATE) restricts the data to only sales within the current quarter.
2. Grouping by store_id, product_id gives each product's total sales at each store during that period.
3. RANK() OVER (PARTITION BY store_id ORDER BY SUM(amount) DESC) ranks products within each store separately, and filtering to rnk = 1 keeps each store's single top-selling product.

Handling Ties Explicitly

Using RANK() rather than ROW_NUMBER() here is deliberate: if two products tie exactly for the highest sales at one store, RANK() returns both (both get rank 1), correctly reflecting the tie, while ROW_NUMBER() would arbitrarily pick just one and hide the tie entirely.

Key Exam Traps

• DATE_TRUNC('quarter', ...) syntax varies by engine - some databases require an explicit quarter-boundary calculation instead of a built-in DATE_TRUNC function; know the equivalent for whichever engine is specified.
• "Current quarter" is a moving target - this query's result changes depending on when it's run; if the requirement is actually a specific fixed quarter (like "Q1 2025"), an explicit date range should be used instead of a relative CURRENT_DATE-based calculation.
• Stores with no sales yet this quarter simply don't appear in the result at all, since there's nothing to group - this is usually the correct behavior, but worth confirming if the business wants every store listed regardless.