SQL enough to answer your own question
Write the three queries behind a weekly readout — daily actives, funnel step conversion and week-N retention — from one events table, and read what each result rules out.
On this page
Three questions account for most of what a PM sends to an analyst: how many people used it today, where in the funnel are they lost, and do they come back. Each is one query against one events table, and together they need GROUP BY, date_trunc, a conditional count and one window function. A PM who can write them gets the answer in ten minutes instead of two days, and more usefully, learns to ask the question precisely enough that the query is short.
One table
Everything below runs against a table of events, one row per thing a user did:
events(user_id, tenant_id, event, ts)
-- event is one of: signup, helpdesk_connected, draft_shown, draft_sent, reopen
-- ts is a timestamp; one row per occurrence, never updated
The shape is the point. An events table is append-only, so a number computed from it today is the same number tomorrow; a users table with a last_active column that gets overwritten cannot answer "how many were active on the 3rd" once the 4th has happened. If the product does not log events this way, the first product decision is to make it do so, and the queries below are the specification: every event name here must exist, with the meaning given.
Query one: daily actives
"Active" must be defined before it can be counted. Here it is "sent at least one draft", because an agent who opened the tool and sent nothing has not used the product. COUNT(DISTINCT user_id) makes an agent who sent forty drafts count once; date_trunc folds timestamps to the day; GROUP BY produces one row per day.
SELECT date_trunc('day', ts) AS day,
COUNT(DISTINCT user_id) AS active_agents
FROM events
WHERE event = 'draft_sent'
AND ts >= date '2026-09-01'
GROUP BY 1
ORDER BY 1;
day active_agents
2026-09-01 1,870
2026-09-02 1,910
2026-09-03 1,320 <- a Thursday, down 31%
2026-09-04 1,880
The result marks the answer: the 3rd is the row to explain. The query that explains it is the same one with tenant_id added to the SELECT and the GROUP BY; if one tenant with 600 agents shows zero on the 3rd, the drop is their outage, not a product change. The weekly version replaces 'day' with 'week', and the North Star readout is a weekly count of unedited draft_sent rows with no reopen on the same ticket within seven days, which this table cannot produce until it logs a ticket_id and an edited flag.
Query two: funnel step conversion
The AARRR funnel counts teams, so it needs one row per team (tenant_id) with a flag per step, then a sum of the flags. The conditional MAX(CASE …) does the first part: it turns many event rows into one 0-or-1 per step per team. The outer query sums the flags and divides.
WITH steps AS (
SELECT tenant_id,
MAX(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) AS signed_up,
MAX(CASE WHEN event = 'helpdesk_connected' THEN 1 ELSE 0 END) AS connected,
MAX(CASE WHEN event = 'draft_sent' THEN 1 ELSE 0 END) AS sent_draft
FROM events
WHERE ts >= date '2026-07-01' AND ts < date '2026-10-01'
GROUP BY tenant_id
)
SELECT SUM(signed_up) AS signed_up,
SUM(connected) AS connected,
SUM(sent_draft) AS sent_draft,
ROUND(100.0 * SUM(connected) / SUM(signed_up), 1) AS connect_pct,
ROUND(100.0 * SUM(sent_draft) / NULLIF(SUM(connected), 0), 1) AS draft_pct
FROM steps;
signed_up connected sent_draft connect_pct draft_pct
10,000 5,000 4,000 50.0 80.0
This is the question the AARRR lesson left open: of the 6,000 teams that never sent a draft, 5,000 never connected a help desk. The step to fix is the integration, and the result says so without an analyst.
Two things the query does not do. It does not check order: a team that sent a draft before connecting (a demo account, say) still counts at every step. A strict funnel compares timestamps, MIN(ts) FILTER (WHERE event = 'draft_sent') > MIN(ts) FILTER (WHERE event = 'helpdesk_connected'), and is worth writing once the loose version has found the step. And NULLIF guards the division: with no connected teams the loose query divides by zero and the whole readout fails, which is the kind of failure that gets noticed at 9 am on a Monday.
Query three: week-N retention
Retention asks, for each user, how many weeks after their first draft they were still sending drafts. The first draft is a per-user fact that has to sit on every one of that user's rows so each row can be dated relative to it, and that is what a window function does: MIN(ts) OVER (PARTITION BY user_id) computes the minimum per user without collapsing the rows the way GROUP BY would.
WITH dated AS (
SELECT user_id, ts,
MIN(ts) OVER (PARTITION BY user_id) AS first_ts
FROM events
WHERE event = 'draft_sent'
),
weeks AS (
SELECT date_trunc('week', first_ts) AS cohort,
FLOOR(EXTRACT(epoch FROM ts - first_ts) / 604800) AS week_n,
user_id
FROM dated
)
SELECT cohort, week_n, COUNT(DISTINCT user_id) AS active
FROM weeks
GROUP BY 1, 2
ORDER BY 1, 2;
cohort week_n active
2026-08-03 0 1,000
2026-08-03 1 620
2026-08-03 2 510
2026-08-03 3 460
2026-08-10 0 1,200
2026-08-10 1 720
...
Week 0 is the cohort's size by construction, since every user sent a draft in the week of their first draft, so week-N retention is active divided by the cohort's week-0 row: 62%, 51%, 46% for the 3 August cohort. 604,800 is seconds in a week, 7 × 86,400, the same constant the estimation lessons round to 100,000 a day. date_trunc('week', …) starts weeks on Monday in Postgres; other warehouses spell the function differently and may start on Sunday, and a retention grid compared across two warehouses with different week starts is two different grids.
Turning the long result into the grid a reader can scan is the cohorts lesson, and the retention exercise does the same calculation in Python, where the ragged right edge of the grid has to be handled explicitly.
The questions you stop sending to an analyst
With these three, the questions that become self-service are: how many used it today, this week, by tenant; how many reached each step and where they stopped; whether a cohort that signed up after a change behaves differently from one before it. The questions that still go to an analyst are the ones with a join across three systems, a definition nobody has agreed, or a result that will be quoted to a customer. The analyst's time is for those, and asking for a daily-active count is spending it on a GROUP BY.
The discipline that matters more than the syntax is writing the definition into the WHERE clause and saying it aloud when the number is quoted: "active means sent a draft; 1,870 agents on the 2nd". A number without its definition is the start of a meeting about what the number means.
In an interview
An execution interviewer who hears "I'd ask the data team" has learned that you cannot check a hypothesis yourself. Say instead what table you would query, which event defines the metric, and what the first GROUP BY would be: "daily actives by tenant, where active is draft_sent; if one tenant went to zero it is theirs, if all tenants fell 30% it is ours". That sentence shows the segmentation tree and the instrument at once.
The mistake that loses points is counting rows instead of users — COUNT(*) on draft_sent is drafts, not agents, and it doubles when one agent sends twice — or quoting a rate without its definition. "Retention is 46%" invites "of whom, at what week, measured on which cohort?"; have those three ready.
Check yourself
1. Daily actives fell 31% on one day and recovered the next. Which single column do you add to the query first, and what result would end the investigation?
tenant_id, toSELECTandGROUP BY. If one tenant's count is zero for that day and the rest are flat, it is an outage on their side or an integration failure on ours, and the investigation becomes a support ticket. If every tenant fell about 30%, it is a product or infrastructure change and the next column is the client version.
2. The loose funnel says 80% of connected teams sent a draft. The strict, timestamp-ordered version says 71%. Which do you report?
The strict one, 71%, with a note that 9 points of teams sent a draft before connecting, which is a demo path and not the product. The loose number overstates the step; reporting it means the real integration problem is 9 points larger than the readout shows.
3. A tenant with 600 agents wants week-4 retention for the cohort that joined last Monday. What do you tell them?
That the number does not exist yet: the cohort reaches week 4 in four weeks. Offer week-0 and week-1 as soon as they close, and the week-4 figure for the cohort that joined five weeks ago as the current best estimate, with the caveat that anything shipped since then is not in it.