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

select device_id,ym,count(question_id) cnt
from
(select device_id,question_id,event_date,date2,date3,date_format(event_date,'%Y年%m月') ym
from
(select date_format(event_date,'%Y-%m-01') date #当月第一天
 ,date_add(date_format(event_date,'%Y-%m-01'),interval 1 month) date1 #下月第一天
 ,date_sub(date_add(date_format(event_date,'%Y-%m-01'),interval 1 month),interval 7 day) date2 #当月最后一周第一天
 ,date_sub(date_add(date_format(event_date,'%Y-%m-01'),interval 1 month),interval 1 day) date3 #当月最后一周最后一天
 ,id
 from question_practice_detail) a right join question_practice_detail q using(id)
 where  event_date between date2 and date3 #筛选出当月最后一周
) b
group by device_id,ym
order by device_id,ym