用户3年内不同级别违法次数 -- SQL面试题

思路：
三年内应该是指用户取得驾照后的3年内，不同年份不同违法次数的分布。
第一步，按用户分组，违法时间降序排列；
第二步，违法表与用户信息表关联
第三步，计算违法时间与取证时间之间的差值
第四步，筛选出时间差小于等于3年的，分组后，计数

/*
insert into `用户表`
VALUES
(10023,'2015-04-21');

insert into `违法表`
VALUES
(10023,'一级','2017-04-21 14:20:20'),
(10023,'二级','2016-04-21 14:20:20'),
(10023,'二级','2017-06-21 14:20:20'),
(10023,'三级','2018-06-21 14:20:20');
*/
 SELECT id,year,`违法级别`, diff,count(diff) from(
SELECT * ,TIMESTAMPDIFF(YEAR,`领证时间`,date(`违法时间`)) as diff,year(`违法时间`) as year FROM
(SELECT * from `违法表` ) a
LEFT JOIN 
(SELECT id as id1, `领证时间` from `用户表` ) b
ON a.id = b.id1
) c
where diff <4
GROUP BY id,year ,`违法级别`
ORDER BY id,year ,`违法级别`