luvv to helpDiscover the Best Free Online Tools

SQL

Learn SQL for Product Analyst for free: roadmap, examples, subskills, and a skill exam.

Published: December 22, 2025 | Updated: December 22, 2025

Table of Contents

Why SQL matters for Product Analysts

SQL is how Product Analysts turn raw product events into concrete answers: How many users came back? Where do people drop in the funnel? Which segment drives monetization? If you can query well, you can validate hypotheses, size opportunities, and guide product decisions with confidence.

  • Translate product questions into datasets and metrics
  • Build DAU/WAU/MAU, funnels, cohorts, segmentation
  • Join user, session, and event tables reliably
  • Ship insights fast with performance-aware queries and sanity checks

What you’ll learn

  • Understand product event tables and common schemas
  • Compute DAU/WAU/MAU correctly
  • Join user, session, and event data safely
  • Build funnels and cohorts for retention analysis
  • Segment by product dimensions (feature, country, device, plan)
  • Validate data quality and write performance-aware queries

Who this is for

  • Aspiring or current Product Analysts needing hands-on SQL
  • PMs, Growth, and UX who want data self-serve confidence
  • Engineers who want to support product analytics workflows

Prerequisites

  • Basic comfort with data types, arithmetic, and reading simple SQL
  • Familiarity with product concepts: user, session, event, funnel, cohort
  • No specific warehouse required; examples use ANSI-like SQL
Before you start: sample tables used in examples
-- events: one row per event
events(user_id, session_id, event_time, event_name, properties)
-- users: one row per user
users(user_id, signup_date, country, plan)
-- sessions: one row per session
sessions(session_id, user_id, session_start, device, source)
-- calendar: dates for easy joins
calendar(d)

Learning path

  1. Understand product event tables — Identify keys, timestamps, and event_name usage. Practice reading schemas and checking coverage.
  2. Metric queries (DAU/WAU/MAU) — Count active users with correct date grains and deduplication.
  3. User & session joins — Safely join users, sessions, and events without inflating counts.
  4. Funnel queries — Build ordered steps per user, compute step-through rates, and drop-off.
  5. Retention & cohorts — Anchor cohorts to first activity, compute N-day/week/month retention.
  6. Segmentation & performance — Slice metrics by product dimensions; add data quality checks and tune queries.

Worked examples

1) DAU, WAU, MAU with consistent logic

Goal: daily active users (DAU), plus rolling WAU/MAU. Deduplicate by user_id per day.

WITH daily_users AS (
  SELECT
    DATE(event_time) AS d,
    user_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '90' DAY
  GROUP BY 1,2
), dau AS (
  SELECT d, COUNT(DISTINCT user_id) AS dau
  FROM daily_users
  GROUP BY 1
), wau AS (
  SELECT d,
         COUNT(DISTINCT user_id) FILTER (WHERE d >= c.d - INTERVAL '6' DAY AND d <= c.d) AS wau
  FROM daily_users du
  JOIN (SELECT DISTINCT d FROM daily_users) c ON du.d BETWEEN c.d - INTERVAL '6' DAY AND c.d
  GROUP BY c.d
), mau AS (
  SELECT d,
         COUNT(DISTINCT user_id) FILTER (WHERE d >= c.d - INTERVAL '29' DAY AND d <= c.d) AS mau
  FROM daily_users du
  JOIN (SELECT DISTINCT d FROM daily_users) c ON du.d BETWEEN c.d - INTERVAL '29' DAY AND c.d
  GROUP BY c.d
)
SELECT a.d, a.dau, w.wau, m.mau
FROM dau a
JOIN wau w ON w.d = a.d
JOIN mau m ON m.d = a.d
ORDER BY a.d;

Tip: Always define “active” consistently (any event vs specific event types).

2) Session-level join without double counting

Goal: attribute DAU by device using sessions, avoiding fan-out joins.

WITH user_day AS (
  SELECT DATE(event_time) AS d, user_id
  FROM events
  GROUP BY 1,2
), session_device AS (
  SELECT s.user_id, DATE(s.session_start) AS d,
         ANY_VALUE(s.device) AS device  -- or MIN/MAX if ANY_VALUE not supported
  FROM sessions s
  GROUP BY 1,2
)
SELECT ud.d, sd.device, COUNT(DISTINCT ud.user_id) AS dau
FROM user_day ud
LEFT JOIN session_device sd
  ON sd.user_id = ud.user_id AND sd.d = ud.d
GROUP BY 1,2
ORDER BY 1,2;

Pattern: compute user-day first, then enrich with one-to-one keys.

