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 Go to next message
Unclefool
Messages: 65
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68352
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3205
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

Re: greatest total amount of purchases per day for each customer_id [message #686833 is a reply to message #686816] Tue, 17 January 2023 02:11 Go to previous message
Michel Cadot
Messages: 68352
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A feedback would be appreciated.

Previous Topic: SQL very slow
Next Topic: Two Column Join
Goto Forum:
  


Current Time: Thu Feb 09 04:12:42 CST 2023