Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Customers with purchases on consecutive days

BeefStuMay 1 2023 — edited May 1 2023

I was hoping someone could help me out. I need to find customers who have 10 or more purchases on consecutive days. Based on my sample data below only 1 row should satisfy this condition and that's for customer_id = 3 from 2023-03-12 to 2023-04-03

Below is my attempt, which I know isn't correct. Thanks in advance to all who respond.

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'John', 'Henry' FROM DUAL UNION ALL
SELECT 3, 'Lisa', 'Saladino' FROM DUAL;



CREATE TABLE purchases(
    ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    CUSTOMER_ID NUMBER, 
   PURCHASE_DATE TIMESTAMP
);



INSERT  INTO purchases
(CUSTOMER_ID, PURCHASE_DATE) 
SELECT 1, TIMESTAMP '2023-04-03 11:41:25' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM  dual CONNECT BY  LEVEL <= 6 UNION ALL
SELECT 2, TIMESTAMP '2023-04-03 01:41:25' + NUMTODSINTERVAL ( LEVEL * 1, 'DAY') FROM  dual CONNECT BY  LEVEL <= 7 UNION ALL
SELECT 3, TIMESTAMP '2023-04-23 21:31:25' + NUMTODSINTERVAL ( LEVEL * 1, 'DAY') FROM  dual CONNECT BY  LEVEL <= 7 UNION ALL
SELECT 3, TIMESTAMP '2023-03-12 21:44:35' + NUMTODSINTERVAL ( LEVEL * 1, 'DAY') FROM    dual
CONNECT BY  LEVEL <=20;



select purchases.*
from (select purchases.*, count(*) over (partition by customer_id) as cnt
      from (select purchases.*,
(row_number() over (partition by customer_id order by purchase_date) -
row_number() over (partition by customer_id order by purchase_date)
) as grp
from  purchases
) purchases
) purchases
where cnt >= 10;
This post has been answered by cormaco on May 1 2023
Jump to Answer
Comments
Post Details
Added on May 1 2023
13 comments
1,317 views