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/