题解 | 用户每月最后一周的练题数

select device_id,ym,count(*) cnt from
(select device_id,concat(year(event_date),"年",mid(event_date,6,2),"月") ym,(case when day(event_date) between day(last_day(event_date)-6) and day(last_day(event_date)) then 1 else 0 end) iflstweek from
question_practice_detail) as newtable
where iflstweek=1
group by device_id,ym
order by device_id,ym

last_day 可以取出当前月份的最后一天 比如31-6=25 ，实际的最后七天是 25 26 27 28 29 30 31 七天