Get all the transaction details of a user of their 2nd month of transaction

Trying to get the 2nd transaction month details for all the customers

Date          User_id      amount
2021-11-01      1           100
2021-11-21      1           200
2021-12-20      2           110
2022-01-20      2           200
2022-02-04      1            50
2022-02-21      1           100
2022-03-22      2           200

For every customer get all the records in the month of their 2nd transaction (There can be multiple transaction in a month and a day by a particular user)

Expected Output

Date       User_id      amount
2022-02-04      1          50
2022-02-21      1          100
2022-01-20      2          200

CodePudding user response：

You can use dense_rank:

select Date, User_id, amount from
(select *, dense_rank() over(partition by User_id order by year(Date), month(date)) r
from table_name) t
where r = 2;

Fiddle

CodePudding user response：

If dense_rank is an option you can:

with cte1 as (
    select *, extract(year_month from date) as yyyymm
    from t
), cte2 as (
    select *, dense_rank() over (partition by user_id order by yyyymm) as dr
    from cte1
)
select *
from cte2
where dr = 2

Note that it is possible to write the above using one cte.