hive sql用户留存计算

select 
	first_day,
	by_day,
	count(orderid) as total_num_of_order
from
(select 
	uuid,
	first_day,
	activate_day,
	orderid,
	datediff(activate_day,first_day) as by_day
from
	(select 
		t1.uuid,
		from_unixtime(unix_timestamp(t1.dt,'yyyymmdd'),'yyyy-mm-dd') as first_day,
		from_unixtime(unix_timestamp(t2.dt,'yyyymmdd'),'yyyy-mm-dd') as activate_day,
		t2.orderid
	from
		(select 
			distinct uuid,
			dt
		from table_new_user
		where installtype = 0 and dt >=20210401) t1 
		left join
		(select 
			distinct uuid,
			dt,
			orderid
		from table_user_action
		where dt >= 20210401) t2 on t1.uuid = t2.uuid
		) as tmp1
	) as tmp2
where first_day = '20210401'
group by first_day,by_day
order by first_day desc, by_day asc
limit 10;

reference：https://www.isky.wang/notes/calcu-user-retention-by-sql/