Interviewers who hire for B2B SaaS rarely want you to recite GROUP BY. They put a subscriptions, events or invoices table in front of you and ask for retention, ranked plans or day-over-day active users. Window functions are the difference between a brittle subquery nest and a readable answer that looks like production analytics SQL.

I run Loop Origin, a software company that builds custom web applications for product teams. I wrote this with help from AI tools and checked every command and claim myself. The examples below use PostgreSQL shapes I see in real multi-tenant apps: tenant_id, subscription periods and product analytics events.

Which SQL window functions should you know for SaaS metrics interviews?

Direct answer: Master ROW_NUMBER, RANK/DENSE_RANK, LAG/LEAD, running aggregates with SUM() OVER, and partitioned frames for per-tenant or per-cohort metrics. Practice DAU, month-over-month revenue and a simple retention sketch on schemas with tenants, subscriptions and events—then explain when a window beats GROUP BY.

Mental model: windows vs aggregates

GROUP BY collapses rows. A window keeps every row and adds a calculated column computed over a related set of rows (the frame), optionally partitioned and ordered.

Need Prefer GROUP BY Prefer window
One total per tenant Yes Overkill
Rank plans inside each tenant Awkward RANK() OVER (PARTITION BY tenant_id ORDER BY mrr DESC)
Previous month's MRR on the same row Self-join hell LAG(mrr) OVER (PARTITION BY tenant_id ORDER BY month)
Running revenue to date Nested aggregates SUM(amount) OVER (ORDER BY day)
Keep detail rows and totals Two queries One query with window

Weak interview answer: "I would pull everything into pandas." Strong answer: show the SQL, name the partition key, state the frame, and call out duplicates or ties.

Schema sketch (keep this on the whiteboard) 

-- simplified SaaS core CREATE TABLE tenants ( id uuid PRIMARY KEY, name text NOT NULL ); CREATE TABLE subscriptions ( id uuid PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES tenants(id), plan text NOT NULL, mrr_cents int NOT NULL, status text NOT NULL, started_on date NOT NULL, ended_on date ); CREATE TABLE events ( id bigserial PRIMARY KEY, tenant_id uuid NOT NULL, user_id uuid NOT NULL, event_name text NOT NULL, occurred_at timestamptz NOT NULL );

Interviewers care that you partition by tenant_id when the metric is per customer, and that you do not accidentally rank globally when the story is "within each account."

ROW_NUMBER, RANK and DENSE_RANK

Use these when the prompt is "top N plans," "latest subscription per tenant," or "dedupe."

-- latest subscription row per tenant SELECT * FROM ( SELECT s.*, ROW_NUMBER() OVER ( PARTITION BY tenant_id ORDER BY started_on DESC, id DESC ) AS rn FROM subscriptions s ) t WHERE rn = 1;

Tie-breaking:

  • ROW_NUMBER always unique ranks (arbitrary among ties unless you add more ORDER BY keys).
  • RANK leaves gaps after ties (1,1,3).
  • DENSE_RANK does not (1,1,2).

Strong answer mentions the tie-break columns. Weak answer shrugs at duplicate started_on values.

LAG / LEAD for period-over-period SaaS metrics 

WITH monthly AS ( SELECT tenant_id, date_trunc('month', started_on)::date AS month, SUM(mrr_cents) AS mrr_cents FROM subscriptions WHERE status IN ('active', 'past_due') GROUP BY 1, 2 ) SELECT tenant_id, month, mrr_cents, LAG(mrr_cents) OVER ( PARTITION BY tenant_id ORDER BY month ) AS prev_mrr, mrr_cents - LAG(mrr_cents) OVER ( PARTITION BY tenant_id ORDER BY month ) AS mrr_delta FROM monthly ORDER BY tenant_id, month;

Say out loud: first month per tenant yields NULL previous MRR; that is correct, not a bug. Offer COALESCE only if the business defines "new logo starts at zero baseline."

Running totals and frames 

