题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#

SELECT act_time,d_dau AS dau , ROUND(IF(new_num IS NULL ,0, new_num/d_dau),2) AS uv_new_ratio FROM ( SELECT act_time ,COUNT(DISTINCT uid) AS d_dau # 求每天的活跃 FROM ( SELECT uid, DATE(in_time) AS act_time FROM tb_user_log UNION ALL SELECT uid,DATE(out_time) AS act_time FROM tb_user_log ) AS tb1 #避免遗漏跨天活跃 需要 nuion GROUP BY act_time ) AS tb4 LEFT JOIN ( SELECT re_time,COUNT(uid) AS new_num # 求每天的新用户 FROM ( SELECT uid,MIN( DATE(in_time)) AS re_time FROM tb_user_log GROUP BY uid ) AS tb2 GROUP BY re_time ) AS tb3 ON tb3.re_time = tb4.act_time ORDER BY act_time;