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?
My solution:
See: DBFIDDLE
For MySQL5.7, see: DBFIDDLE
You need to select the transaction row belonging to customer_id and filter the results by row 20