我就是这样做的.例如，如果"首次"交易同时发生，该解决方案就可以正常工作.其他人也一样

• "first_to_last"是一个递归查询，只显示我们需要获取事务的数字(在您的情况下是1到3).这使得查询很容易调整，以防您突然需要的不是前3个事务，而是前10个事务

• "编号"-按日期对交易进行排序

• 主查询连接前两个CTE，并用"第一"、"第二"和"第三"等词替换数字.除了硬编码值，我没有找到其他方法.

with recursive first_to_last(step) as (
  select 1
  union all 
  select step + 1 
    from first_to_last
   where step < 3 -- how many lines to display
),
numbered as (
  select dense_rank() over(partition by user_id order by created_at) rnk, created_at, user_id
    from transactions
)

select user_id,
       concat(case when f.step = 1 then 'first_deposit: '
                   when f.step = 2 then 'second_deposit: '
                   when f.step = 3 then 'third_deposit: '
              end,
              count(rnk))
  from numbered n
  join first_to_last f
    on n.rnk = f.step
 group by user_id, f.step
 order by user_id, f.step

dbfiddle

UPD.对附加问题的回答:"我只想计算所有第一、第二和任何不是第一或第二的存款"

只需删除"从第一个到最后一个"cte

with numbered as (
  select dense_rank() over(partition by user_id order by created_at) rnk, created_at, user_id
    from transactions
)

select user_id,
       concat(case when n.rnk = 1 then 'first_deposit: '
                   when n.rnk = 2 then 'second_deposit: '
                   else 'other_deposits: ' 
              end,
              count(rnk))
  from numbered n
 group by user_id, case when n.rnk = 1 then 'first_deposit: '
                   when n.rnk = 2 then 'second_deposit: '
                   else 'other_deposits: ' 
              end
 order by user_id, rnk

UPD2.3列输出:第一列、第二列和其他列

with numbered as (
  select dense_rank() over(partition by user_id order by created_at) rnk, created_at, user_id
    from transactions
)

select 
       sum(case when n.rnk = 1 then 1 else 0 end) first_deposit,
       sum(case when n.rnk = 2 then 1 else 0 end)  second_deposit,
       sum(case when n.rnk not in (1,2) then 1 else 0 end) other_deposit
  from numbered n