SQL for Analytics - lesson 6 | HackIndia Skills

SQL for Analytics - lesson 6 | HackIndia Skills | HackIndia

Example

Monthly retention

This example uses an events table with user_id, event_name, and event_time. It groups users by signup month, then counts who was active in later months.

Change event names to match your sample data. If your database is SQLite, use strftime. If you use PostgreSQL, use date_trunc.

-- PostgreSQL
WITH signups AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_time)) AS cohort_month
  FROM events
  WHERE event_name = 'signup'
  GROUP BY user_id
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_time) AS activity_month
  FROM events
  WHERE event_name IN ('login', 'view_product', 'place_order')
),
cohort_activity AS (
  SELECT
    s.cohort_month,
    a.activity_month,
    (
      EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month)
    ) - (
      EXTRACT(YEAR FROM s.cohort_month) * 12 + EXTRACT(MONTH FROM s.cohort_month)
    ) AS month_number,
    s.user_id
  FROM signups s
  JOIN activity a ON a.user_id = s.user_id
  WHERE a.activity_month >= s.cohort_month
)
SELECT
  cohort_month,
  month_number,
  COUNT(DISTINCT user_id) AS retained_users
FROM cohort_activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

Example

Ordered funnel

This query counts users who reached each step after the previous step. It prevents a user from being counted for checkout if checkout happened before signup.

Use MIN to keep the first time a user did each step.

WITH user_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_name = 'signup' THEN event_time END) AS signup_time,
    MIN(CASE WHEN event_name = 'view_product' THEN event_time END) AS view_time,
    MIN(CASE WHEN event_name = 'add_to_cart' THEN event_time END) AS cart_time,
    MIN(CASE WHEN event_name = 'place_order' THEN event_time END) AS order_time
  FROM events
  GROUP BY user_id
),
valid_steps AS (
  SELECT
    user_id,
    signup_time,
    CASE WHEN view_time >= signup_time THEN view_time END AS view_time,
    CASE WHEN cart_time >= view_time AND view_time >= signup_time THEN cart_time END AS cart_time,
    CASE WHEN order_time >= cart_time AND cart_time >= view_time AND view_time >= signup_time THEN order_time END AS order_time
  FROM user_steps
)
SELECT 'signup' AS step, COUNT(*) AS users FROM valid_steps WHERE signup_time IS NOT NULL
UNION ALL
SELECT 'view_product', COUNT(*) FROM valid_steps WHERE view_time IS NOT NULL
UNION ALL
SELECT 'add_to_cart', COUNT(*) FROM valid_steps WHERE cart_time IS NOT NULL
UNION ALL
SELECT 'place_order', COUNT(*) FROM valid_steps WHERE order_time IS NOT NULL;

Write cohort and funnel queries

One idea

Cohort meaning

Cohort query shape

Monthly retention

Funnel meaning

Ordered funnel

Common mistake

Do this now

Quick check