3) Funnel: step-through from View → AddToCart → Purchase
WITH ordered AS (
  SELECT user_id,
         event_time,
         event_name,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
  FROM events
  WHERE event_name IN ('view', 'add_to_cart', 'purchase')
), steps AS (
  SELECT user_id,
         MIN(CASE WHEN event_name = 'view' THEN event_time END) AS step1,
         MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS step2,
         MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS step3
  FROM ordered
  GROUP BY user_id
)
SELECT
  COUNT(*) AS users_seen,
  COUNT(*) FILTER (WHERE step1 IS NOT NULL) AS s1,
  COUNT(*) FILTER (WHERE step1 IS NOT NULL AND step2 >= step1) AS s2,
  COUNT(*) FILTER (WHERE step1 IS NOT NULL AND step2 >= step1 AND step3 >= step2) AS s3,
  ROUND(100.0 * COUNT(*) FILTER (WHERE step1 IS NOT NULL AND step2 >= step1) / NULLIF(COUNT(*) FILTER (WHERE step1 IS NOT NULL),0), 2) AS s1_to_s2_pct,
  ROUND(100.0 * COUNT(*) FILTER (WHERE step1 IS NOT NULL AND step2 >= step1 AND step3 >= step2) / NULLIF(COUNT(*) FILTER (WHERE step1 IS NOT NULL AND step2 >= step1),0), 2) AS s2_to_s3_pct
FROM steps;

Key: preserve order — later steps must occur after earlier steps.

4) Retention: D7 from first activity cohort
WITH first_seen AS (
  SELECT user_id, MIN(DATE(event_time)) AS cohort_day
  FROM events
  GROUP BY 1
), activity AS (
  SELECT e.user_id,
         DATE(e.event_time) AS d
  FROM events e
  GROUP BY 1,2
), ret AS (
  SELECT f.cohort_day,
         COUNT(DISTINCT a.user_id) FILTER (WHERE a.d = f.cohort_day + INTERVAL '7' DAY) AS retained_d7,
         COUNT(DISTINCT f.user_id) AS cohort_size
  FROM first_seen f
  LEFT JOIN activity a ON a.user_id = f.user_id
  GROUP BY 1
)
SELECT cohort_day, cohort_size,
       retained_d7,
       ROUND(100.0 * retained_d7 / NULLIF(cohort_size,0), 2) AS d7_retention_pct
FROM ret
ORDER BY cohort_day;

Anchor cohorts consistently and count activity on the right day offset.

5) Segmentation & performance: plan-country breakdown with filters
WITH active AS (
  SELECT DATE(event_time) AS d, user_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY 1,2
)
SELECT a.d, u.plan, u.country, COUNT(DISTINCT a.user_id) AS dau
FROM active a
JOIN users u ON u.user_id = a.user_id
WHERE u.country IS NOT NULL  -- filter early
GROUP BY 1,2,3
ORDER BY a.d, u.plan, u.country;

Performance tips: filter date ranges early, avoid SELECT *, limit columns, and pre-aggregate before joining wide tables.

Drills and quick exercises

  • Write DAU for the last 14 days using DISTINCT user_id per day.
  • Compute WAU using a 7-day rolling window over daily user sets.
  • Join sessions to events to get device share of DAU without double counting.
  • Build a 3-step funnel of your product’s core events and report step-through rates.
  • Produce D1, D7, D30 retention from first activity cohorts.
  • Segment DAU by plan and country; identify top 3 combinations.
  • Add data quality checks: null rates on keys, negative durations, future dates.
  • Optimize a slow query by removing SELECT *, pushing filters up, and pre-aggregating.

Common mistakes and debugging tips

  • Counting events instead of users. Always DISTINCT user_id when reporting user metrics.
  • Funnel step order ignored. Enforce temporal order between steps with timestamps.
  • Fan-out joins inflate counts. Build user-day or session-level aggregates first, then join.
  • Inconsistent “active” definition. Decide whether any event or a specific event type defines activity.
  • Cohort anchor confusion. Use first activity (or signup) consistently across all cohorts.
  • Slow scans. Filter by date early, select only needed columns, and pre-aggregate.
  • Data quality blind spots. Check for null user_id/session_id, out-of-range timestamps, and duplicate keys.
Debugging checklist
  • Validate row counts at each CTE; does each step reduce or increase as expected?
  • Compare sample users across steps to ensure logic integrity.
  • Run small date slices first (1–3 days) to confirm correctness before scaling.
  • Cross-check results using two methods (e.g., window vs pre-agg) for sanity.

Mini project: Funnel + retention for a new feature

