题解 | #月总刷题数和日均刷题数#
月总刷题数和日均刷题数
https://www.nowcoder.com/practice/f6b4770f453d4163acc419e3d19e6746
我就要搞不一样的
with t1 as ( select '01' month, 31 days from dual UNION select '02' month, 28 days from dual UNION select '03' month, 31 days from dual UNION select '04' month, 30 days from dual UNION select '05' month, 31 days from dual UNION select '06' month, 30 days from dual UNION select '07' month, 31 days from dual UNION select '08' month, 31 days from dual UNION select '09' month, 30 days from dual UNION select '10' month, 31 days from dual UNION select '11' month, 30 days from dual UNION select '12' month, 31 days from dual ), t2 as ( select uid, DATE_FORMAT(submit_time, '%Y%m') submit_month, DATE_FORMAT(submit_time, '%m') month from practice_record where DATE_FORMAT(submit_time, '%Y')=2021 ), t3 as ( select t2.submit_month, count(t2.uid) month_q_cnt, round(count(t2.uid)/t1.days,3) avg_day_q_cnt from t1 JOIN t2 on t1.month = t2.month group by t2.submit_month, t1.days order by submit_month ), t4 as ( select "2021汇总" submit_month, count(uid) month_q_cnt, round(count(uid)/31,3) avg_day_q_cnt from t2 ), t5 as ( select * from t3 UNION select * from t4 ) select* from t5
投递小米集团等公司10个岗位 >