Drill 7 of 12 in Marketing questions
0

Interview Question Bank · date_trunc('week') · LAG · PARTITION BY

7. Week-over-week signups by channel

MediumMarketingQuestion #175

Concept: date_trunc('week') · LAG · PARTITION BYshow

Week-over-week change needs each week's count next to the previous week's for the same series. Bucket rows with date_trunc('week', d), which snaps every date to its Monday, count per bucket, then read the prior bucket with LAG(count) OVER (PARTITION BY series ORDER BY week). The first week has nothing before it, so its change is NULL.

Example (different data):

WITH w AS (
  SELECT store, date_trunc('week', sold_on)::date AS week, COUNT(*) AS sales
  FROM sales GROUP BY 1, 2
)
SELECT store, week, sales,
  LAG(sales) OVER (PARTITION BY store ORDER BY week) AS prev_sales
FROM w;

Your task

Larkspur Roasters' growth stand-up opens with signups by acquisition channel for each week of March 2025 and how each moved against the week before.

For each channel and week (the Monday it starts on, as a date), return channel, week_start, signups, prev_week_signups (the same channel's previous week, NULL for its first week) and wow_change_pct (100 * (signups - prev_week_signups) / prev_week_signups, rounded to 1 decimal). Sort by channel, then week_start.

Starting Postgres in your browser…

Checking your access…