SQL Data Analysis Techniques: Complete Guide

-- Cohort retention analysis by signup month
WITH user_cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', signup_date) as cohort_month
  FROM users
),
user_activity AS (
  SELECT
    uc.user_id,
    uc.cohort_month,
    DATE_TRUNC('month', a.activity_date) as activity_month,
    EXTRACT(MONTH FROM AGE(a.activity_date, uc.cohort_month)) as months_since_signup
  FROM user_cohorts uc
  JOIN activity a ON uc.user_id = a.user_id
)
SELECT
  cohort_month,
  months_since_signup,
  COUNT(DISTINCT user_id) as active_users,
  ROUND(
    100.0 * COUNT(DISTINCT user_id) /
    FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
      PARTITION BY cohort_month
      ORDER BY months_since_signup
    ), 2
  ) as retention_rate
FROM user_activity
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

-- Daily revenue with week-over-week comparison
WITH daily_revenue AS (
  SELECT
    DATE_TRUNC('day', order_date) as day,
    SUM(total) as revenue
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '60 days'
  GROUP BY DATE_TRUNC('day', order_date)
)
SELECT
  day,
  revenue,
  LAG(revenue, 7) OVER (ORDER BY day) as revenue_last_week,
  revenue - LAG(revenue, 7) OVER (ORDER BY day) as wow_change,
  ROUND(
    100.0 * (revenue - LAG(revenue, 7) OVER (ORDER BY day)) /
    NULLIF(LAG(revenue, 7) OVER (ORDER BY day), 0),
    2
  ) as wow_change_pct,
  AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) as seven_day_avg
FROM daily_revenue
ORDER BY day DESC;

-- RFM analysis with quintile scoring
WITH customer_rfm AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date) as recency_days,
    COUNT(DISTINCT order_id) as frequency,
    SUM(total) as monetary
  FROM orders
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency_days) as r_score,
    NTILE(5) OVER (ORDER BY frequency DESC) as f_score,
    NTILE(5) OVER (ORDER BY monetary DESC) as m_score
  FROM customer_rfm
)
SELECT
  customer_id,
  recency_days,
  frequency,
  monetary,
  r_score,
  f_score,
  m_score,
  CASE
    WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
    WHEN r_score >= 3 AND f_score >= 3 THEN 'Loyal Customers'
    WHEN r_score >= 4 AND f_score <= 2 THEN 'Promising'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
    WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
    ELSE 'Other'
  END as rfm_segment
FROM rfm_scores
ORDER BY r_score DESC, f_score DESC, m_score DESC;

-- Product category revenue mix with percentages
WITH category_revenue AS (
  SELECT
    category,
    SUM(revenue) as category_total
  FROM product_sales
  WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY category
),
total_revenue AS (
  SELECT SUM(revenue) as overall_total
  FROM product_sales
  WHERE sale_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  cr.category,
  cr.category_total,
  tr.overall_total,
  ROUND(
    100.0 * cr.category_total / tr.overall_total,
    2
  ) as pct_of_total,
  ROUND(
    100.0 * SUM(cr.category_total) OVER (
      ORDER BY cr.category_total DESC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) / tr.overall_total,
    2
  ) as cumulative_pct
FROM category_revenue cr
CROSS JOIN total_revenue tr
ORDER BY cr.category_total DESC;

-- Revenue by category and month (pivot format)
SELECT
  category,
  SUM(CASE WHEN month = '2024-01' THEN revenue ELSE 0 END) as jan_2024,
  SUM(CASE WHEN month = '2024-02' THEN revenue ELSE 0 END) as feb_2024,
  SUM(CASE WHEN month = '2024-03' THEN revenue ELSE 0 END) as mar_2024,
  SUM(CASE WHEN month = '2024-04' THEN revenue ELSE 0 END) as apr_2024,
  SUM(CASE WHEN month = '2024-05' THEN revenue ELSE 0 END) as may_2024,
  SUM(CASE WHEN month = '2024-06' THEN revenue ELSE 0 END) as jun_2024,
  SUM(revenue) as total_revenue
FROM (
  SELECT
    category,
    TO_CHAR(sale_date, 'YYYY-MM') as month,
    SUM(amount) as revenue
  FROM sales
  WHERE sale_date >= '2024-01-01'
  GROUP BY category, TO_CHAR(sale_date, 'YYYY-MM')
) monthly_sales
GROUP BY category
ORDER BY total_revenue DESC;