Home » SQL & PL/SQL » SQL & PL/SQL » greatest total amount of purchases per day for each customer_id
greatest total amount of purchases per day for each customer_id [message #686816] |
Sat, 14 January 2023 13:10 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
I'm trying to find the greatest total amount of purchases per day for each customer_id. If there is a tie I would like to use the latest date.
Below is my attempt but I can't seem to get it working. If there is a better way then rank() that would be fine too.
Any help would be greatly appreciated.
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD-MON-YYYY HH24:MI:SS.FF';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
CREATE TABLE customers
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Jerry', 'Torchiano' FROM DUAL;
CREATE TABLE items
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;
CREATE TABLE purchases
(CUSTOMER_ID, PRODUCT_ID, QUANTITY, PURCHASE_DATE) AS
SELECT 1, 101, 3, TIMESTAMP'2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 1, 100, 1, TIMESTAMP '2022-10-12 19:04:18' FROM DUAL UNION ALL
SELECT 2, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 2, 101, 3, TIMESTAMP '2022-10-17 19:34:58' FROM DUAL UNION ALL
SELECT 3, 101,1, TIMESTAMP '2022-10-11 09:54:48' FROM DUAL UNION ALL
SELECT 3, 102,1, TIMESTAMP '2022-10-17 19:04:18' FROM DUAL UNION ALL
SELECT 3, 102, 4,TIMESTAMP '2022-10-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM dual
CONNECT BY LEVEL <= 5;
with cte as
(select
p.CUSTOMER_ID,
p.PURCHASE_DATE,
rank() over (partition by c.customer_id order by MAX(p.quantity * i.price) desc) rnk
from purchases p,
items i
)
SELECT c.customer_id,
first_name,
last_name,
purchase_date,
tot
from cte
JOIN customers c ON c.customer_id = p.customer_id
where rnk = 1;
|
|
|
Re: greatest total amount of purchases per day for each customer_id [message #686817 is a reply to message #686816] |
Sat, 14 January 2023 14:06 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
There are several errors in your code:
- "c.customer_id" in cte does not exist
- "order by MAX(p.quantity * i.price)" is not correct: the purpose of RANK ... ORDER BY ... DESC is then to find the row with the MAX value
- there are no join conditions between purchases and items in cte
- "tot" in main query is not defined
- "p.customer_id" in main query does not exist
So (if I correctly understand what you want):
SQL> select * from purchases order by CUSTOMER_ID, PURCHASE_DATE, PRODUCT_ID;
CUSTOMER_ID PRODUCT_ID QUANTITY PURCHASE_DATE
----------- ---------- ---------- -----------------------
1 101 3 11/10/2022 09:54:48.000
1 100 1 12/10/2022 19:04:18.000
2 101 1 11/10/2022 09:54:48.000
2 101 3 17/10/2022 19:34:58.000
3 101 1 11/10/2022 09:54:48.000
3 102 4 14/10/2022 21:44:35.000
3 102 4 16/10/2022 21:44:35.000
3 102 1 17/10/2022 19:04:18.000
3 102 4 18/10/2022 21:44:35.000
3 102 4 20/10/2022 21:44:35.000
3 102 4 22/10/2022 21:44:35.000
11 rows selected.
SQL> with cte as
2 (select
3 p.CUSTOMER_ID,
4 trunc(p.PURCHASE_DATE) PURCHASE_DATE,
5 rank() over
6 (partition by p.customer_id
7 order by sum(p.quantity * i.price) desc, trunc(p.PURCHASE_DATE) desc)
8 rnk,
9 sum(p.quantity * i.price) tot
10 from purchases p, items i
11 where i.product_id = p.product_id
12 group by p.CUSTOMER_ID, trunc(p.PURCHASE_DATE)
13 )
14 SELECT c.customer_id,
15 c.first_name,
16 c.last_name,
17 cte.purchase_date,
18 cte.tot
19 from cte JOIN customers c ON c.customer_id = cte.customer_id
20 where rnk = 1;
CUSTOMER_ID FIRST LAST_NAME PURCHASE_DATE TOT
----------- ----- --------- ------------------- ----------
1 Faith Mazzarone 11/10/2022 00:00:00 335.97
2 Lisa Saladino 17/10/2022 00:00:00 335.97
3 Jerry Torchiano 11/10/2022 00:00:00 111.99
3 rows selected.
[Updated on: Sun, 15 January 2023 02:20] Report message to a moderator
|
|
|
Re: greatest total amount of purchases per day for each customer_id [message #686820 is a reply to message #686817] |
Mon, 16 January 2023 08:14 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Just for fun, match_recognize solution:
with cte as (
select p.customer_id,
trunc(p.purchase_date) purchase_date,
sum(p.quantity * i.price) tot
from purchases p,
items i
where i.product_id = p.product_id
group by p.customer_id,
trunc(p.purchase_date)
)
select c.customer_id,
c.first_name,
c.last_name,
m.purchase_date,
m.tot
from cte
match_recognize(
partition by customer_id
order by tot,
purchase_date
all rows per match
pattern(top $)
define top as next(tot) is null
) m,
customers c
where c.customer_id = m.customer_id
/
CUSTOMER_ID FIRST_NAME LAST_NAME PURCHASE_DATE TOT
----------- ---------- --------- ------------- ----------
1 Faith Mazzarone 11-OCT-22 335.97
2 Lisa Saladino 17-OCT-22 335.97
3 Jerry Torchiano 11-OCT-22 111.99
SQL>
SY.
[Updated on: Mon, 16 January 2023 08:28] Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:53:48 CDT 2024
|