sql-analysis

SQL Data Analysis (SQL Analysis)

Use Cases

• Business metric queries (GMV / DAU / conversion rate)
• Funnel analysis
• Retention analysis (N-day retention)
• Year-over-year and month-over-month comparison
• Window functions (ranking / cumulative / moving average)
• CTE recursion (hierarchy / path)

Core Principles

1. 先明确问题再写 SQL
2. 用 CTE 分步骤（可读）
3. 注意时区和日期边界
4. 排除测试数据 / 异常值
5. 大表注意性能（索引 / 分区）
6. 结果必须可解释

Common Patterns

Funnel Analysis

WITH funnel AS (
  SELECT
    COUNT(DISTINCT CASE WHEN event = 'view' THEN user_id END) AS step1_view,
    COUNT(DISTINCT CASE WHEN event = 'add_cart' THEN user_id END) AS step2_cart,
    COUNT(DISTINCT CASE WHEN event = 'checkout' THEN user_id END) AS step3_checkout,
    COUNT(DISTINCT CASE WHEN event = 'payment' THEN user_id END) AS step4_payment
  FROM events
  WHERE created_at BETWEEN '2026-05-01' AND '2026-05-31'
)
SELECT
  step1_view,
  step2_cart,
  ROUND(step2_cart * 100.0 / step1_view, 1) AS cart_rate,
  step3_checkout,
  ROUND(step3_checkout * 100.0 / step2_cart, 1) AS checkout_rate,
  step4_payment,
  ROUND(step4_payment * 100.0 / step3_checkout, 1) AS payment_rate
FROM funnel;

N-Day Retention

WITH first_day AS (
  SELECT user_id, MIN(DATE(created_at)) AS first_date
  FROM events
  GROUP BY user_id
),
retention AS (
  SELECT
    f.first_date,
    DATE(e.created_at) - f.first_date AS day_n,
    COUNT(DISTINCT e.user_id) AS retained
  FROM events e
  JOIN first_day f ON e.user_id = f.user_id
  GROUP BY f.first_date, day_n
)
SELECT
  first_date,
  MAX(CASE WHEN day_n = 0 THEN retained END) AS d0,
  MAX(CASE WHEN day_n = 1 THEN retained END) AS d1,
  MAX(CASE WHEN day_n = 7 THEN retained END) AS d7,
  MAX(CASE WHEN day_n = 30 THEN retained END) AS d30
FROM retention
GROUP BY first_date
ORDER BY first_date;

Window Functions

-- 排名
SELECT user_id, amount,
  RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;

-- 累计
SELECT date, revenue,
  SUM(revenue) OVER (ORDER BY date) AS cumulative
FROM daily_revenue;

-- 移动平均（7日）
SELECT date, revenue,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue;

Supporting Templates

• templates/sql-analysis-template.md — SQL analysis record template

Quality Self-Check

□ 问题明确
□ 数据源正确
□ 时区处理
□ 排除异常
□ 性能可接受
□ 结果可解释
□ CTE 可读

Common Pitfalls

1. Inconsistent time zones——UTC vs Local differs by 8 hours
2. Test data not excluded——metrics distorted
3. COUNT vs COUNT DISTINCT——deduplication omitted
4. NULL involved in calculation——result deviation
5. Full scan of large tables——timeout

Collaboration with Other Skills

上游：metric-design → 指标定义
下游：data-visualization → 图表展示
下游：analysis-report → 纳入报告