进阶实战题

SELECT
  COUNT(DISTINCT a1.user_id) AS total,
  COUNT(DISTINCT a2.user_id) AS retained,
  ROUND(COUNT(DISTINCT a2.user_id) * 1.0 / COUNT(DISTINCT a1.user_id), 4) AS retain_rate
FROM reg a1
LEFT JOIN dau a2
  ON a1.user_id = a2.user_id
  AND a2.active_date = DATE_ADD(a1.reg_date, INTERVAL 7 DAY)
WHERE a1.reg_date = '2023-06-01';

SELECT
  SUM(step1) AS home_sessions,
  SUM(step2) AS detail_sessions,
  SUM(step3) AS pay_sessions,
  ROUND(SUM(CASE WHEN step1=1 AND step2=1 AND step3=1 THEN 1 ELSE 0 END) * 1.0 / NULLIF(SUM(step1), 0), 4) AS full_cvr
FROM (
  SELECT session_id,
    MAX(CASE WHEN action = 'home' THEN 1 ELSE 0 END) AS step1,
    MAX(CASE WHEN action = 'detail' THEN 1 ELSE 0 END) AS step2,
    MAX(CASE WHEN action = 'pay' THEN 1 ELSE 0 END) AS step3
  FROM logs
  GROUP BY session_id
) t;

SELECT a1.active_date,
  COUNT(DISTINCT a2.user_id) AS dau_7d
FROM (SELECT DISTINCT active_date FROM dau) a1
JOIN dau a2
  ON a2.active_date BETWEEN DATE_SUB(a1.active_date, INTERVAL 6 DAY) AND a1.active_date
GROUP BY a1.active_date
ORDER BY a1.active_date;

SELECT a1.product_id AS product_a, a2.product_id AS product_b,
  COUNT(*) AS co_purchase_cnt
FROM order_items a1
JOIN order_items a2
  ON a1.order_id = a2.order_id AND a1.product_id > a2.product_id
GROUP BY a1.product_id, a2.product_id
ORDER BY co_purchase_cnt DESC
LIMIT 1;

SELECT is_promotion,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN amount > 500 THEN 1 ELSE 0 END) AS high_value_cnt,
  ROUND(SUM(CASE WHEN amount > 500 THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 4) AS high_value_rate
FROM orders
GROUP BY is_promotion;

WITH new_users AS (
  SELECT user_id FROM users WHERE YEAR(reg_date) = 2023
),
first_orders AS (
  SELECT o.user_id, o.order_date AS first_date, o.amount AS first_amount
  FROM (
    SELECT o.user_id, o.order_date, o.amount,
      ROW_NUMBER() OVER(PARTITION BY o.user_id ORDER BY o.order_date) AS rn
    FROM orders o
    JOIN new_users u ON o.user_id = u.user_id
  ) o
  WHERE rn = 1
),
repurchase AS (
  SELECT f.user_id,
    MAX(CASE WHEN o.order_date > f.first_date
      AND DATEDIFF(o.order_date, f.first_date) BETWEEN 30 AND 60
      THEN 1 ELSE 0 END) AS is_repurchase
  FROM first_orders f
  LEFT JOIN orders o ON f.user_id = o.user_id
  GROUP BY f.user_id
)
SELECT
  ROUND(AVG(f.first_amount), 2) AS avg_first_order,
  ROUND(AVG(r.is_repurchase), 4) AS repurchase_rate
FROM first_orders f
JOIN repurchase r ON f.user_id = r.user_id;

SELECT u.user_id, u.name,
  COALESCE(SUM(o.amount), 0) AS year_amount
FROM users u
LEFT JOIN orders o
  ON u.user_id = o.user_id
  AND YEAR(o.order_date) = 2023
GROUP BY u.user_id, u.name
ORDER BY u.user_id;

SELECT a1.date,
  SUM(CASE WHEN a2.login_cnt > 3 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS rate
FROM (
  SELECT DISTINCT user_id, date
  FROM user_activity
  WHERE action = 'purchase'
    AND MONTH(date) = 7
) a1
LEFT JOIN (
  SELECT user_id,
    SUM(CASE WHEN action = 'login' THEN 1 ELSE 0 END) AS login_cnt
  FROM user_activity
  WHERE MONTH(date) = 7
  GROUP BY user_id
) a2 ON a1.user_id = a2.user_id
GROUP BY a1.date
ORDER BY a1.date;

SELECT dept_id,
  COUNT(DISTINCT emp_id) AS employee_count
FROM history
WHERE start_date <= '2023-12-31'
  AND COALESCE(end_date, '9999-01-01') > '2023-12-31'
GROUP BY dept_id;