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

随便写的一大段竟然很快就通过了。。。。

select  device_id,
concat(
 concat(year(event_date),'年'),
 concat(substr(event_date,6,2),'月')   
) as ym,
count(device_id) as cnt from
(select *,
(case when month(event_date) in (1,3,5,7,8,10,12) then 31
      when month(event_date) in (4,6,9,11) then 30
      else 28
end ) as last_day
from question_practice_detail) a
where day(event_date) between a.last_day-6 and a.last_day
group by device_id,ym
order by device_id,ym