题解 | #月总刷题数和日均刷题数#

SELECT COALESCE(a.month, '2021汇总') AS submit_month,
       COUNT(submit_time) AS month_q_cnt,
       ROUND(
             COUNT(submit_time)/MAX(month_days)
          ,3) AS avg_day_q_cnt
FROM(
    SELECT DATE_FORMAT(submit_time,'%Y%m') AS month,
           uid, 
           question_id,
           submit_time, 
           DAYOFMONTH(LAST_DAY(submit_time)) AS month_days
    FROM practice_record
    WHERE YEAR(submit_time) = '2021'
    ) AS a
GROUP BY a.month
WITH ROLLUP
;