如何用SQL生成客户保持表/cohort分析

CREATE TABLE customer_retention (
  user_id INT,
  join_date DATE,
  purchase_date DATE,
  PRIMARY KEY (user_id)
);

INSERT INTO customer_retention (user_id, join_date, purchase_date)
VALUES (1, '2021-01-01', '2021-02-15'),
       (2, '2021-01-01', '2021-01-10'),
       (3, '2021-02-01', '2021-02-05'),
       ...

SELECT COUNT(*) AS new_users
FROM customer_retention
WHERE join_date >= '2021-01-01' AND join_date <= '2021-12-31';

SELECT YEAR(join_date) AS join_year, MONTH(join_date) AS join_month, COUNT(*) AS users
FROM customer_retention
GROUP BY join_year, join_month;

SELECT YEAR(join_date) AS join_year, MONTH(join_date) AS join_month,
       COUNT(DISTINCT user_id) AS total_users,
       COUNT(DISTINCT CASE WHEN DATEDIFF(purchase_date, join_date) >= 30 THEN user_id END) AS retained_users,
       (COUNT(DISTINCT CASE WHEN DATEDIFF(purchase_date, join_date) >= 30 THEN user_id END) / COUNT(DISTINCT user_id)) * 100 AS retention_rate
FROM customer_retention
GROUP BY join_year, join_month;