SQL练习笔记——查询每个用户在首次付费后 30 天内的总付费次数

订单表中包含信息如下，查询每个用户在首次付费后 30 天内的总付费次数

解题思路

#1.每个用户的首次付费时间和首次付费的30天后

select users_id,
	min(`time`) as 首次付费时间,
	adddate(min(`time`),interval 30 day) as 首次付费后30天
from trade_order
group by users_id;

#2.左连接

select o.*,t.首次付费后30天
from trade_order o
left join 
(select users_id,min(`time`) as 首次付费时间,adddate(min(`time`),interval 30 day) as 首次付费后30天
from trade_order 
group by users_id)t
on o.users_id =t.users_id;

#3.进行分组计数和时间过滤

select o.users_id ,count(*) as 总次数
from trade_order o
left join 
(select users_id,min(`time`) as 首次付费时间,adddate(min(`time`),interval 30 day) as 首次付费后30天
from trade_order 
group by users_id)t
on o.users_id =t.users_id
where o.`time` <= t.首次付费后30天
group by o.users_id;

输出结果：