Why Product Analytics SQL Is Its Own Skill
Funnel, retention, and cohort queries are the daily bread of product analyst and data scientist roles at consumer tech companies — and they're conceptually different from the "find the Nth highest salary" style of classic SQL interview question. Instead of static tables, you're usually working with an events table (one row per user action) and need to reshape raw event streams into business metrics. This guide covers the three query patterns that come up constantly: funnels, retention, and cohorts.
The Sample Data
-- events: one row per user action, the foundation of almost all product analytics SQL
| user_id | event_name | event_time |
|---------|------------------|------------------------|
| 1 | signed_up | 2026-01-01 09:00:00 |
| 1 | viewed_item | 2026-01-01 09:05:00 |
| 1 | added_to_cart | 2026-01-01 09:07:00 |
| 1 | purchased | 2026-01-01 09:10:00 |
| 2 | signed_up | 2026-01-01 10:00:00 |
| 2 | viewed_item | 2026-01-01 10:02:00 |
| 3 | signed_up | 2026-01-02 08:00:00 |Funnel Analysis
A funnel measures how many users complete each step of a defined sequence, and where they drop off. The standard pattern: flag whether each user reached each step, then aggregate.
WITH funnel_flags AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'viewed_item' THEN 1 ELSE 0 END) AS viewed,
MAX(CASE WHEN event_name = 'added_to_cart' THEN 1 ELSE 0 END) AS added,
MAX(CASE WHEN event_name = 'purchased' THEN 1 ELSE 0 END) AS purchased
FROM events
GROUP BY user_id
)
SELECT
SUM(viewed) AS step1_viewed,
SUM(added) AS step2_added_to_cart,
SUM(purchased) AS step3_purchased,
ROUND(100.0 * SUM(added) / NULLIF(SUM(viewed), 0), 1) AS pct_view_to_cart,
ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added), 0), 1) AS pct_cart_to_purchase
FROM funnel_flags;Ordered funnels matter. The simple version above just checks whether each event happened at all, regardless of order — usually fine for a strict "must complete step 1 to attempt step 2" product flow, but if a user could technically purchase without viewing first (a direct link, for example), you need to enforce order explicitly:
-- Strict ordered funnel: added_to_cart must happen AFTER viewed_item, for the same user
WITH viewed AS (
SELECT user_id, MIN(event_time) AS viewed_at
FROM events WHERE event_name = 'viewed_item' GROUP BY user_id
),
added AS (
SELECT a.user_id, MIN(a.event_time) AS added_at
FROM events a
JOIN viewed v ON a.user_id = v.user_id
WHERE a.event_name = 'added_to_cart' AND a.event_time > v.viewed_at
GROUP BY a.user_id
)
SELECT COUNT(DISTINCT v.user_id) AS viewed_count, COUNT(DISTINCT a.user_id) AS added_after_view_count
FROM viewed v
LEFT JOIN added a ON v.user_id = a.user_id;Retention Analysis
Retention measures whether a user who took an initial action (usually signup) came back and was active again N days later. The classic metrics are Day-1, Day-7, and Day-30 retention.
WITH signups AS (
SELECT user_id, DATE(event_time) AS signup_date
FROM events WHERE event_name = 'signed_up'
),
day7_active AS (
SELECT DISTINCT user_id, DATE(event_time) AS active_date
FROM events
)
SELECT
s.signup_date,
COUNT(DISTINCT s.user_id) AS signed_up_users,
COUNT(DISTINCT d.user_id) AS retained_day7,
ROUND(100.0 * COUNT(DISTINCT d.user_id) / NULLIF(COUNT(DISTINCT s.user_id), 0), 1) AS day7_retention_pct
FROM signups s
LEFT JOIN day7_active d
ON s.user_id = d.user_id AND d.active_date = s.signup_date + INTERVAL '7 days'
GROUP BY s.signup_date
ORDER BY s.signup_date;This is a single-day snapshot definition ("active on exactly day 7"); a common variant instead checks "active any time in days 6-8" or "active any time in the week containing day 7" for a less noisy signal — both are valid definitions, but always confirm which one a question intends, since it changes the query's WHERE/JOIN condition.
Cohort Analysis
A cohort analysis groups users by when they started (signup week or month) and tracks a metric across subsequent periods — the classic "cohort retention triangle" seen in nearly every product analytics dashboard.
WITH cohorts AS (
SELECT user_id, DATE_TRUNC('month', MIN(event_time)) AS cohort_month
FROM events WHERE event_name = 'signed_up'
GROUP BY user_id
),
activity AS (
SELECT DISTINCT user_id, DATE_TRUNC('month', event_time) AS active_month
FROM events
)
SELECT
c.cohort_month,
a.active_month,
DATE_PART('month', AGE(a.active_month, c.cohort_month)) AS months_since_signup,
COUNT(DISTINCT a.user_id) AS active_users
FROM cohorts c
JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_month, a.active_month
ORDER BY c.cohort_month, a.active_month;The result is naturally "long" format (one row per cohort × month combination); most dashboards then pivot this into the familiar wide cohort-retention grid — see our guide to pivoting data for that reshaping step.
Handling the Denominator Correctly
A recurring mistake across all three patterns: computing a percentage against the wrong denominator. Always be explicit about what the denominator represents:
- Funnel step percentages — usually the count from the immediately previous step (step-over-step conversion), not always the very first step (which gives an "overall conversion rate" instead) — both are valid metrics, but they answer different questions.
- Retention percentages — the signup cohort size on that specific date/cohort, not the total user base across all time.
- Always guard against divide-by-zero with
NULLIF(denominator, 0), since an empty cohort or a zero-user day will otherwise throw a runtime error.
Common Mistakes
- Forgetting DISTINCT on user_id when counting — an events table has many rows per user, so a naive
COUNT(user_id)counts events, not unique users. - Ignoring event order in a funnel when the business logic actually requires it — silently overstates conversion if users can reach later steps without the earlier one.
- Timezone inconsistency between when a "day" starts for retention purposes and how event timestamps are stored — always confirm whether dates should be evaluated in UTC or the user's local timezone before writing the query.
- Comparing retention across cohorts of very different sizes without noting the sample size — a 90% Day-7 retention on a 10-person cohort is not comparable to 60% on a 50,000-person cohort.
Common Interview Questions
- Write a query to compute the conversion rate from viewing an item to purchasing it. The funnel pattern above, either the simple flag version or the strict ordered version depending on what the interviewer clarifies.
- Write a query for Day-1 retention. Same structure as the Day-7 example, with
INTERVAL '1 day'instead of 7. - How would you build a cohort retention table by signup month? The cohort analysis pattern above, typically followed by a pivot step to present it as a grid.
- What's the difference between retention and stickiness (DAU/MAU)? Retention tracks a specific cohort's return behavior over time from a fixed starting point; DAU/MAU is a ratio of daily to monthly active users, a general engagement-density metric not tied to any particular cohort.
Frequently Asked Questions
Do I need a dedicated events table to practice these patterns?
You need some log of user actions with a user identifier and a timestamp — the exact table name and columns vary by company, but the core shape (user_id, event_name, event_time) is close to universal across product analytics schemas.
Is a self join or a window function better for ordered funnel steps?
Both work; the join-based approach shown above is often clearer for a small, fixed number of steps, while LAG()/LEAD() can be more concise for longer sequential funnels. Either is a reasonable answer in an interview — explain your choice.
Practice Product Analytics SQL
These patterns are as close as SQL interview prep gets to the actual day-to-day work of a product analyst or data scientist role. Try them hands-on with our SQL practice questions, or work through a full product analytics case study in our case studies. For the pivoting step often needed to present cohort results, see our guide to pivot and unpivot.