MySQL - SQL query to get the customer ids and date of 20th transaction-CodePudding

I am having trouble coming up with a query to get a list of customer ids and the date of their 20th purchase.

I am given a table called transactions with the column name customer_id and purchase_date. Each row in the table is equal to one transaction.

customer_id	purchase_date
1	2020-11-19
2	2022-01-01
3	2021-12-05
3	2021-12-09
3	2021-12-16

I tried to do it like this and assumed I would have to count the number of times the customer_id has been mentioned and return the id number if the count equals 20.

SELECT customer_id, MAX(purchase_date)
FROM transactions
(
     SELECT customer_id,
     FROM transactions
     GROUP BY customer_id
     HAVING COUNT (customer_id) =20
)

How can I get this to return the list of customer_id and only the date of the 20th transaction?

CodePudding user response：

Try:

 with cte as (select *,
                    row_number() over(partition by customer_id order by purchase_date asc) as row_num
                    from transactions
            )
select customer_id,purchase_date
from cte
where row_num=20;

order by purchase_date asc in the row_number function is to order the purchases so that you can only select the 20th purchase.

CodePudding user response：

My solution:

select *
from transactions t
inner join (
   select 
      customer_id,
      purchase_date,
      row_number() over (partition by customer_id order by purchase_date) R
   from transactions) x on x.purchase_date=t.purchase_date
                       and x.customer_id=t.customer_id
where x.R=20;

see: DBFIDDLE

For MySQL5.7, see: DBFIDDLE

set @r:=1;
select *
from transactions t
inner join (
   select 
      customer_id,
      purchase_date,
      @r:=@r 1 R
   from transactions) x on x.purchase_date=t.purchase_date
                       and x.customer_id=t.customer_id
where x.R=20;

CodePudding user response：

You need to select the rows of transactions belonging to the customer_id and filter the result by the 20th row

SELECT * FROM (
    SELECT customer_id, purchase_date, ROW_NUMBER() OVER(
        PARTITION BY customer_id
        ORDER BY purchase_date DESC
    ) AS nth
    FROM transactions
) as t WHERE nth = 20

CodePudding user response：

Use row_number = 20

SELECT 
  customer_id,
  purchase_date as date_t_20
FROM 
(
     SELECT  
       customer_id,
       purchase_date,
       Row_number() OVER (
         PARTITION BY customer_id 
          ORDER BY purchase_date) AS rn
     FROM transactions
) T
WHERE rn = 20;