Get the 10 most recent orders
select * from co.orders
order by order_datetime desc
fetch first 10 rows only| ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | 1950 | 12-APR-19 09.41.06.642641 PM | 348 | COMPLETE | 23 | 1949 | 11-APR-19 07.14.31.588169 PM | 348 | COMPLETE | 23 | 1948 | 11-APR-19 03.49.45.728115 AM | 319 | COMPLETE | 23 | 1947 | 11-APR-19 03.49.08.760887 AM | 377 | COMPLETE | 23 | 1946 | 10-APR-19 06.22.55.181577 PM | 348 | COMPLETE | 23 | 1945 | 10-APR-19 03.49.31.059138 PM | 341 | COMPLETE | 22 | 1944 | 10-APR-19 10.14.27.406048 AM | 367 | COMPLETE | 19 | 1943 | 10-APR-19 08.14.01.961369 AM | 377 | COMPLETE | 23 | 1942 | 10-APR-19 03.36.17.661842 AM | 203 | COMPLETE | 23 | 1941 | 08-APR-19 11.16.55.199255 PM | 290 | COMPLETE | 23 |
|---|
Add a row number for each customer
select o.*,
row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o| ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 182 | 26-APR-18 03.56.02.680569 AM | 1 | COMPLETE | 1 | 4 | 159 | 20-APR-18 12.51.57.254596 PM | 1 | COMPLETE | 1 | 5 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 1 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 832 | 22-SEP-18 10.12.29.878125 AM | 3 | COMPLETE | 3 | 2 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 3 | 608 | 11-AUG-18 06.57.30.697703 PM | 3 | COMPLETE | 1 | 4 | 544 | 27-JUL-18 05.27.59.314441 AM | 3 | COMPLETE | 1 | 5 | 307 | 27-MAY-18 04.27.28.810727 AM | 3 | COMPLETE | 1 | 6 | 298 | 24-MAY-18 08.30.09.072186 PM | 3 | COMPLETE | 1 | 7 | 63 | 22-MAR-18 06.17.24.214468 AM | 3 | COMPLETE | 1 | 8 | 20 | 01-MAR-18 10.07.32.467335 AM | 3 | COMPLETE | 3 | 9 | 1 | 04-FEB-18 01.20.22.245677 PM | 3 | CANCELLED | 1 | 10 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 920 | 10-OCT-18 04.55.19.471734 AM | 4 | COMPLETE | 4 | 3 | 672 | 23-AUG-18 12.33.23.328945 PM | 4 | COMPLETE | 4 | 4 | 585 | 05-AUG-18 09.58.27.792726 PM | 4 | COMPLETE | 1 | 5 | 506 | 17-JUL-18 09.52.33.762499 PM | 4 | COMPLETE | 1 | 6 | 453 | 02-JUL-18 11.08.52.629924 PM | 4 | COMPLETE | 4 | 7 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 1058 | 03-NOV-18 02.53.59.709450 PM | 6 | COMPLETE | 6 | 3 | 972 | 19-OCT-18 01.08.36.540797 PM | 6 | COMPLETE | 6 | 4 | 769 | 09-SEP-18 01.45.17.970634 PM | 6 | REFUNDED | 1 | 5 | 204 | 30-APR-18 11.39.15.779302 AM | 6 | COMPLETE | 1 | 6 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 1 | 1555 | 24-JAN-19 03.17.49.859502 PM | 7 | COMPLETE | 7 | 2 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 3 | 950 | 15-OCT-18 05.58.32.401980 PM | 7 | COMPLETE | 1 | 4 | 862 | 29-SEP-18 01.30.09.602519 AM | 7 | COMPLETE | 7 | 5 | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | 1 | 1561 | 25-JAN-19 07.56.53.190977 AM | 8 | COMPLETE | 8 | 2 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 3 | 1082 | 07-NOV-18 04.25.22.587208 PM | 8 | COMPLETE | 8 | 4 | 993 | 23-OCT-18 09.12.34.064134 PM | 8 | COMPLETE | 1 | 5 | 430 | 26-JUN-18 07.51.51.514668 PM | 8 | COMPLETE | 1 | 6 | 240 | 08-MAY-18 12.27.09.346498 AM | 8 | COMPLETE | 1 | 7 | 196 | 28-APR-18 04.53.48.264575 PM | 8 | COMPLETE | 1 | 8 | 1260 | 10-DEC-18 12.17.10.374740 AM | 9 | COMPLETE | 9 | 1 | 1102 | 11-NOV-18 09.21.21.436253 AM | 9 | COMPLETE | 9 | 2 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 3 | 928 | 11-OCT-18 04.03.45.848273 AM | 9 | COMPLETE | 1 | 4 |
|---|
Get the last three orders for each customer
with rws as (
select o.*, row_number () over (
partition by customer_id
order by order_datetime desc
) rn
from co.orders o
)
select * from rws
where rn <= 3
order by customer_id, order_datetime desc| ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 1 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 832 | 22-SEP-18 10.12.29.878125 AM | 3 | COMPLETE | 3 | 2 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 3 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 920 | 10-OCT-18 04.55.19.471734 AM | 4 | COMPLETE | 4 | 3 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 1058 | 03-NOV-18 02.53.59.709450 PM | 6 | COMPLETE | 6 | 3 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 1 | 1555 | 24-JAN-19 03.17.49.859502 PM | 7 | COMPLETE | 7 | 2 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 3 | 1817 | 10-MAR-19 09.54.25.125750 PM | 8 | COMPLETE | 8 | 1 | 1561 | 25-JAN-19 07.56.53.190977 AM | 8 | COMPLETE | 8 | 2 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 3 | 1260 | 10-DEC-18 12.17.10.374740 AM | 9 | COMPLETE | 9 | 1 | 1102 | 11-NOV-18 09.21.21.436253 AM | 9 | COMPLETE | 9 | 2 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 3 | 1730 | 23-FEB-19 02.34.28.490023 AM | 10 | COMPLETE | 10 | 1 | 1525 | 21-JAN-19 01.17.54.579876 PM | 10 | COMPLETE | 1 | 2 | 986 | 22-OCT-18 05.59.46.862850 AM | 10 | COMPLETE | 10 | 3 | 1635 | 05-FEB-19 01.18.25.041366 PM | 11 | COMPLETE | 11 | 1 | 1321 | 19-DEC-18 05.28.34.329405 PM | 11 | COMPLETE | 1 | 2 | 960 | 17-OCT-18 08.14.25.241276 AM | 11 | COMPLETE | 1 | 3 | 1637 | 06-FEB-19 02.16.49.481784 AM | 12 | COMPLETE | 1 | 1 | 839 | 24-SEP-18 12.55.09.647618 AM | 12 | COMPLETE | 12 | 2 | 125 | 10-APR-18 12.41.39.268531 PM | 12 | COMPLETE | 1 | 3 | 1498 | 17-JAN-19 05.36.16.018250 AM | 13 | COMPLETE | 13 | 1 | 1418 | 05-JAN-19 04.26.48.901210 AM | 13 | COMPLETE | 13 | 2 | 1052 | 02-NOV-18 04.10.21.166613 AM | 13 | COMPLETE | 1 | 3 | 1560 | 25-JAN-19 05.33.55.537004 AM | 14 | COMPLETE | 1 | 1 | 1483 | 14-JAN-19 07.04.58.793222 PM | 14 | COMPLETE | 14 | 2 | 1471 | 13-JAN-19 06.25.29.562733 AM | 14 | COMPLETE | 14 | 3 | 1486 | 15-JAN-19 03.31.29.057027 PM | 15 | COMPLETE | 15 | 1 | 1400 | 02-JAN-19 05.48.13.206098 AM | 15 | COMPLETE | 15 | 2 | 1195 | 30-NOV-18 05.42.24.627921 PM | 15 | COMPLETE | 1 | 3 | 1451 | 10-JAN-19 07.06.48.689623 PM | 16 | COMPLETE | 16 | 1 | 1448 | 10-JAN-19 06.05.40.626338 PM | 16 | COMPLETE | 1 | 2 | 1439 | 08-JAN-19 03.48.25.232374 PM | 16 | COMPLETE | 1 | 3 | 1552 | 24-JAN-19 12.09.14.646254 PM | 17 | COMPLETE | 17 | 1 | 1200 | 30-NOV-18 11.19.54.798149 PM | 17 | COMPLETE | 1 | 2 | 899 | 06-OCT-18 08.37.49.467272 PM | 17 | COMPLETE | 1 | 3 |
|---|
Get the date of the last order for each customer
select customer_id,
max ( order_datetime )
from co.orders
group by customer_id| CUSTOMER_ID | MAX(ORDER_DATETIME) | 124 | 12-OCT-18 08.15.39.717974 PM | 107 | 13-MAR-19 04.10.34.107048 PM | 14 | 25-JAN-19 05.33.55.537004 AM | 166 | 25-MAR-19 02.52.16.631428 AM | 51 | 20-MAR-19 07.13.19.500719 PM | 281 | 19-MAR-19 06.19.55.352793 AM | 359 | 10-MAR-19 09.21.02.444428 AM | 318 | 21-MAR-19 03.07.31.851060 PM | 161 | 21-MAR-19 07.50.08.213950 PM | 237 | 07-MAR-19 02.59.15.029147 AM | 328 | 20-DEC-18 12.39.35.118770 PM | 6 | 10-JAN-19 06.52.30.324331 PM | 173 | 19-OCT-18 04.30.09.442692 PM | 391 | 18-MAR-19 06.50.34.800957 AM | 27 | 03-FEB-19 05.13.10.955291 AM | 135 | 12-MAR-19 05.09.38.031580 AM | 23 | 20-MAR-18 12.07.13.591684 AM | 226 | 02-FEB-19 08.28.14.386011 AM | 348 | 12-APR-19 09.41.06.642641 PM | 58 | 31-MAR-19 06.29.01.038321 PM | 158 | 06-MAR-19 11.17.49.990617 PM | 108 | 04-MAR-19 05.36.39.891421 PM | 277 | 18-MAR-19 01.24.20.184511 AM | 291 | 26-AUG-18 07.25.53.889570 AM | 125 | 17-MAR-19 06.33.56.523112 AM | 371 | 24-MAR-19 05.06.14.312057 PM | 210 | 08-JUL-18 02.36.38.114618 PM | 72 | 06-FEB-19 04.33.54.085409 AM | 64 | 19-DEC-18 08.08.49.968261 PM | 313 | 16-NOV-18 08.44.20.739015 AM | 113 | 13-AUG-18 08.53.08.143929 AM | 57 | 07-JAN-19 03.05.40.406352 PM | 232 | 03-APR-19 01.40.01.840676 PM | 52 | 08-JAN-19 12.50.52.374027 PM | 50 | 14-MAR-19 02.07.24.615426 AM | 290 | 08-APR-19 11.16.55.199255 PM | 189 | 05-JAN-19 06.57.32.486289 AM | 71 | 16-FEB-19 04.51.59.801488 AM | 136 | 05-APR-19 12.26.49.495590 AM | 344 | 07-MAR-19 06.11.57.595424 PM | 85 | 20-DEC-18 01.53.48.432922 AM | 206 | 31-JAN-19 04.04.00.853750 PM | 88 | 22-SEP-18 07.44.46.434859 PM | 350 | 08-OCT-18 08.13.06.842274 AM | 268 | 10-NOV-18 02.47.04.331650 PM | 180 | 20-FEB-19 04.02.05.809720 PM | 40 | 16-DEC-18 11.47.49.807137 AM | 109 | 30-MAR-19 01.37.15.392243 AM | 7 | 25-MAR-19 11.31.58.636926 PM | 99 | 30-DEC-18 04.09.22.336203 PM |
|---|
Get the date of the last order for each customer and the store where this was made
select customer_id,
max ( order_datetime ),
max ( store_id ) keep (
dense_rank last
order by order_datetime
) last_purchase_store_id
from co.orders
group by customer_id| CUSTOMER_ID | MAX(ORDER_DATETIME) | LAST_PURCHASE_STORE_ID | 1 | 16-JAN-19 08.12.31.926232 AM | 1 | 2 | 09-SEP-18 08.32.41.319898 AM | 2 | 3 | 20-JAN-19 11.42.12.885651 PM | 1 | 4 | 13-JAN-19 01.22.50.411160 AM | 1 | 5 | 31-AUG-18 05.08.24.306909 AM | 1 | 6 | 10-JAN-19 06.52.30.324331 PM | 1 | 7 | 25-MAR-19 11.31.58.636926 PM | 7 | 8 | 10-MAR-19 09.54.25.125750 PM | 8 | 9 | 10-DEC-18 12.17.10.374740 AM | 9 | 10 | 23-FEB-19 02.34.28.490023 AM | 10 | 11 | 05-FEB-19 01.18.25.041366 PM | 11 | 12 | 06-FEB-19 02.16.49.481784 AM | 1 | 13 | 17-JAN-19 05.36.16.018250 AM | 13 | 14 | 25-JAN-19 05.33.55.537004 AM | 1 | 15 | 15-JAN-19 03.31.29.057027 PM | 15 | 16 | 10-JAN-19 07.06.48.689623 PM | 16 | 17 | 24-JAN-19 12.09.14.646254 PM | 17 | 18 | 10-FEB-19 01.15.53.995472 AM | 18 | 19 | 23-FEB-19 08.53.32.461195 PM | 19 | 20 | 09-MAR-19 10.33.24.254670 AM | 20 | 21 | 12-MAR-19 11.56.53.384122 PM | 21 | 22 | 11-MAR-19 07.20.17.942291 AM | 22 | 23 | 20-MAR-18 12.07.13.591684 AM | 1 | 24 | 16-AUG-18 05.47.22.306204 PM | 1 | 25 | 18-OCT-18 09.52.33.531823 PM | 1 | 26 | 21-JAN-19 11.28.57.135392 AM | 1 | 27 | 03-FEB-19 05.13.10.955291 AM | 1 | 28 | 01-FEB-19 10.59.58.576087 PM | 1 | 29 | 27-MAR-19 09.29.34.311413 PM | 23 | 30 | 31-JAN-19 08.32.37.958480 PM | 1 | 31 | 20-DEC-18 11.19.17.528223 AM | 2 | 32 | 11-NOV-18 04.36.12.942269 AM | 1 | 33 | 20-DEC-18 06.10.04.750197 PM | 1 | 34 | 26-SEP-18 04.29.39.472854 AM | 1 | 35 | 05-DEC-18 07.20.08.753049 AM | 6 | 36 | 15-JAN-19 10.51.20.451280 PM | 7 | 37 | 25-SEP-18 02.00.27.715942 PM | 8 | 38 | 03-DEC-18 06.47.29.874991 PM | 9 | 39 | 27-DEC-18 03.04.20.388274 PM | 1 | 40 | 16-DEC-18 11.47.49.807137 AM | 1 | 41 | 10-JAN-19 09.11.04.094560 PM | 12 | 42 | 01-DEC-18 07.42.04.777939 PM | 13 | 43 | 23-JAN-19 09.03.54.597996 PM | 1 | 44 | 25-JAN-19 03.44.54.626100 AM | 1 | 45 | 17-FEB-19 06.37.31.277529 PM | 16 | 46 | 16-DEC-18 09.48.49.073184 AM | 17 | 47 | 03-FEB-19 10.09.23.422797 AM | 1 | 48 | 26-FEB-19 05.05.37.459393 AM | 19 | 49 | 15-MAR-19 03.50.48.032274 AM | 20 | 50 | 14-MAR-19 02.07.24.615426 AM | 21 |
|---|
Get the date of the last order for each customer and the store where this was made
select customer_id,
max ( order_datetime ),
max ( store_id ) keep (
dense_rank first
order by order_datetime desc
) last_purchase_store_id
from co.orders
group by customer_id| CUSTOMER_ID | MAX(ORDER_DATETIME) | LAST_PURCHASE_STORE_ID | 1 | 16-JAN-19 08.12.31.926232 AM | 1 | 2 | 09-SEP-18 08.32.41.319898 AM | 2 | 3 | 20-JAN-19 11.42.12.885651 PM | 1 | 4 | 13-JAN-19 01.22.50.411160 AM | 1 | 5 | 31-AUG-18 05.08.24.306909 AM | 1 | 6 | 10-JAN-19 06.52.30.324331 PM | 1 | 7 | 25-MAR-19 11.31.58.636926 PM | 7 | 8 | 10-MAR-19 09.54.25.125750 PM | 8 | 9 | 10-DEC-18 12.17.10.374740 AM | 9 | 10 | 23-FEB-19 02.34.28.490023 AM | 10 | 11 | 05-FEB-19 01.18.25.041366 PM | 11 | 12 | 06-FEB-19 02.16.49.481784 AM | 1 | 13 | 17-JAN-19 05.36.16.018250 AM | 13 | 14 | 25-JAN-19 05.33.55.537004 AM | 1 | 15 | 15-JAN-19 03.31.29.057027 PM | 15 | 16 | 10-JAN-19 07.06.48.689623 PM | 16 | 17 | 24-JAN-19 12.09.14.646254 PM | 17 | 18 | 10-FEB-19 01.15.53.995472 AM | 18 | 19 | 23-FEB-19 08.53.32.461195 PM | 19 | 20 | 09-MAR-19 10.33.24.254670 AM | 20 | 21 | 12-MAR-19 11.56.53.384122 PM | 21 | 22 | 11-MAR-19 07.20.17.942291 AM | 22 | 23 | 20-MAR-18 12.07.13.591684 AM | 1 | 24 | 16-AUG-18 05.47.22.306204 PM | 1 | 25 | 18-OCT-18 09.52.33.531823 PM | 1 | 26 | 21-JAN-19 11.28.57.135392 AM | 1 | 27 | 03-FEB-19 05.13.10.955291 AM | 1 | 28 | 01-FEB-19 10.59.58.576087 PM | 1 | 29 | 27-MAR-19 09.29.34.311413 PM | 23 | 30 | 31-JAN-19 08.32.37.958480 PM | 1 | 31 | 20-DEC-18 11.19.17.528223 AM | 2 | 32 | 11-NOV-18 04.36.12.942269 AM | 1 | 33 | 20-DEC-18 06.10.04.750197 PM | 1 | 34 | 26-SEP-18 04.29.39.472854 AM | 1 | 35 | 05-DEC-18 07.20.08.753049 AM | 6 | 36 | 15-JAN-19 10.51.20.451280 PM | 7 | 37 | 25-SEP-18 02.00.27.715942 PM | 8 | 38 | 03-DEC-18 06.47.29.874991 PM | 9 | 39 | 27-DEC-18 03.04.20.388274 PM | 1 | 40 | 16-DEC-18 11.47.49.807137 AM | 1 | 41 | 10-JAN-19 09.11.04.094560 PM | 12 | 42 | 01-DEC-18 07.42.04.777939 PM | 13 | 43 | 23-JAN-19 09.03.54.597996 PM | 1 | 44 | 25-JAN-19 03.44.54.626100 AM | 1 | 45 | 17-FEB-19 06.37.31.277529 PM | 16 | 46 | 16-DEC-18 09.48.49.073184 AM | 17 | 47 | 03-FEB-19 10.09.23.422797 AM | 1 | 48 | 26-FEB-19 05.05.37.459393 AM | 19 | 49 | 15-MAR-19 03.50.48.032274 AM | 20 | 50 | 14-MAR-19 02.07.24.615426 AM | 21 |
|---|
Get the date of the last purchase, location of first purchase, and status of most recent order for each customer
select customer_id,
max ( order_datetime ) last_order_date,
max ( store_id ) keep (
dense_rank first
order by order_datetime
) first_purchase_store_id,
max ( order_status ) keep (
dense_rank last
order by order_id
) last_order_id_status
from co.orders
group by customer_id| CUSTOMER_ID | LAST_ORDER_DATE | FIRST_PURCHASE_STORE_ID | LAST_ORDER_ID_STATUS | 1 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 2 | 09-SEP-18 08.32.41.319898 AM | 1 | COMPLETE | 3 | 20-JAN-19 11.42.12.885651 PM | 1 | COMPLETE | 4 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 5 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 6 | 10-JAN-19 06.52.30.324331 PM | 1 | COMPLETE | 7 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 8 | 10-MAR-19 09.54.25.125750 PM | 1 | COMPLETE | 9 | 10-DEC-18 12.17.10.374740 AM | 1 | COMPLETE | 10 | 23-FEB-19 02.34.28.490023 AM | 1 | COMPLETE | 11 | 05-FEB-19 01.18.25.041366 PM | 1 | COMPLETE | 12 | 06-FEB-19 02.16.49.481784 AM | 1 | COMPLETE | 13 | 17-JAN-19 05.36.16.018250 AM | 1 | COMPLETE | 14 | 25-JAN-19 05.33.55.537004 AM | 1 | COMPLETE | 15 | 15-JAN-19 03.31.29.057027 PM | 1 | COMPLETE | 16 | 10-JAN-19 07.06.48.689623 PM | 1 | COMPLETE | 17 | 24-JAN-19 12.09.14.646254 PM | 1 | COMPLETE | 18 | 10-FEB-19 01.15.53.995472 AM | 1 | COMPLETE | 19 | 23-FEB-19 08.53.32.461195 PM | 1 | COMPLETE | 20 | 09-MAR-19 10.33.24.254670 AM | 1 | COMPLETE | 21 | 12-MAR-19 11.56.53.384122 PM | 1 | COMPLETE | 22 | 11-MAR-19 07.20.17.942291 AM | 1 | COMPLETE | 23 | 20-MAR-18 12.07.13.591684 AM | 1 | COMPLETE | 24 | 16-AUG-18 05.47.22.306204 PM | 1 | COMPLETE | 25 | 18-OCT-18 09.52.33.531823 PM | 1 | COMPLETE | 26 | 21-JAN-19 11.28.57.135392 AM | 1 | COMPLETE | 27 | 03-FEB-19 05.13.10.955291 AM | 1 | COMPLETE | 28 | 01-FEB-19 10.59.58.576087 PM | 1 | COMPLETE | 29 | 27-MAR-19 09.29.34.311413 PM | 1 | COMPLETE | 30 | 31-JAN-19 08.32.37.958480 PM | 1 | COMPLETE | 31 | 20-DEC-18 11.19.17.528223 AM | 1 | COMPLETE | 32 | 11-NOV-18 04.36.12.942269 AM | 3 | COMPLETE | 33 | 20-DEC-18 06.10.04.750197 PM | 1 | COMPLETE | 34 | 26-SEP-18 04.29.39.472854 AM | 5 | COMPLETE | 35 | 05-DEC-18 07.20.08.753049 AM | 1 | COMPLETE | 36 | 15-JAN-19 10.51.20.451280 PM | 1 | COMPLETE | 37 | 25-SEP-18 02.00.27.715942 PM | 1 | COMPLETE | 38 | 03-DEC-18 06.47.29.874991 PM | 1 | COMPLETE | 39 | 27-DEC-18 03.04.20.388274 PM | 1 | COMPLETE | 40 | 16-DEC-18 11.47.49.807137 AM | 1 | COMPLETE | 41 | 10-JAN-19 09.11.04.094560 PM | 1 | COMPLETE | 42 | 01-DEC-18 07.42.04.777939 PM | 1 | COMPLETE | 43 | 23-JAN-19 09.03.54.597996 PM | 1 | COMPLETE | 44 | 25-JAN-19 03.44.54.626100 AM | 1 | COMPLETE | 45 | 17-FEB-19 06.37.31.277529 PM | 1 | COMPLETE | 46 | 16-DEC-18 09.48.49.073184 AM | 1 | COMPLETE | 47 | 03-FEB-19 10.09.23.422797 AM | 1 | COMPLETE | 48 | 26-FEB-19 05.05.37.459393 AM | 1 | COMPLETE | 49 | 15-MAR-19 03.50.48.032274 AM | 1 | COMPLETE | 50 | 14-MAR-19 02.07.24.615426 AM | 1 | COMPLETE |
|---|
Get the date of the last order and a random value for the other columns
select customer_id,
max ( order_datetime ),
any_value ( store_id ),
any_value ( order_status ),
any_value ( order_id )
from co.orders
group by customer_id| CUSTOMER_ID | MAX(ORDER_DATETIME) | ANY_VALUE(STORE_ID) | ANY_VALUE(ORDER_STATUS) | ANY_VALUE(ORDER_ID) | 124 | 12-OCT-18 08.15.39.717974 PM | 1 | COMPLETE | 796 | 107 | 13-MAR-19 04.10.34.107048 PM | 1 | COMPLETE | 804 | 14 | 25-JAN-19 05.33.55.537004 AM | 1 | COMPLETE | 808 | 166 | 25-MAR-19 02.52.16.631428 AM | 1 | COMPLETE | 821 | 51 | 20-MAR-19 07.13.19.500719 PM | 1 | COMPLETE | 848 | 281 | 19-MAR-19 06.19.55.352793 AM | 1 | COMPLETE | 874 | 359 | 10-MAR-19 09.21.02.444428 AM | 1 | COMPLETE | 926 | 318 | 21-MAR-19 03.07.31.851060 PM | 1 | COMPLETE | 938 | 161 | 21-MAR-19 07.50.08.213950 PM | 1 | COMPLETE | 940 | 237 | 07-MAR-19 02.59.15.029147 AM | 5 | COMPLETE | 951 | 328 | 20-DEC-18 12.39.35.118770 PM | 1 | COMPLETE | 956 | 6 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 972 | 173 | 19-OCT-18 04.30.09.442692 PM | 1 | COMPLETE | 974 | 391 | 18-MAR-19 06.50.34.800957 AM | 1 | COMPLETE | 975 | 27 | 03-FEB-19 05.13.10.955291 AM | 1 | COMPLETE | 981 | 135 | 12-MAR-19 05.09.38.031580 AM | 1 | COMPLETE | 50 | 23 | 20-MAR-18 12.07.13.591684 AM | 1 | COMPLETE | 60 | 226 | 02-FEB-19 08.28.14.386011 AM | 1 | COMPLETE | 98 | 348 | 12-APR-19 09.41.06.642641 PM | 1 | COMPLETE | 143 | 58 | 31-MAR-19 06.29.01.038321 PM | 1 | COMPLETE | 216 | 158 | 06-MAR-19 11.17.49.990617 PM | 1 | COMPLETE | 251 | 108 | 04-MAR-19 05.36.39.891421 PM | 1 | COMPLETE | 291 | 277 | 18-MAR-19 01.24.20.184511 AM | 1 | COMPLETE | 312 | 291 | 26-AUG-18 07.25.53.889570 AM | 1 | COMPLETE | 374 | 125 | 17-MAR-19 06.33.56.523112 AM | 1 | COMPLETE | 393 | 371 | 24-MAR-19 05.06.14.312057 PM | 1 | COMPLETE | 397 | 210 | 08-JUL-18 02.36.38.114618 PM | 7 | COMPLETE | 445 | 72 | 06-FEB-19 04.33.54.085409 AM | 1 | COMPLETE | 483 | 64 | 19-DEC-18 08.08.49.968261 PM | 1 | REFUNDED | 489 | 313 | 16-NOV-18 08.44.20.739015 AM | 1 | COMPLETE | 550 | 113 | 13-AUG-18 08.53.08.143929 AM | 1 | COMPLETE | 618 | 57 | 07-JAN-19 03.05.40.406352 PM | 1 | COMPLETE | 718 | 232 | 03-APR-19 01.40.01.840676 PM | 1 | COMPLETE | 1396 | 52 | 08-JAN-19 12.50.52.374027 PM | 1 | COMPLETE | 1438 | 50 | 14-MAR-19 02.07.24.615426 AM | 21 | COMPLETE | 1830 | 290 | 08-APR-19 11.16.55.199255 PM | 23 | COMPLETE | 1941 | 189 | 05-JAN-19 06.57.32.486289 AM | 1 | COMPLETE | 793 | 71 | 16-FEB-19 04.51.59.801488 AM | 1 | COMPLETE | 797 | 136 | 05-APR-19 12.26.49.495590 AM | 1 | COMPLETE | 800 | 344 | 07-MAR-19 06.11.57.595424 PM | 1 | COMPLETE | 801 | 85 | 20-DEC-18 01.53.48.432922 AM | 1 | COMPLETE | 816 | 206 | 31-JAN-19 04.04.00.853750 PM | 3 | COMPLETE | 831 | 88 | 22-SEP-18 07.44.46.434859 PM | 1 | COMPLETE | 835 | 350 | 08-OCT-18 08.13.06.842274 AM | 2 | COMPLETE | 846 | 268 | 10-NOV-18 02.47.04.331650 PM | 1 | COMPLETE | 855 | 180 | 20-FEB-19 04.02.05.809720 PM | 1 | CANCELLED | 856 | 40 | 16-DEC-18 11.47.49.807137 AM | 1 | COMPLETE | 858 | 109 | 30-MAR-19 01.37.15.392243 AM | 1 | COMPLETE | 859 | 7 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 862 | 99 | 30-DEC-18 04.09.22.336203 PM | 1 | COMPLETE | 867 |
|---|
Get the three customers that placed the most orders at each store
with rws as (
select store_id, customer_id, count (*) num_orders,
row_number () over (
partition by store_id
order by count(*) desc
) rn
from co.orders
group by store_id, customer_id
)
select * from rws
where rn <= 3
order by store_id, rn| STORE_ID | CUSTOMER_ID | NUM_ORDERS | RN | 1 | 58 | 8 | 1 | 1 | 115 | 8 | 2 | 1 | 3 | 8 | 3 | 2 | 205 | 3 | 1 | 2 | 31 | 3 | 2 | 2 | 292 | 3 | 3 | 3 | 322 | 3 | 1 | 3 | 380 | 3 | 2 | 3 | 235 | 3 | 3 | 4 | 91 | 3 | 1 | 4 | 207 | 3 | 2 | 4 | 4 | 3 | 3 | 5 | 237 | 3 | 1 | 5 | 353 | 3 | 2 | 5 | 121 | 3 | 3 | 6 | 267 | 3 | 1 | 6 | 35 | 3 | 2 | 6 | 209 | 3 | 3 | 7 | 268 | 3 | 1 | 7 | 326 | 3 | 2 | 7 | 239 | 3 | 3 | 8 | 385 | 3 | 1 | 8 | 66 | 3 | 2 | 8 | 8 | 3 | 3 | 9 | 38 | 3 | 1 | 9 | 270 | 3 | 2 | 9 | 357 | 3 | 3 | 10 | 213 | 3 | 1 | 10 | 300 | 3 | 2 | 10 | 358 | 3 | 3 | 11 | 301 | 3 | 1 | 11 | 388 | 3 | 2 | 11 | 69 | 3 | 3 | 12 | 244 | 3 | 1 | 12 | 215 | 3 | 2 | 12 | 128 | 3 | 3 | 13 | 158 | 3 | 1 | 13 | 187 | 3 | 2 | 13 | 129 | 3 | 3 | 14 | 333 | 3 | 1 | 14 | 159 | 3 | 2 | 14 | 14 | 3 | 3 | 15 | 334 | 3 | 1 | 15 | 131 | 3 | 2 | 15 | 363 | 3 | 3 | 16 | 219 | 3 | 1 | 16 | 16 | 3 | 2 | 16 | 190 | 3 | 3 | 17 | 278 | 3 | 1 | 17 | 162 | 3 | 2 |
|---|
Get the three customers that placed the most orders at each store
with rws as (
select store_id, customer_id, count (*) num_orders,
row_number () over (
partition by store_id
order by count(*) desc, customer_id
) rn
from co.orders
group by store_id, customer_id
)
select * from rws
where rn <= 3
order by store_id, rn| STORE_ID | CUSTOMER_ID | NUM_ORDERS | RN | 1 | 3 | 8 | 1 | 1 | 58 | 8 | 2 | 1 | 115 | 8 | 3 | 2 | 31 | 3 | 1 | 2 | 118 | 3 | 2 | 2 | 205 | 3 | 3 | 3 | 206 | 3 | 1 | 3 | 235 | 3 | 2 | 3 | 264 | 3 | 3 | 4 | 4 | 3 | 1 | 4 | 91 | 3 | 2 | 4 | 207 | 3 | 3 | 5 | 121 | 3 | 1 | 5 | 179 | 3 | 2 | 5 | 237 | 3 | 3 | 6 | 35 | 3 | 1 | 6 | 122 | 3 | 2 | 6 | 209 | 3 | 3 | 7 | 7 | 3 | 1 | 7 | 239 | 3 | 2 | 7 | 268 | 3 | 3 | 8 | 8 | 3 | 1 | 8 | 66 | 3 | 2 | 8 | 240 | 3 | 3 | 9 | 38 | 3 | 1 | 9 | 183 | 3 | 2 | 9 | 270 | 3 | 3 | 10 | 213 | 3 | 1 | 10 | 300 | 3 | 2 | 10 | 358 | 3 | 3 | 11 | 69 | 3 | 1 | 11 | 156 | 3 | 2 | 11 | 301 | 3 | 3 | 12 | 128 | 3 | 1 | 12 | 215 | 3 | 2 | 12 | 244 | 3 | 3 | 13 | 129 | 3 | 1 | 13 | 158 | 3 | 2 | 13 | 187 | 3 | 3 | 14 | 14 | 3 | 1 | 14 | 159 | 3 | 2 | 14 | 188 | 3 | 3 | 15 | 102 | 3 | 1 | 15 | 131 | 3 | 2 | 15 | 160 | 3 | 3 | 16 | 16 | 3 | 1 | 16 | 190 | 3 | 2 | 16 | 219 | 3 | 3 | 17 | 162 | 3 | 1 | 17 | 278 | 3 | 2 |
|---|
Get the customers that placed the most orders at each store
with rws as (
select store_id, customer_id, count (*) num_orders,
rank () over (
partition by store_id
order by count(*) desc
) rn
from co.orders
group by store_id, customer_id
)
select * from rws
where rn <= 3
order by store_id, rn, customer_id| STORE_ID | CUSTOMER_ID | NUM_ORDERS | RN | 1 | 3 | 8 | 1 | 1 | 58 | 8 | 1 | 1 | 115 | 8 | 1 | 1 | 166 | 8 | 1 | 2 | 31 | 3 | 1 | 2 | 118 | 3 | 1 | 2 | 205 | 3 | 1 | 2 | 292 | 3 | 1 | 2 | 321 | 3 | 1 | 3 | 206 | 3 | 1 | 3 | 235 | 3 | 1 | 3 | 264 | 3 | 1 | 3 | 322 | 3 | 1 | 3 | 380 | 3 | 1 | 4 | 4 | 3 | 1 | 4 | 91 | 3 | 1 | 4 | 207 | 3 | 1 | 4 | 265 | 3 | 1 | 4 | 294 | 3 | 1 | 4 | 381 | 3 | 1 | 5 | 121 | 3 | 1 | 5 | 179 | 3 | 1 | 5 | 237 | 3 | 1 | 5 | 353 | 3 | 1 | 5 | 382 | 3 | 1 | 6 | 35 | 3 | 1 | 6 | 122 | 3 | 1 | 6 | 209 | 3 | 1 | 6 | 238 | 3 | 1 | 6 | 267 | 3 | 1 | 7 | 7 | 3 | 1 | 7 | 239 | 3 | 1 | 7 | 268 | 3 | 1 | 7 | 326 | 3 | 1 | 8 | 8 | 3 | 1 | 8 | 66 | 3 | 1 | 8 | 240 | 3 | 1 | 8 | 385 | 3 | 1 | 9 | 38 | 3 | 1 | 9 | 183 | 3 | 1 | 9 | 270 | 3 | 1 | 9 | 357 | 3 | 1 | 10 | 213 | 3 | 1 | 10 | 300 | 3 | 1 | 10 | 358 | 3 | 1 | 11 | 69 | 3 | 1 | 11 | 156 | 3 | 1 | 11 | 301 | 3 | 1 | 11 | 388 | 3 | 1 | 12 | 128 | 3 | 1 |
|---|
Get the three highest of orders per store all the customers who made these number of purchases
with rws as (
select store_id, customer_id, count (*) num_orders,
dense_rank () over (
partition by store_id
order by count(*) desc
) rn
from co.orders
group by store_id, customer_id
)
select * from rws
where rn <= 3
order by store_id, rn, customer_id| STORE_ID | CUSTOMER_ID | NUM_ORDERS | RN | 1 | 3 | 8 | 1 | 1 | 58 | 8 | 1 | 1 | 115 | 8 | 1 | 1 | 166 | 8 | 1 | 1 | 41 | 7 | 2 | 1 | 49 | 7 | 2 | 1 | 62 | 7 | 2 | 1 | 65 | 7 | 2 | 1 | 135 | 7 | 2 | 1 | 169 | 7 | 2 | 1 | 172 | 7 | 2 | 1 | 234 | 7 | 2 | 1 | 257 | 7 | 2 | 1 | 266 | 7 | 2 | 1 | 355 | 7 | 2 | 1 | 364 | 7 | 2 | 1 | 385 | 7 | 2 | 1 | 14 | 6 | 3 | 1 | 27 | 6 | 3 | 1 | 30 | 6 | 3 | 1 | 45 | 6 | 3 | 1 | 77 | 6 | 3 | 1 | 100 | 6 | 3 | 1 | 157 | 6 | 3 | 1 | 161 | 6 | 3 | 1 | 182 | 6 | 3 | 1 | 203 | 6 | 3 | 1 | 219 | 6 | 3 | 1 | 229 | 6 | 3 | 1 | 238 | 6 | 3 | 1 | 259 | 6 | 3 | 1 | 261 | 6 | 3 | 1 | 271 | 6 | 3 | 1 | 287 | 6 | 3 | 1 | 300 | 6 | 3 | 1 | 301 | 6 | 3 | 1 | 314 | 6 | 3 | 1 | 317 | 6 | 3 | 1 | 327 | 6 | 3 | 1 | 357 | 6 | 3 | 1 | 359 | 6 | 3 | 1 | 361 | 6 | 3 | 1 | 386 | 6 | 3 | 2 | 31 | 3 | 1 | 2 | 118 | 3 | 1 | 2 | 205 | 3 | 1 | 2 | 292 | 3 | 1 | 2 | 321 | 3 | 1 | 2 | 60 | 2 | 2 | 2 | 89 | 2 | 2 |
|---|
A SQL Macro to get N rows from a table
create or replace function top_n (
tab dbms_tf.table_t,
num_rows integer
)
return varchar2 sql_macro as
begin
return
'select * from tab
fetch first num_rows rows only';
end top_n; Function created.
Calling the top N SQL macro
select * from top_n (
co.orders, 3
)| ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | 792 | 13-SEP-18 04.00.07.171604 AM | 271 | COMPLETE | 1 | 793 | 13-SEP-18 01.35.16.589378 PM | 189 | COMPLETE | 1 | 794 | 13-SEP-18 02.43.07.711020 PM | 326 | COMPLETE | 7 |
|---|
Get the SQL generated by the macro
declare
l_clob clob;
begin
dbms_utility.expand_sql_text (
input_sql_text => q'!select * from top_n (
co.orders, :num_rows
)!',
output_sql_text => l_clob );
dbms_output.put_line(l_clob);
end; Statement processed.
SELECT "A1"."ORDER_ID" "ORDER_ID","A1"."ORDER_DATETIME" "ORDER_DATETIME","A1"."CUSTOMER_ID" "CUSTOMER_ID","A1"."ORDER_STATUS" "ORDER_STATUS","A1"."STORE_ID" "STORE_ID" FROM (SELECT "A3"."ORDER_ID" "ORDER_ID","A3"."ORDER_DATETIME" "ORDER_DATETIME","A3"."CUSTOMER_ID" "CUSTOMER_ID","A3"."ORDER_STATUS" "ORDER_STATUS","A3"."STORE_ID" "STORE_ID" FROM (SELECT "A4"."ORDER_ID" "ORDER_ID","A4"."ORDER_DATETIME" "ORDER_DATETIME","A4"."CUSTOMER_ID" "CUSTOMER_ID","A4"."ORDER_STATUS" "ORDER_STATUS","A4"."STORE_ID" "STORE_ID" FROM (SELECT "A5"."ORDER_ID" "ORDER_ID","A5"."ORDER_DATETIME" "ORDER_DATETIME","A5"."CUSTOMER_ID" "CUSTOMER_ID","A5"."ORDER_STATUS" "ORDER_STATUS","A5"."STORE_ID" "STORE_ID",ROW_NUMBER() OVER ( ORDER BY NULL) "rowlimit_$$_rownumber" FROM (SELECT "A2"."ORDER_ID" "ORDER_ID","A2"."ORDER_DATETIME" "ORDER_DATETIME","A2"."CUSTOMER_ID" "CUSTOMER_ID","A2"."ORDER_STATUS" "ORDER_STATUS","A2"."STORE_ID" "STORE_ID" FROM "CO"."ORDERS" "A2") "A5") "A4" WHERE "A4"."rowlimit_$$_rownumber"<=:B1) "A3") "A1"
A top-N per group SQL macro
create or replace function top_n_per_group (
tab dbms_tf.table_t,
group_cols dbms_tf.columns_t,
order_cols dbms_tf.columns_t,
num_rows integer,
desc_cols dbms_tf.columns_t
default null
)
return clob sql_macro as
grouping_cols clob;
ordering_cols clob;
stmt clob;
begin
for col in 1 .. group_cols.count loop
grouping_cols := grouping_cols || group_cols ( col ) || ',';
end loop;
for col in 1 .. order_cols.count loop
if order_cols ( col ) member of desc_cols then
ordering_cols := ordering_cols || order_cols ( col ) || 'desc,' ;
else
ordering_cols := ordering_cols || order_cols ( col ) || ',' ;
end if;
end loop;
stmt := 'with rws as (
select t.*, row_number () over (
partition by ' || rtrim ( grouping_cols, ',' ) || '
order by ' || rtrim ( ordering_cols, ',' ) || '
) rn
from tab t
)
select * from rws
where rn <= num_rows';
return stmt;
end top_n_per_group; Function created.
Get the last three orders for each customer using a SQL macro
select * from top_n_per_group (
co.orders,
columns ( customer_id ),
columns ( store_id, order_datetime ),
3,
columns ( order_datetime )
)| ORDER_ID | ORDER_DATETIME | CUSTOMER_ID | ORDER_STATUS | STORE_ID | RN | 1491 | 16-JAN-19 08.12.31.926232 AM | 1 | COMPLETE | 1 | 1 | 1390 | 31-DEC-18 03.47.26.170374 PM | 1 | COMPLETE | 1 | 2 | 201 | 29-APR-18 11.48.01.437203 AM | 1 | COMPLETE | 1 | 3 | 5 | 11-FEB-18 06.01.30.906119 PM | 2 | COMPLETE | 1 | 1 | 765 | 09-SEP-18 08.32.41.319898 AM | 2 | COMPLETE | 2 | 2 | 1520 | 20-JAN-19 11.42.12.885651 PM | 3 | COMPLETE | 1 | 1 | 766 | 09-SEP-18 11.33.57.269603 AM | 3 | COMPLETE | 1 | 2 | 608 | 11-AUG-18 06.57.30.697703 PM | 3 | COMPLETE | 1 | 3 | 1468 | 13-JAN-19 01.22.50.411160 AM | 4 | COMPLETE | 1 | 1 | 1455 | 11-JAN-19 02.35.59.390244 PM | 4 | COMPLETE | 1 | 2 | 585 | 05-AUG-18 09.58.27.792726 PM | 4 | COMPLETE | 1 | 3 | 707 | 31-AUG-18 05.08.24.306909 AM | 5 | COMPLETE | 1 | 1 | 331 | 03-JUN-18 01.25.06.729734 PM | 5 | COMPLETE | 1 | 2 | 306 | 27-MAY-18 03.20.25.802951 AM | 5 | COMPLETE | 5 | 3 | 1450 | 10-JAN-19 06.52.30.324331 PM | 6 | COMPLETE | 1 | 1 | 1111 | 12-NOV-18 11.38.06.043178 PM | 6 | COMPLETE | 1 | 2 | 769 | 09-SEP-18 01.45.17.970634 PM | 6 | REFUNDED | 1 | 3 | 1121 | 15-NOV-18 10.21.03.765653 AM | 7 | COMPLETE | 1 | 1 | 950 | 15-OCT-18 05.58.32.401980 PM | 7 | COMPLETE | 1 | 2 | 1890 | 25-MAR-19 11.31.58.636926 PM | 7 | COMPLETE | 7 | 3 | 1543 | 23-JAN-19 11.49.58.042142 AM | 8 | COMPLETE | 1 | 1 | 993 | 23-OCT-18 09.12.34.064134 PM | 8 | COMPLETE | 1 | 2 | 430 | 26-JUN-18 07.51.51.514668 PM | 8 | COMPLETE | 1 | 3 | 968 | 18-OCT-18 11.26.07.286451 AM | 9 | COMPLETE | 1 | 1 | 928 | 11-OCT-18 04.03.45.848273 AM | 9 | COMPLETE | 1 | 2 | 7 | 22-FEB-18 12.57.11.011114 AM | 9 | COMPLETE | 1 | 3 | 1525 | 21-JAN-19 01.17.54.579876 PM | 10 | COMPLETE | 1 | 1 | 943 | 14-OCT-18 03.37.42.139148 AM | 10 | COMPLETE | 1 | 2 | 437 | 28-JUN-18 05.37.34.394938 AM | 10 | COMPLETE | 1 | 3 | 1321 | 19-DEC-18 05.28.34.329405 PM | 11 | COMPLETE | 1 | 1 | 960 | 17-OCT-18 08.14.25.241276 AM | 11 | COMPLETE | 1 | 2 | 761 | 08-SEP-18 07.31.00.472757 PM | 11 | COMPLETE | 1 | 3 | 1637 | 06-FEB-19 02.16.49.481784 AM | 12 | COMPLETE | 1 | 1 | 125 | 10-APR-18 12.41.39.268531 PM | 12 | COMPLETE | 1 | 2 | 839 | 24-SEP-18 12.55.09.647618 AM | 12 | COMPLETE | 12 | 3 | 1052 | 02-NOV-18 04.10.21.166613 AM | 13 | COMPLETE | 1 | 1 | 82 | 30-MAR-18 12.19.54.871540 AM | 13 | COMPLETE | 1 | 2 | 1498 | 17-JAN-19 05.36.16.018250 AM | 13 | COMPLETE | 13 | 3 | 1560 | 25-JAN-19 05.33.55.537004 AM | 14 | COMPLETE | 1 | 1 | 1191 | 30-NOV-18 02.08.39.882263 AM | 14 | COMPLETE | 1 | 2 | 1169 | 24-NOV-18 03.11.55.107758 PM | 14 | COMPLETE | 1 | 3 | 1195 | 30-NOV-18 05.42.24.627921 PM | 15 | COMPLETE | 1 | 1 | 513 | 19-JUL-18 06.56.11.226657 PM | 15 | COMPLETE | 1 | 2 | 290 | 23-MAY-18 01.08.48.447445 PM | 15 | COMPLETE | 1 | 3 | 1448 | 10-JAN-19 06.05.40.626338 PM | 16 | COMPLETE | 1 | 1 | 1439 | 08-JAN-19 03.48.25.232374 PM | 16 | COMPLETE | 1 | 2 | 687 | 27-AUG-18 11.04.59.797479 AM | 16 | COMPLETE | 1 | 3 | 1200 | 30-NOV-18 11.19.54.798149 PM | 17 | COMPLETE | 1 | 1 | 899 | 06-OCT-18 08.37.49.467272 PM | 17 | COMPLETE | 1 | 2 | 132 | 12-APR-18 01.14.57.734241 AM | 17 | COMPLETE | 1 | 3 |
|---|
Get the three customers that placed the most orders at each store
with customer_orders_per_store as (
select store_id, customer_id, count(*) num_orders
from co.orders
group by store_id, customer_id
)
select * from top_n_per_group (
customer_orders_per_store,
columns ( store_id ),
columns ( num_orders ),
3,
columns ( num_orders )
)| STORE_ID | CUSTOMER_ID | NUM_ORDERS | RN | 1 | 58 | 8 | 1 | 1 | 115 | 8 | 2 | 1 | 3 | 8 | 3 | 2 | 205 | 3 | 1 | 2 | 31 | 3 | 2 | 2 | 292 | 3 | 3 | 3 | 322 | 3 | 1 | 3 | 380 | 3 | 2 | 3 | 235 | 3 | 3 | 4 | 91 | 3 | 1 | 4 | 207 | 3 | 2 | 4 | 4 | 3 | 3 | 5 | 237 | 3 | 1 | 5 | 353 | 3 | 2 | 5 | 121 | 3 | 3 | 6 | 267 | 3 | 1 | 6 | 35 | 3 | 2 | 6 | 209 | 3 | 3 | 7 | 268 | 3 | 1 | 7 | 326 | 3 | 2 | 7 | 239 | 3 | 3 | 8 | 385 | 3 | 1 | 8 | 66 | 3 | 2 | 8 | 8 | 3 | 3 | 9 | 38 | 3 | 1 | 9 | 270 | 3 | 2 | 9 | 357 | 3 | 3 | 10 | 213 | 3 | 1 | 10 | 300 | 3 | 2 | 10 | 358 | 3 | 3 | 11 | 301 | 3 | 1 | 11 | 388 | 3 | 2 | 11 | 69 | 3 | 3 | 12 | 244 | 3 | 1 | 12 | 215 | 3 | 2 | 12 | 128 | 3 | 3 | 13 | 158 | 3 | 1 | 13 | 187 | 3 | 2 | 13 | 129 | 3 | 3 | 14 | 333 | 3 | 1 | 14 | 159 | 3 | 2 | 14 | 14 | 3 | 3 | 15 | 334 | 3 | 1 | 15 | 131 | 3 | 2 | 15 | 363 | 3 | 3 | 16 | 219 | 3 | 1 | 16 | 16 | 3 | 2 | 16 | 190 | 3 | 3 | 17 | 278 | 3 | 1 | 17 | 162 | 3 | 2 |
|---|
Get the three most expensive line-items per customer
with paid_per_item as (
select customer_id,
unit_price * quantity total_paid
from co.order_items oi
join co.orders o
on o.order_id = oi.order_id
)
select * from top_n_per_group (
paid_per_item,
columns ( customer_id ),
columns ( total_paid ),
3,
columns ( total_paid )
)| CUSTOMER_ID | TOTAL_PAID | RN | 1 | 98.24 | 1 | 1 | 57.36 | 2 | 1 | 56.42 | 3 | 2 | 136.24 | 1 | 2 | 116.6 | 2 | 2 | 63.2 | 3 | 3 | 159.64 | 1 | 3 | 148 | 2 | 3 | 147.55 | 3 | 4 | 176.68 | 1 | 4 | 156.64 | 2 | 4 | 126.72 | 3 | 5 | 73.38 | 1 | 5 | 46.64 | 2 | 5 | 30.72 | 3 | 6 | 148 | 1 | 6 | 132.51 | 2 | 6 | 119.73 | 3 | 7 | 159.64 | 1 | 7 | 148 | 2 | 7 | 119.34 | 3 | 8 | 159.56 | 1 | 8 | 146.25 | 2 | 8 | 122.76 | 3 | 9 | 196.48 | 1 | 9 | 174.84 | 2 | 9 | 112.02 | 3 | 10 | 159.56 | 1 | 10 | 141.05 | 2 | 10 | 130.7 | 3 | 11 | 176.68 | 1 | 11 | 117.48 | 2 | 11 | 111 | 3 | 12 | 95.8 | 1 | 12 | 79.56 | 2 | 12 | 68.94 | 3 | 13 | 196.48 | 1 | 13 | 174.84 | 2 | 13 | 157.28 | 3 | 14 | 198.9 | 1 | 14 | 195.8 | 2 | 14 | 153.45 | 3 | 15 | 199.55 | 1 | 15 | 191.4 | 2 | 15 | 153.45 | 3 | 16 | 195 | 1 | 16 | 159.56 | 2 | 16 | 156.64 | 3 | 17 | 122.76 | 1 | 17 | 119.73 | 2 |
|---|