Drill 2 of 3 in Retail questions
0

Interview Question Bank · RANK over a grouped total · year filter

2. Top store in each region, ties included

MediumRetailQuestion #280

Concept: RANK over a grouped total · year filtershow

To pick the best row per group, total first, then rank inside each group with RANK() OVER (PARTITION BY group ORDER BY total DESC) and keep rank 1. RANK lets two equal totals both be first, while ROW_NUMBER, DISTINCT ON and LIMIT keep exactly one and drop the tie arbitrarily. Restrict the period in WHERE before you total, or older years leak into the ranking.

Example (different data):

WITH t AS (
  SELECT department, rep, SUM(deal_value) AS booked,
    RANK() OVER (PARTITION BY department ORDER BY SUM(deal_value) DESC) AS r
  FROM deals
  WHERE closed_on >= DATE '2025-01-01'
  GROUP BY department, rep
)
SELECT department, rep, booked FROM t WHERE r = 1;

Your task

Hearthline Stores names a store of the year in every region based on 2024 sales. If two stores tie, both get the award.

Return region, store_name and sales_2024 (the store's total sales across 2024's quarters) for the store or stores with the highest 2024 sales in each region. Sort by region, then store_name.

Starting Postgres in your browser…

Checking your access…