如何修改SQL查询语句以统计用户第二笔存款的对应汇总数据

核心修改点 #

• 原查询通过row_num=1筛选用户首笔存款，要统计第二笔仅需将外层过滤条件改为row_num=2即可
• 同步修改统计结果的别名，避免和首笔存款的统计含义混淆
• 原有子查询的窗口排序逻辑无需调整，ROW_NUMBER() OVER(PARTITION BY bbt.user_id ORDER BY bbt.created_at)已经按用户维度分组、交易时间升序给每笔存款排序，row_num为2天然对应用户第二笔存款

注意事项 #

只有累计至少有2笔符合条件（已执行、金额大于0）的存款的用户才会被纳入统计范围，仅1笔存款的用户不会出现在本次结果中。

修改后的完整SQL代码 #

select "Manager",
count(*) as "Number of second deposits",
sum("Amount, $") as "Sum of second deposits" 
from
(SELECT 
bbt.user_id as "ClientID",
uu.email as "Email",
initcap(split_part(man.email,'@',1)) as "Manager",
bbt.created_at as "Date of transaction",
bbt.payment as "Amount, $",
ROW_NUMBER() OVER(PARTITION BY bbt.user_id ORDER BY bbt.created_at) as row_num
from billing_balancetransaction bbt 
left join users_user uu on bbt.user_id=uu.id
left join users_user man on uu.account_manager_id=man.id
where bbt.executed_at is not null
and bbt.user_id in ('27241','27255','27298','27276','27342','26572','26227','27282','26612','26068','26069','27853','26288','26523','26911')
and bbt.payment>0) t
where row_num=2
group by 1

内容的提问来源于stack exchange，提问作者Yariy Zhlob