sql-queries

name	sql-queries
description	Translates a product question into clean, correct SQL against a described schema and explains it line by line. Covers the core product-analytics patterns — funnels, retention and cohorts, DAU/MAU stickiness, and event counts — plus how to sanity-check the output. Use when you say "write me a query for", "how many users did X then Y", "what's our 7-day retention", "DAU/MAU by week", "build a funnel from signup to activation", or "is this number right?"
tier	quick
time	10-20 min
inputs	the question, and your table/column names
outputs	metrics/queries/<question>.sql

SQL Queries

Turn a product question into trustworthy SQL using a small set of battle-tested product-analytics patterns. Most product questions reduce to one of four shapes — funnel, retention/cohort, active-user ratio, or event count — and getting the shape right matters more than clever syntax.

Grounded in: Lean Analytics — Croll & Yoskovitz: answer the product question (funnels, retention windows, DAU/MAU) — correctly, not approximately. Go deeper (The Product Channel): Product Metrics

When to use this

• A PM or analyst asks "how many users went from signup to first key action, and where do they drop off?"
• You need 1/7/30-day retention or a weekly cohort triangle and want it correct, not approximately correct.
• You want DAU, WAU, MAU, or the DAU/MAU stickiness ratio over time.
• You need to count events or unique users per feature, segment, or time bucket — and trust the number.
• A dashboard figure looks off and you want a query plus a sanity-check protocol to confirm or debunk it.

Before you start (gather these)

• The question, stated as a metric — e.g. "7-day retention of users who signed up in May," not "how's retention."
• The schema — table names, key columns, and grain. At minimum: the events/activity table, its user-id column, its timestamp column, and how an "event type" is identified.
• The SQL dialect — Postgres, BigQuery, Snowflake, Redshift, MySQL, DuckDB. Date functions differ; this matters.
• Definitions — what counts as "active," "activated," "retained"? What's the time grain (day/week/month) and timezone?

If two or more of these are missing or vague, ASK 2–4 sharp questions before writing SQL (e.g. "Which table holds events and what's the user-id column?", "Postgres or BigQuery?", "Is retention day-N exact, or N-day-window?", "What event defines activation?"). If the schema and question are already provided, proceed and state every assumption inline as a -- comment in the query.

Process

1. Classify the shape. Decide which of the four patterns fits: funnel, retention/cohort, active-user ratio, or event count. Say which one and why. Most questions are exactly one; some compose two.
2. Pin the grain and the definitions. One row per what? (user, user-day, cohort-week.) Write the definition of the metric in one sentence before any SQL.
3. Anchor the population. Define the cohort/denominator first — the set of users in scope and their anchor date (signup, first event). Retention and funnels live or die on a correct denominator.
4. Handle time deliberately. Truncate to the grain (date_trunc), be explicit about timezone (e.g. DATE_TRUNC('day', event_time AT TIME ZONE 'America/New_York') to bucket by account-local days instead of UTC), and use half-open intervals [start, end) to avoid double-counting boundary rows. 4b. Exclude incomplete cohorts. For any windowed/day-N metric, drop cohorts whose window hasn't fully elapsed — WHERE cohort_start + window <= now() (use the window END for ranges). A cohort from 3 days ago can't have day-7 retention yet; including it silently understates the number.
5. De-dupe to the grain. Use COUNT(DISTINCT user_id) for people, raw COUNT(*) for events — never confuse the two. Collapse multiple events per user per period before joining.
6. Write the query with CTEs, not nesting. One named CTE per logical step (cohort → activity → join → aggregate). Readable beats clever.
7. Sanity-check before you trust it. Run the checks in the template: row counts, denominator size, a spot-check on one known user, and monotonicity (retention and funnels only ever decrease).
8. Explain it. Two or three sentences: what each CTE does, what one row of output means, and the one assumption most likely to be wrong.

Output template

## Question
[Restate the product question as a precise metric, with grain and timezone.]

## Shape
[Funnel | Retention/Cohort | Active-user ratio | Event count] — [one line: why this shape.]

## Assumptions
- Dialect: [Postgres | BigQuery | Snowflake | …]
- Events table: `[table]`, user id: `[col]`, timestamp: `[col]`, event type: `[col] = '[value]'`
- "[Metric term, e.g. retained]" means: [exact definition].
- Timezone: [UTC | account tz]; grain: [day | week | month].