SELECT occurred_at::date AS day, COUNT(*) AS events_that_day, SUM(COUNT(*)) OVER ( ORDER BY occurred_at::date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS events_to_date FROM events WHERE event_name = 'api.request' GROUP BY 1 ORDER BY 1;

Default frame for ORDER BY without an explicit frame is often a range up to the current peer group—know that peers with the same day can surprise you. Prefer explicit ROWS BETWEEN in interviews when you discuss cumulative sums.

Printed analytics charts and spreadsheet pages on a wooden desk in soft window light, representing SaaS metrics work
AI-generated image.

Daily active users with windows

Classic prompt: DAU and day-over-day change.

WITH dau AS ( SELECT occurred_at::date AS day, COUNT(DISTINCT user_id) AS dau FROM events WHERE event_name = 'session.start' GROUP BY 1 ) SELECT day, dau, LAG(dau) OVER (ORDER BY day) AS prev_dau, ROUND( 100.0 * (dau - LAG(dau) OVER (ORDER BY day)) / NULLIF(LAG(dau) OVER (ORDER BY day), 0), 1 ) AS pct_change FROM dau ORDER BY day;

Strong follow-up: "Should DAU be global or per tenant?" For B2B, show both—a global chart for the company and a partitioned version for customer success:

COUNT(DISTINCT user_id) OVER ( PARTITION BY tenant_id, occurred_at::date )

is illegal as written because DISTINCT inside a window aggregate is not supported in PostgreSQL. Instead aggregate first, then window—exactly as the CTE pattern above. Calling that limitation out scores points.

Cohort retention sketch (interview-sized)

Full retention pipelines are dbt models. In a 45-minute screen, a sketch is enough:

WITH first_seen AS ( SELECT user_id, MIN(occurred_at::date) AS cohort_day FROM events WHERE event_name = 'signup.completed' GROUP BY 1 ), activity AS ( SELECT DISTINCT e.user_id, e.occurred_at::date AS active_day FROM events e WHERE e.event_name = 'session.start' ) SELECT f.cohort_day, (a.active_day - f.cohort_day) AS day_n, COUNT(DISTINCT a.user_id) AS users FROM first_seen f JOIN activity a ON a.user_id = f.user_id WHERE a.active_day BETWEEN f.cohort_day AND f.cohort_day + 30 GROUP BY 1, 2 ORDER BY 1, 2;

Then explain how you would turn day_n into a retention % by dividing by cohort size using a window:

COUNT(DISTINCT a.user_id) * 1.0 / FIRST_VALUE(COUNT(DISTINCT a.user_id)) OVER ( PARTITION BY f.cohort_day ORDER BY (a.active_day - f.cohort_day) )

is the wrong shape—aggregate in a CTE first, then apply MAX(users) FILTER (WHERE day_n = 0) OVER (PARTITION BY cohort_day). Showing that you know to separate aggregation from windowing is the senior signal.

I cover adjacent product and platform topics in Nuxt 3 rendering for B2B SaaS and replatforming without losing SEO; metrics literacy is what lets engineering and growth argue from the same tables.

Weak vs strong interview answers

Prompt Weak Strong
Top plan per tenant GROUP BY losing columns, then vague join DISTINCT ON (tenant_id) or ROW_NUMBER with explicit order
MoM growth Export CSV, "I'd chart it" LAG on monthly CTE, discuss NULL first period
Retention "Use Mixpanel" only Cohort CTE + day_n + clear definition of active
Performance Ignore indexes Note (tenant_id, occurred_at) and selective event_name filters
Ties Ignore Add id to ORDER BY, pick RANK vs ROW_NUMBER deliberately

Interview prep checklist

  • [ ] Explain partition vs order vs frame in one sentence each
  • [ ] Write ROW_NUMBER dedupe and justify tie-breakers
  • [ ] Write LAG MoM on a monthly MRR CTE
  • [ ] Write DAU with pct change and mention the DISTINCT limitation
  • [ ] Sketch 7/30-day retention with cohort_day
  • [ ] Compare one query that needs GROUP BY only vs one that needs a window
  • [ ] Name indexes you would want on events(tenant_id, occurred_at)
  • [ ] State definitions: DAU user key, active subscription filter, timezone (UTC)

Frequently asked questions

Do I need window functions if I know subqueries well?

Correlated subqueries can emulate some windows but get expensive and hard to read. Interviewers increasingly expect OVER clauses for ranking and period-over-period metrics.

What is the most common mistake on PARTITION BY?

Partitioning by the wrong grain—ranking globally when the story is per tenant, or partitioning by day when you meant per user. Say the grain before you write the clause.

Does ORDER BY inside OVER sort the final output?

No. It defines the window calculation order. Add an outer ORDER BY for the result set.

How do I practice with realistic data?

Generate a few thousand event rows with multiple tenants and ask yourself for DAU, top users and retention. Production-shaped nulls and duplicate timestamps matter more than volume.

Are window functions slow?

They can be, on huge unsorted scans. They are often faster than equivalent self-joins when indexes match the partition and order keys. Always check EXPLAIN.

Should product metrics live only in a warehouse?

Warehouses are right for heavy history. Product engineers still need window fluency for operational SQL, support investigations and interviews. For broader platform work we do under custom web application development, the same fluency shows up when APIs expose admin analytics.

Master the five patterns above until you can write them without scrolling docs. That is enough to clear most SaaS backend and data-aware full-stack interviews—and enough to review a metrics PR without guessing.

Follow & Connect