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

SELECT
	COALESCE(submit_month,'2021汇总') submit_month,
	month_q_cnt,
	avg_day_q_cnt
FROM(
	SELECT 
		DATE_FORMAT(submit_time,'%Y%m') submit_month,
		count(uid) month_q_cnt,
		ROUND(COUNT(uid)/MAX(a.days),3) avg_day_q_cnt
	FROM (
		SELECT *, day(LAST_DAY(submit_time)) days
		FROM practice_record WHERE YEAR(submit_time)='2021'
	) a
	GROUP BY DATE_FORMAT(submit_time,'%Y%m')
	WITH ROLLUP
) b;