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
WITH cohorts AS (
  SELECT user_id,
    DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_month
  FROM orders GROUP BY user_id
)
SELECT c.cohort_month,
  DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
  COUNT(DISTINCT o.user_id) AS active_users
FROM orders o
JOIN cohorts c ON o.user_id = c.user_id
GROUP BY c.cohort_month, order_month;

-- Day-1 Retention
SELECT DATE(first_login) AS cohort,
  COUNT(DISTINCT a.user_id) AS day0,
  COUNT(DISTINCT b.user_id) AS day1,
  ROUND(COUNT(DISTINCT b.user_id)*100.0
    / COUNT(DISTINCT a.user_id),1) AS day1_retention
FROM sessions a
LEFT JOIN sessions b
  ON a.user_id = b.user_id
  AND DATE(b.login_time) = DATE(a.first_login) + INTERVAL 1 DAY
GROUP BY cohort;

-- Funnel Analysis
SELECT
  COUNT(DISTINCT user_id) AS visited,
  COUNT(DISTINCT CASE WHEN added_to_cart THEN user_id END) AS cart,
  COUNT(DISTINCT CASE WHEN purchased     THEN user_id END) AS purchased
FROM user_events;

Common Mistakes

Interview Tips

Practice