题解 | #每个6/7级用户活跃情况#

with user AS( select uid from user_info where level="6" or level="7" )

SELECT u.uid, COUNT(DISTINCT DATE_FORMAT(er.start_time,'%Y%m'))

• count(DISTINCT DATE_FORMAT(pr.submit_time,'%Y%m'))

• COUNT(DISTINCT case when DATE_FORMAT(er.start_time,'%Y%m')=DATE_FORMAT(pr.submit_time,'%Y%m') THEN 1 end) AS act_month_total, COUNT(DISTINCT case when year(er.start_time)='2021' then DAY(er.start_time) end) + COUNT(DISTINCT case when year(pr.submit_time)='2021' then DAY(pr.submit_time) end) -COUNT(DISTINCT case when DAY(er.start_time)=DAY(pr.submit_time) then 1 end)AS act_days_2021, COUNT(DISTINCT case when year(er.start_time)='2021' then DAY(er.start_time) end) AS act_days_2021_exam, COUNT(DISTINCT case when year(pr.submit_time)='2021' then DAY(pr.submit_time) end) AS act_days_2021_queston from (user u LEFT JOIN exam_record er ON u.uid=er.uid) LEFT JOIN practice_record pr ON u.uid=pr.uid GROUP BY u.uid ORDER BY act_month_total DESC,act_days_2021 DESC;