Advanced Analytics SQL

What You'll Learn

Expert analytics SQL involves cohort analysis (grouping users by when they joined), retention analysis (% of users who return), and funnel analysis (conversion rates through a sequence of steps). These patterns appear in product analytics, growth engineering, and data science roles.

Syntax

-- Cohort: group users by first event date
-- Retention: join to later activity
-- Funnel: SUM(CASE WHEN step THEN 1 END)

Example

-- Cohort Analysis: group customers by their first-order month
WITH first_order AS (
  SELECT customer_id,
    MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id
)
SELECT
  DATE_FORMAT(f.first_order_date, '%Y-%m') AS cohort_month,
  DATE_FORMAT(o.order_date, '%Y-%m')       AS order_month,
  COUNT(DISTINCT o.customer_id)            AS active_customers
FROM orders o
JOIN first_order f ON o.customer_id = f.customer_id
GROUP BY DATE_FORMAT(f.first_order_date, '%Y-%m'),
         DATE_FORMAT(o.order_date, '%Y-%m');

-- Day-1 Retention from the logins table (introduced below)
SELECT
  a.login_date                 AS cohort_day,
  COUNT(DISTINCT a.customer_id) AS day0,
  COUNT(DISTINCT b.customer_id) AS day1
FROM logins a
LEFT JOIN logins b
  ON a.customer_id = b.customer_id
  AND b.login_date = a.login_date + INTERVAL 1 DAY
GROUP BY a.login_date;

-- Funnel Analysis: the order pipeline in one pass
SELECT
  COUNT(*)                                       AS orders_placed,
  COUNT(CASE WHEN status = 'shipped' THEN 1 END) AS shipped,
  COUNT(DISTINCT CASE WHEN status = 'shipped'
    AND customer_id IN (SELECT customer_id FROM orders
                        GROUP BY customer_id HAVING COUNT(*) > 1)
    THEN customer_id END)                        AS repeat_shipped_customers
FROM orders;

Beyond the Basics

-- logins (introduced by this topic)
-- customer_id | login_date
-- 1 (Karl)    | 2024-01-01, 01-02, 01-03, 03-01
-- 2 (Ines)    | 2024-02-01, 02-02
-- 3 (Omar)    | 2024-04-01
-- 4 (Sofia)   | 2024-05-01
-- 5 (Maike)   | 2024-03-01, 03-02, 03-03, 03-04

-- orders (from Topic 7)
-- id | customer_id | status    | total  | order_date
-- 1  | 1           | shipped   | 240.00 | 2024-01-15
-- 2  | 2           | shipped   | 89.90  | 2024-02-03
-- 3  | 1           | pending   | 430.00 | 2024-03-11
-- 4  | 3           | shipped   | 59.50  | 2024-04-02
-- 5  | 4           | cancelled | 120.00 | 2024-05-20

-- Output of the example's cohort query:
-- cohort_month | order_month | active_customers
-- 2024-01      | 2024-01     | 1     (Karl's first order)
-- 2024-01      | 2024-03     | 1     (Karl came BACK)
-- 2024-02      | 2024-02     | 1     (Ines)
-- 2024-04      | 2024-04     | 1     (Omar)
-- 2024-05      | 2024-05     | 1     (Sofia)
-- Karl is the only returning customer — five orders, one repeat

-- Day-1 retention from the example, traced:
-- Karl: Jan 1 → Jan 2 exists ✓ (and Jan 2 → Jan 3 ✓)
-- Ines: Feb 1 → Feb 2 ✓
-- Maike: Mar 1 → Mar 2 ✓ (and 2→3, 3→4 ✓)
-- Omar: Apr 1 → nothing ✗   Sofia: May 1 → nothing ✗
-- Day-0 rows: 9 · Day-1 matches: 7 → 7/9 ≈ 78%

-- From the example, traced on ShopCo:
-- orders_placed:            5  (all orders)
-- shipped:                  3  (1, 2, 4)
-- repeat_shipped_customers: 1  (Karl — order 1 shipped, 2 orders total)
-- Note what the funnel DOESN'T say: Karl's repeat order is pending.

Common Mistakes

Interview Tips

Official References

• PostgreSQL — SQL Commands reference
• SQLite — Query Language reference
• PostgreSQL — Tutorial

Practice

Frequently Asked Questions