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.
bigint()
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 |
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 |
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