MySQL to get customer ID and 20th transaction date - SQL query

Question

I am unable to make a query to get a list of customer IDs and the date of their 20th purchase.

I got a table named transactions with columns named customer_id and purchase_date. Each row in the table equals a 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 this and assumed that I have to count the number of times customer_id is mentioned and if the count is equal to 20, return the id number.

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

How do I make it return a list of customer_id and the date of the 20th transaction?

Answer 1

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;

Answer 2

You need to select the transaction row belonging to customer_id and filter the results by row 20

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