## SQL
\`\`\`sql
-- PATTERN: RETENTION (day-N exact). Swap pattern block as needed.
-- NOTE: both sides of the day-N comparison MUST be day-truncated. cohort_day is
-- DATE_TRUNC'd and active_day is DATE_TRUNC'd, so they align. If you compare a
-- DATE_TRUNC'd anchor against a RAW timestamp signup column, `= cohort_day + INTERVAL '7 day'`
-- almost never matches (timestamps land at 14:32:07, not midnight) → silent zero retention.
WITH cohort AS (                       -- denominator: who, anchored when
  SELECT
    user_id,
    DATE_TRUNC('day', MIN(event_time)) AS cohort_day
  FROM [events_table]
  GROUP BY user_id
),
activity AS (                          -- one row per user per active day
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('day', event_time) AS active_day
  FROM [events_table]
)
SELECT
  c.cohort_day,
  COUNT(DISTINCT c.user_id)                          AS cohort_size,
  COUNT(DISTINCT CASE
    WHEN a.active_day = c.cohort_day + INTERVAL '7 day'
    THEN c.user_id END)                              AS retained_d7,
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN a.active_day = c.cohort_day + INTERVAL '7 day'
    THEN c.user_id END) / NULLIF(COUNT(DISTINCT c.user_id), 0), 1) AS retention_d7_pct
FROM cohort c
LEFT JOIN activity a ON a.user_id = c.user_id
-- Drop incomplete cohorts: a cohort hasn't had time to reach day 7 yet if
-- cohort_day + 7d is still in the future. Including it understates retention.
WHERE c.cohort_day + INTERVAL '7 day' <= DATE_TRUNC('day', now())
GROUP BY c.cohort_day
ORDER BY c.cohort_day;
\`\`\`

**Retention — N-day WINDOW `[+7d, +14d)` (the canonical "week-2 retention"):**
\`\`\`sql
-- "Week-2 retention" = active ANY time in the window [cohort_day + 7d, cohort_day + 14d),
-- NOT active exactly on day 7. Half-open interval avoids double-counting the boundary.
WITH cohort AS (
  SELECT userTRUNC('day', MIN(eventday
  FROM [eventsid
),
activity AS (
  SELECT DISTINCT userTRUNC('day', eventday
  FROM [eventsday,
  COUNT(DISTINCT c.usersize,
  COUNT(DISTINCT CASE




\\`sql
WITH steps AS (
  SELECT usertype = 'signup'     THEN eventtype = 'activated'  THEN eventtype = 'purchased'  THEN eventtable]
  GROUP BY usersignup,
  COUNT() FILTER (WHERE t3 >= t2 AND t2 >= t1)                AS step3days AS (                        -- de-dupe to one row per user per active day
  SELECT DISTINCT userTRUNC('day', eventtable]
),
dau AS (                                   -- distinct users active each day
  SELECT day, COUNT(DISTINCT userdays
  GROUP BY day
),
mau AS (                                   -- trailing-30 distinct users via window frame
  SELECT day,


\\`sql
SELECT DATEtime) AS wk, eventid) AS uniquetable]
GROUP BY 1, 2 ORDER BY 1, 2;
\\`


| [cohortsize] | [retainedd7size ≈ known signups for the period).
 [ ]  used  for users,  for events — not swapped.
 [ ]  pick a known userstart + window > now()` — recency-truncated cohorts haven't had time to mature and drag the metric down.
 [ ]  both sides of a day-N comparison are day-truncated (a raw-timestamp anchor vs a truncated one = silent zero matches).
 [ ]  ; LEFT JOIN didn't silently drop the cohort.


[2–3 sentences: what one row means, the headline number, and the single assumption most likely to flip the answer.]

Avoid (anti-patterns)

• Counting events when you mean users (or vice versa) — COUNT(*) where you needed COUNT(DISTINCT user_id) silently inflates every number.
• INNER JOIN on the cohort — drops users who never returned, turning a retention denominator into only the retained. Use LEFT JOIN and anchor on the cohort table.
• Off-by-one time windows — BETWEEN is inclusive on both ends and double-counts midnight rows; use half-open >= start AND < end. Forgetting timezone shifts whole cohorts by a day.
• Day-N exact vs N-day-window confusion — "day-7 retention" (active exactly on day 7) and "week-1 retention" (active any time in days 1–7) give very different numbers. State which.
• Trusting the first run — shipping a number without the spot-check and monotonicity checks. A query that runs is not a query that's correct.

Tips

• Build the denominator first and confirm its size against a known figure before adding any retention/funnel logic — most wrong answers are wrong denominators.
• Prefer CTEs over nested subqueries. One CTE per step reads like the explanation you'll have to write anyway, and is far easier to debug.
• Window functions earn their keep for "first/next event" logic: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) to grab a user's first action beats a self-join every time.
• State the dialect up front and use its native date functions — DATE_TRUNC (Postgres/Snowflake) vs TIMESTAMP_TRUNC/DATE_TRUNC (BigQuery) vs DATE_FORMAT (MySQL). A query for the wrong dialect is a query that doesn't run.