Comment on page
This section contains reference documentation for the ROW_NUMBER function.
Assigns a row number to all the rows in a specified table.
Order transactions by the payment date and assign them row numbers.
select customer_id, payment_date, amount, ROW_NUMBER() OVER(ORDER BY payment_date from payment;
WITH payment_cte as (SELECT ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY amount DESC), customer_id, payment_date, amount from payment) SELECT row_number, customer_id, payment_date, amount from payment_cte WHERE row_number <= 2;
Find the number of transactions ranked for each customer. The customer with the highest number of transactions will have a rank of 1, and so on. Order records by the total transactions in descending order. In your rankings, return a unique rank value (to cover multiple customers with the same number of transactions).
SELECT customer_id, count(*), ROW_NUMBER() OVER(ORDER BY count(*) DESC, customer_id ASC) from payment GROUP BY customer_id;