MySQL:一道简单的面试题（要求20分钟内手写）

前言：群里一位同学的面试题，看起来难度不高，但是蛮考验基础的，而且要求20分钟内手写，所以并不容易，对我这种平时不大用SQL的人来说有点难度，用了将近一个小时才完成，而且还是用模拟数据做出来的=,=，真实面试的话直接就挂了吧。。。。

select t4.`购买时间` as 日期, 
sum(case when t4.离首次购买间隔月份=0 then 1 else 0 end) as 当日首次购买用户数,
sum(case when t4.离首次购买间隔月份=1 then 1 else 0 end) as 次月复购用户数,
sum(case when t4.离首次购买间隔月份=2 then 1 else 0 end) as 第三月复购用户数,
sum(case when t4.离首次购买间隔月份=3 then 1 else 0 end) as 第四月复购用户数,
sum(case when t4.离首次购买间隔月份=4 then 1 else 0 end) as 第五月复购用户数,
sum(case when t4.离首次购买间隔月份=5 then 1 else 0 end) as 第六月复购用户数,
sum(case when t4.离首次购买间隔月份=6 then 1 else 0 end) as 第七月复购用户数,
sum(case when t4.离首次购买间隔月份=7 then 1 else 0 end) as 第八月复购用户数,
sum(case when t4.离首次购买间隔月份=8 then 1 else 0 end) as 第九月复购用户数,
sum(case when t4.离首次购买间隔月份=9 then 1 else 0 end) as 第十月复购用户数,
sum(case when t4.离首次购买间隔月份=10 then 1 else 0 end) as 第十一月复购用户数,
...
sum(case when t4.离首次购买间隔月份=20 then 1 else 0 end) as 第二十月复购用户数
from 
(select t3.`用户id`, t3.`购买时间`, ((year(t3.`购买时间`)-year(t3.首次购买日期))*12 + MONTH(t3.`购买时间`) - month(t3.首次购买日期)) as 离首次购买间隔月份 from
(select t1.`用户id`, t1.`购买时间`, t2.`首次购买日期` from `购买记录表`t1
left join
(select `用户id`, min(`购买时间`) as 首次购买日期 from `购买记录表` group by `用户id`)t2
on t1.`用户id` = t2.`用户id`)t3)t4
group by t4.`购买时间`
order by t4.`购买时间`;