Scenario: A new feature has three key events: feature_view, feature_use, feature_success. You need to measure initial adoption and 7-day retention among users who touched the feature.

  1. Create a cohort of first-time feature_view users by day.
  2. Build a 3-step funnel feature_view → feature_use → feature_success with step-through rates by day.
  3. Compute D7 retention for the cohort (any event counts as retained).
  4. Segment funnel conversion and D7 retention by plan and device.
  5. Add data quality checks: null user_id, out-of-order timestamps, missing step events.
  6. Optimize the slowest query by removing SELECT *, limiting date range, and pre-aggregating user-day first.
Suggested structure (CTEs)
WITH feature_events AS (
  SELECT user_id, event_time, event_name
  FROM events
  WHERE event_name IN ('feature_view','feature_use','feature_success')
    AND event_time >= CURRENT_DATE - INTERVAL '60' DAY
), first_view AS (
  SELECT user_id, MIN(DATE(event_time)) AS cohort_day
  FROM feature_events
  WHERE event_name = 'feature_view'
  GROUP BY 1
), funnel AS (
  SELECT fe.user_id,
         MIN(CASE WHEN event_name='feature_view' THEN event_time END) AS s1,
         MIN(CASE WHEN event_name='feature_use' THEN event_time END) AS s2,
         MIN(CASE WHEN event_name='feature_success' THEN event_time END) AS s3
  FROM feature_events fe
  GROUP BY 1
), d7 AS (
  SELECT a.user_id, DATE(a.event_time) AS d
  FROM events a
  WHERE a.event_time >= CURRENT_DATE - INTERVAL '60' DAY
)
SELECT fv.cohort_day, u.plan, s.device,
       COUNT(DISTINCT fv.user_id) AS cohort_size,
       COUNT(DISTINCT f.user_id) FILTER (WHERE f.s1 IS NOT NULL) AS step1,
       COUNT(DISTINCT f.user_id) FILTER (WHERE f.s1 IS NOT NULL AND f.s2 >= f.s1) AS step2,
       COUNT(DISTINCT f.user_id) FILTER (WHERE f.s1 IS NOT NULL AND f.s2 >= f.s1 AND f.s3 >= f.s2) AS step3,
       COUNT(DISTINCT fv.user_id) FILTER (
         WHERE EXISTS (
           SELECT 1 FROM d7 x WHERE x.user_id = fv.user_id AND x.d = fv.cohort_day + INTERVAL '7' DAY
         )
       ) AS retained_d7
FROM first_view fv
LEFT JOIN funnel f ON f.user_id = fv.user_id
LEFT JOIN users u ON u.user_id = fv.user_id
LEFT JOIN (
  SELECT user_id, ANY_VALUE(device) AS device
  FROM sessions
  GROUP BY 1
) s ON s.user_id = fv.user_id
GROUP BY 1,2,3
ORDER BY 1,2,3;

Subskills

  • Product Event Tables Understanding — Identify keys, timestamps, and event taxonomies; avoid fan-out mistakes.
  • Metric Queries DAU MAU WAU — Compute active user metrics consistently across time windows.
  • User And Session Level Joins — Build one-to-one keys first (user-day/session) to prevent inflated counts.
  • Funnel Queries — Enforce step order, compute conversion, and analyze drop-off.
  • Retention And Cohort Queries — Anchor on first activity or signup; measure D1/D7/D30, WAU/MAU stickiness.
  • Segmentation By Product Dimensions — Slice metrics by plan, country, device, feature flags.
  • Data Quality Checks — Validate nulls, duplicates, impossible times, and schema drift.
  • Performance Aware Queries — Push filters early, pre-aggregate, limit columns, avoid SELECT *.

Next steps

  • Practice each subskill with the drills above
  • Complete the mini project and share your approach in review sessions
  • Take the skill exam below to validate your readiness

SQL — Skill Exam

14 questions. Estimated time: 20–25 minutes. You can take this exam for free. If you are logged in, your progress and results will be saved; if not, you can still complete the exam but results won’t be saved. Passing score: 70%.Rules: choose the best answer(s). Some questions have multiple correct answers. SQL syntax is ANSI-like; focus on logic over vendor-specific functions.

14 questions70% to pass

Topics

8 topics to cover

1

Product Event Tables Understanding

2

Metric Queries DAU MAU WAU

3

User And Session Level Joins

4

Funnel Queries

5

Retention And Cohort Queries

6

Segmentation By Product Dimensions

7

Data Quality Checks

8

Performance Aware Queries

Back to Product Analyst

Have questions about SQL?

AI Assistant

Ask questions about this tool