Data Analyst Interview Questions & Answers: Set 2, Retail & Supermarket Chain

SQL

1. 1.Write a query to find the top 3 best-selling products in each store by quantity.

   Hide answer

   Answer: Aggregate quantity per store and product first, then rank products inside each store with a window function and keep ranks 1 to 3. Assumed table: sales(store_id, product_id, sale_date, quantity). I use DENSE_RANK so tied products both appear; use ROW_NUMBER if you need exactly 3 rows per store. Trap: you cannot filter on a window function in WHERE of the same SELECT, so wrap it in a CTE or subquery.

   WITH product_qty AS (
     SELECT store_id, product_id, SUM(quantity) AS total_qty
     FROM sales
     GROUP BY store_id, product_id
   ),
   ranked AS (
     SELECT *,
            DENSE_RANK() OVER (PARTITION BY store_id
                               ORDER BY total_qty DESC) AS rnk
     FROM product_qty
   )
   SELECT store_id, product_id, total_qty, rnk
   FROM ranked
   WHERE rnk <= 3
   ORDER BY store_id, rnk;

2. 2.Calculate each store's month-over-month sales growth using a window function.

   Hide answer

   Answer: Roll sales up to store-month, then use LAG() to bring the previous month's sales onto the same row and compute (this - previous) / previous. Assumed table: sales(store_id, sale_date, amount). NULLIF avoids division by zero, and the first month of each store has NULL growth. Trap: if a store has a missing month (e.g. closed for renovation), LAG compares non-adjacent months, so join to a calendar of months first if gaps are possible.

   WITH monthly AS (
     SELECT store_id,
            DATE_TRUNC('month', sale_date) AS month,
            SUM(amount) AS sales
     FROM sales
     GROUP BY store_id, DATE_TRUNC('month', sale_date)
   )
   SELECT store_id, month, sales,
          LAG(sales) OVER w AS prev_sales,
          ROUND(100.0 * (sales - LAG(sales) OVER w)
                / NULLIF(LAG(sales) OVER w, 0), 1) AS mom_growth_pct
   FROM monthly
   WINDOW w AS (PARTITION BY store_id ORDER BY month)
   ORDER BY store_id, month;