Drill 3 of 3 in Retail questions
0

Interview Question Bank · Aggregate, then window · SUM(SUM()) OVER · PARTITION BY year

3. Weekly sales with a year-to-date running total

MediumRetailQuestion #281

Concept: Aggregate, then window · SUM(SUM()) OVER · PARTITION BY yearshow

A running total is SUM(x) OVER (PARTITION BY ... ORDER BY time), and the PARTITION BY decides when it restarts: add the year to start over every January. If the table has several rows per period (one per department, say), total them with GROUP BY first; the window can then run over the grouped sum directly, as SUM(SUM(x)) OVER (...).

Example (different data):

SELECT clinic, visit_month, COUNT(*) AS visits,
  SUM(COUNT(*)) OVER (
    PARTITION BY clinic, EXTRACT(YEAR FROM visit_month)
    ORDER BY visit_month) AS visits_ytd
FROM clinic_visits
GROUP BY clinic, visit_month;

Your task

Hearthline Stores records weekly sales per department. Finance wants each store's weekly total with a year-to-date figure that starts over in the first week of the new year. A week belongs to the year its week_start falls in.

For every store and week in rt_weekly_dept_sales, return store_name, week_start, weekly_sales (all departments added) and ytd_sales (the store's running total of weekly_sales within week_start's calendar year, in week order). Sort by store_name, then week_start.

Starting Postgres in your browser…

Checking your access…