clIckhouse 游戏数据分析实战，计算留存LTV

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

计算留存

	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_detai