Apache Pinot Docs
latest
Ask or search…
K
Comment on page

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

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
Last modified 2mo ago