SQL Analytics Queries – Real-World Data Analysis Patterns

-- Step 1: Assign each user to their signup cohort (first order month)
WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY user_id
),

-- Step 2: Tag every order with the user's cohort and the "month offset"
-- (how many months after signup the order was placed)
order_cohorts AS (
  SELECT
    uc.cohort_month,
    DATE_TRUNC('month', o.created_at)            AS order_month,
    DATE_PART('year', AGE(DATE_TRUNC('month', o.created_at), uc.cohort_month)) * 12 +
    DATE_PART('month', AGE(DATE_TRUNC('month', o.created_at), uc.cohort_month))
                                                 AS months_since_signup,
    o.total
  FROM orders o
  JOIN user_cohorts uc ON uc.user_id = o.user_id
  WHERE o.status != 'cancelled'
)

-- Step 3: Aggregate by cohort and month offset
SELECT
  TO_CHAR(cohort_month, 'YYYY-MM')  AS cohort,
  months_since_signup               AS month_number,
  COUNT(*)                          AS orders,
  ROUND(SUM(total), 2)              AS revenue
FROM order_cohorts
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

WITH first_orders AS (
  -- Find each user's first order month
  SELECT user_id,
         DATE_TRUNC('month', MIN(created_at)) AS first_month
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY user_id
),

monthly_activity AS (
  -- Find all months in which each user was active (placed an order)
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', created_at) AS active_month
  FROM orders
  WHERE status != 'cancelled'
)

-- Calculate retention per cohort
SELECT
  TO_CHAR(f.first_month, 'YYYY-MM')       AS cohort_month,
  COUNT(DISTINCT f.user_id)               AS cohort_size,

  -- Month 0: should equal cohort_size (100%)
  COUNT(DISTINCT CASE
    WHEN ma.active_month = f.first_month THEN f.user_id END
  ) AS month_0,

  -- Month 1: users active 1 month after signup
  COUNT(DISTINCT CASE
    WHEN ma.active_month = f.first_month + INTERVAL '1 month' THEN f.user_id END
  ) AS month_1,

  -- Month 2
  COUNT(DISTINCT CASE
    WHEN ma.active_month = f.first_month + INTERVAL '2 months' THEN f.user_id END
  ) AS month_2,

  -- Month 3
  COUNT(DISTINCT CASE
    WHEN ma.active_month = f.first_month + INTERVAL '3 months' THEN f.user_id END
  ) AS month_3,

  -- Retention rates as percentages
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN ma.active_month = f.first_month + INTERVAL '1 month' THEN f.user_id END)
    / NULLIF(COUNT(DISTINCT f.user_id), 0), 1) AS retention_month_1_pct

FROM first_orders f
LEFT JOIN monthly_activity ma ON ma.user_id = f.user_id
GROUP BY f.first_month
ORDER BY f.first_month;

WITH daily_revenue AS (
  -- Aggregate revenue per day
  SELECT
    created_at::DATE     AS day,
    SUM(total)           AS daily_total
  FROM orders
  WHERE status != 'cancelled'
  GROUP BY created_at::DATE
),

calendar AS (
  -- Generate a complete date series so days with no orders appear as 0
  -- (PostgreSQL generate_series)
  SELECT generate_series(
    (SELECT MIN(day) FROM daily_revenue),
    (SELECT MAX(day) FROM daily_revenue),
    '1 day'::INTERVAL
  )::DATE AS day
)

SELECT
  c.day,
  COALESCE(dr.daily_total, 0)   AS daily_revenue,
  ROUND(
    AVG(COALESCE(dr.daily_total, 0)) OVER (
      ORDER BY c.day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7-day window
    ), 2
  )                             AS rolling_7d_avg
FROM calendar c
LEFT JOIN daily_revenue dr ON dr.day = c.day
ORDER BY c.day;

-- NTILE divides rows into N equal buckets by the ORDER BY column
WITH order_quartiles AS (
  SELECT
    id          AS order_id,
    user_id,
    total,
    NTILE(4) OVER (ORDER BY total) AS quartile,
    NTILE(10) OVER (ORDER BY total) AS decile
  FROM orders
  WHERE status != 'cancelled'
)
SELECT
  quartile,
  COUNT(*)             AS order_count,
  ROUND(MIN(total), 2) AS min_total,
  ROUND(MAX(total), 2) AS max_total,
  ROUND(AVG(total), 2) AS avg_total
FROM order_quartiles
GROUP BY quartile
ORDER BY quartile;

-- Find the top 10% spenders (decile 10)
SELECT u.name, u.email, SUM(o.total) AS total_spend
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status != 'cancelled'
GROUP BY u.id, u.name, u.email
HAVING SUM(o.total) >= (
  -- The 90th percentile cutoff
  SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total)
  FROM orders WHERE status != 'cancelled'
)
ORDER BY total_spend DESC;

WITH user_stats AS (
  SELECT
    u.id,
    u.name,
    COUNT(o.id)       AS order_count,
    SUM(o.total)      AS total_spend
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id AND o.status != 'cancelled'
  GROUP BY u.id, u.name
),

funnel AS (
  SELECT
    COUNT(*) AS total_users,

    -- Step 1: Users who placed at least one order
    COUNT(CASE WHEN order_count >= 1 THEN 1 END) AS placed_order,

    -- Step 2: Users who came back and ordered more than once (repeat buyers)
    COUNT(CASE WHEN order_count >= 2 THEN 1 END) AS repeat_buyer,

    -- Step 3: High-value customers (spent over $500)
    COUNT(CASE WHEN total_spend > 500 THEN 1 END) AS high_value
  FROM user_stats
)
SELECT
  'All Users'             AS step,
  total_users             AS user_count,
  100.0                   AS pct_of_previous

UNION ALL

SELECT
  '1. Placed any order',
  placed_order,
  ROUND(100.0 * placed_order / NULLIF(total_users, 0), 1)
FROM funnel

UNION ALL

SELECT
  '2. Placed 2+ orders (repeat)',
  repeat_buyer,
  ROUND(100.0 * repeat_buyer / NULLIF(placed_order, 0), 1)
FROM funnel

UNION ALL

SELECT
  '3. Spent > $500 (high-value)',
  high_value,
  ROUND(100.0 * high_value / NULLIF(placed_order, 0), 1)
FROM funnel;