ROW_NUMBER

This section contains reference documentation for the ROW_NUMBER function.

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;

customer_id	payment_date	amount	row_number
416	2023-02-14 21:21:59.996577	2.99	1
516	2023-02-14 21:23:39.996577	4.99	2
239	2023-02-14 21:29:00.996577	4.99	3
592	2023-02-14 21:41:12.996577	6.99	4
49	2023-02-14 21:44:52.996577	0.99	5
264	2023-02-14 21:44:53.996577	3.99	6
46	2023-02-14 21:45:29.996577	4.99	7
481	2023-02-14 22:03:35.996577	2.99	8
139	2023-02-14 22:11:22.996577	2.99	9
595	2023-02-14 22:16:01.996577	2.99	10

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;

row_number	customer_id	payment_date	amount
1	1	2023-02-15 19:37:12.996577	9.99
2	1	2023-04-11 08:42:12.996577	7.99
1	2	2023-04-30 12:16:09.996577	10.99
2	2	2023-04-30 14:49:39.996577	8.99
1	3	2023-04-27 18:51:38.996577	8.99
2	3	2023-03-21 19:19:14.996577	10.99
1	4	2023-03-18 03:43:10.996577	10.99
2	4	2023-03-20 11:24:06.996577	10.99

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;

customer_id	count	row_number
148	45	1
245	42	2
144	39	3
253	39	4
410	36	5
368	34	6