ROW_NUMBER

Assigns a row number to all the rows in a specified table.

Signature

bigint()

ROW_NUMBER examples

• Order transactions by payment date

• Identify the top two transactions by customer, ordered by transaction amount

• Identify customers with the highest number of transactions

Order transactions by payment date

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;

Identify the top two transactions by customer, ordered by transaction amount

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;

Identify customers with the highest number of transactions

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;

\