clickhouse 计算留存，LTV

clIckhouse大数据分析-游戏玩家留存，LTV 计算 SQL 案例

计算留存

SELECT
	add_time,
	sum( add_num ) AS add_count,
	countIf ( remain = 1 ) AS remain1,
	countIf ( remain = 2 ) AS remain2,
	countIf ( remain = 3 ) AS remain3,
	countIf ( remain = 4 ) AS remain4,
	countIf ( remain = 5 ) AS remain5,
	countIf ( remain = 6 ) AS remain6,
	countIf ( remain = 7 ) AS remain7,
	countIf ( remain = 8 ) AS remain8,
	countIf ( remain = 9 ) AS remain9,
	countIf ( remain = 10 ) AS remain10,
	countIf ( remain = 11 ) AS remain11 
FROM
	(
SELECT
	b.add_time,
	( CASE WHEN a.login_time = b.add_time THEN 1 ELSE 0 END ) AS add_num,
	( CASE WHEN a.login_time >= b.add_time THEN dateDiff( 'day', b.add_time, a.login_time ) ELSE - 1 END ) AS remain 
FROM
	( SELECT role_id, cur_ts AS login_time FROM role_login_details WHERE cur_ts BETWEEN '2024-01-01' AND '2024-02-12' GROUP BY cur_ts, role_id ) AS a,
	( SELECT role_id, min( cur_ts ) AS add_time FROM role_create_details WHERE cur_ts BETWEEN '2024-01-01' AND '2024-02-12' GROUP BY role_id ) AS b 
WHERE
	a.role_id = b.role_id 
	) AS t 
WHERE
	remain >= 0 
	AND remain <= 11 
	AND add_time <= '2024-02-01' 
GROUP BY
	add_time 
ORDER BY
	add_time ASC;
​


​

计算LTV

SELECT
	add_time,
	sumIf ( money, ltv = 0 ) AS ltv0,
	sumIf ( money, ltv <= 1 ) AS ltv1,
	sumIf ( money, ltv <= 2 ) AS ltv2,
	sumIf ( money, ltv <= 3 ) AS ltv3,
	sumIf ( money, ltv <= 4 ) AS ltv4,
	sumIf ( money, ltv <= 5 ) AS ltv5,
	sumIf ( money, ltv <= 6 ) AS ltv6,
	sumIf ( money, ltv <= 7 ) AS ltv7,
	sumIf ( money, ltv <= 8 ) AS ltv8,
	sumIf ( money, ltv <= 9 ) AS ltv9,
	sumIf ( money, ltv <= 10 ) AS ltv10,
	sumIf ( money, ltv <= 11 ) AS ltv11,
	sumIf ( money, ltv <= 12 ) AS ltv12,
	sumIf ( money, ltv <= 13 ) AS ltv13 
FROM
	(
SELECT
	b.add_time,
	( CASE WHEN a.pay_time >= b.add_time THEN dateDiff( 'day', b.add_time, a.pay_time ) ELSE - 1 END ) AS ltv,
	a.money 
FROM
	( SELECT role_id, cur_ts AS pay_time, sum( money_amount ) / 100 AS money FROM recharge_details WHERE cur_ts BETWEEN '2024-01-01' GROUP BY cur_ts, role_id ) AS a,
	( SELECT role_id, min( cur_ts ) AS add_time FROM role_create_details WHERE cur_ts BETWEEN '2024-01-01' GROUP BY role_id ) AS b 
WHERE
	a.role_id = b.role_id 
	) AS t 
WHERE
	ltv >= 0 
	AND ltv <= 13 
	AND add_time <= '2024-02-01' 
GROUP BY
	add_time 
ORDER BY
	add_time ASC;