题解 | #计算用户8月每天的练题数量#

select t2.time as dt, t2.h_cnt as dau, round(coalesce(t1.x_cnt,0) / t2.h_cnt, 2) as uv_new_ratio -- 因为是右连接，所以X1的用户数可能是null，这样算出来结果会是None from ( -- 计算每天的新用户数，先计算每位用户的最早活跃时间，然后按照最早活跃时间统计每天的新用户数 select e_dt, count(uid) as x_cnt from ( select uid, date(min(in_time)) as e_dt from tb_user_log group by uid ) a group by e_dt ) t1 RIGHT JOIN ( -- 计算每天的活跃用户数，先利用union得到用户活跃表 select time, count(uid) as h_cnt from ( select uid, date(in_time) as time from tb_user_log union select uid, date(out_time) as time from tb_user_log ) b group by time ) t2 on t1.e_dt = t2.time order by t2.time