SQL for Analytics

SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS total_revenue,
  AVG(total_amount) AS avg_order_value,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

WITH customer_cohorts AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(order_date)) AS cohort_month
  FROM orders
  GROUP BY customer_id
),
cohort_activity AS (
  SELECT
    c.cohort_month,
    DATE_TRUNC('month', o.order_date) AS activity_month,
    COUNT(DISTINCT o.customer_id) AS active_customers
  FROM customer_cohorts c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
  cohort_month,
  activity_month,
  active_customers,
  ROUND(100.0 * active_customers /
    FIRST_VALUE(active_customers) OVER (
      PARTITION BY cohort_month
      ORDER BY activity_month
    ), 2) AS retention_rate
FROM cohort_activity
ORDER BY cohort_month, activity_month;

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total_amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  revenue AS current_revenue,
  LAG(revenue, 12) OVER (ORDER BY month) AS previous_year_revenue,
  ROUND(100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month)) /
    LAG(revenue, 12) OVER (ORDER BY month), 2) AS yoy_growth_pct
FROM monthly_revenue
ORDER BY month DESC
LIMIT 6;

WITH funnel_steps AS (
  SELECT
    'Product View' AS step,
    1 AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM product_views
  WHERE view_date >= '2025-03-01'

  UNION ALL

  SELECT
    'Add to Cart' AS step,
    2 AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM cart_additions
  WHERE added_date >= '2025-03-01'

  UNION ALL

  SELECT
    'Checkout Started' AS step,
    3 AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM checkout_starts
  WHERE checkout_date >= '2025-03-01'

  UNION ALL

  SELECT
    'Purchase Completed' AS step,
    4 AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM orders
  WHERE order_date >= '2025-03-01'
)
SELECT
  step,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_order), 2) AS overall_conversion,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_order), 2) AS step_conversion
FROM funnel_steps
ORDER BY step_order;

WITH customer_rfm AS (
  SELECT
    customer_id,
    CURRENT_DATE - MAX(order_date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(total_amount) AS monetary
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
  GROUP BY customer_id
),
rfm_scores AS (
  SELECT
    customer_id,
    recency_days,
    frequency,
    monetary,
    NTILE(5) OVER (ORDER BY recency_days DESC) AS r_score,
    NTILE(5) OVER (ORDER BY frequency) AS f_score,
    NTILE(5) OVER (ORDER BY monetary) AS m_score
  FROM customer_rfm
)
SELECT
  customer_id,
  recency_days,
  frequency,
  ROUND(monetary, 2) AS monetary_value,
  r_score || f_score || m_score AS rfm_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 'New Customers'
    WHEN r_score <= 2 AND f_score >= 3 THEN 'At Risk'
    WHEN r_score <= 2 AND f_score <= 2 THEN 'Lost'
    ELSE 'Potential'
  END AS customer_segment
FROM rfm_scores
ORDER BY r_score DESC, f_score DESC, m_score DESC
LIMIT 10;

WITH last_touch AS (
  SELECT DISTINCT ON (o.order_id)
    o.order_id,
    o.customer_id,
    o.total_amount,
    m.channel,
    m.interaction_date
  FROM orders o
  LEFT JOIN marketing_interactions m
    ON o.customer_id = m.customer_id
    AND m.interaction_date <= o.order_date
  WHERE o.order_date >= '2025-03-01'
  ORDER BY o.order_id, m.interaction_date DESC
)
SELECT
  channel,
  COUNT(*) AS attributed_orders,
  SUM(total_amount) AS attributed_revenue,
  ROUND(AVG(total_amount), 2) AS avg_order_value,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct_of_orders
FROM last_touch
GROUP BY channel
ORDER BY attributed_revenue DESC;

-- Index on date columns for time-series analysis
CREATE INDEX idx_orders_date ON orders(order_date);

-- Index on foreign keys for joins
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index for common query patterns
CREATE INDEX idx_orders_date_customer ON orders(order_date, customer_id);

-- Good: Clear, readable structure
WITH daily_sales AS (
  SELECT DATE(order_date) AS sale_date, SUM(total_amount) AS revenue
  FROM orders
  WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY DATE(order_date)
)
SELECT
  sale_date,
  revenue,
  AVG(revenue) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales;

-- Good: Aggregate first, then join
WITH customer_totals AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, ct.order_count, ct.total_spent
FROM customers c
JOIN customer_totals ct ON c.customer_id = ct.customer_id;

-- Avoid: Joining then aggregating creates a large intermediate result
-- SELECT c.name, COUNT(*), SUM(o.total_amount)
-- FROM customers c
-- JOIN orders o ON c.customer_id = o.customer_id
-- GROUP BY c.name;

-- Wrong: Division by zero or NULL
SELECT
  product_id,
  SUM(quantity_sold) / COUNT(*) AS avg_per_order
FROM order_items
GROUP BY product_id;

-- Correct: Use COUNT(column) to exclude NULLs, or COALESCE
SELECT
  product_id,
  COALESCE(SUM(quantity_sold) / NULLIF(COUNT(quantity_sold), 0), 0) AS avg_per_order
FROM order_items
GROUP BY product_id;

-- Wrong: Misses orders on 2025-03-31
SELECT * FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-03-31';

-- Correct: Include the entire last day
SELECT * FROM orders
WHERE order_date >= '2025-03-01' AND order_date < '2025-04-01';