Find Duplicate Subscription for overlapping period

May 6, 2024

I need to identify valid subscription for users for different order placed.

CREATE OR REPLACE TEMP TABLE customer_orders(
    customer_id VARCHAR
  , customer_order_id VARCHAR
  , product_id VARCHAR
  , subscription_start_date DATE
  , subscription_end_date DATE
);
INSERT INTO customer_orders
VALUES
  ('customer_id_001', 'order_id_001', 'product_id_001', '2024-01-01', '2024-01-31'),
  ('customer_id_001', 'order_id_002', 'product_id_001', '2024-02-01', '2024-02-29'),
  ('customer_id_001', 'order_id_003', 'product_id_001', '2024-03-01', '2024-03-31'),
  ('customer_id_001', 'order_id_004', 'product_id_001', '2024-04-01', '2024-04-15'),
  ('customer_id_001', 'order_id_005', 'product_id_001', '2024-04-01', '2024-04-30');
SELECT * FROM customer_orders ORDER BY 1,2,3,4,5;

In this data, we have an invalid order order_id_004 as there is a subsequent order order_id_005, which covers the subscription of whole month. I need to flag this record for audit purpose.

CUSTOMER_ID CUSTOMER_ORDER_ID   PRODUCT_ID  SUBSCRIPTION_START_DATE SUBSCRIPTION_END_DATE COMMENTS 
customer_id_001 order_id_001    product_id_001  2024-01-01  2024-01-31 VALID ORDER
customer_id_001 order_id_002    product_id_001  2024-02-01  2024-02-29 VALID ORDER
customer_id_001 order_id_003    product_id_001  2024-03-01  2024-03-31 VALID ORDER
customer_id_001 order_id_004    product_id_001  2024-04-01  2024-04-15 INVALID ORDER
customer_id_001 order_id_005    product_id_001  2024-04-01  2024-04-30 VALID ORDER

Here is work in progress, producing invalid results:

SELECT   
    c1.customer_id 
  , c1.customer_order_id 
  , c1.product_id 
  , c1.subscription_start_date 
  , c1.subscription_end_date  
  , CASE WHEN c1.subscription_start_date BETWEEN c2.subscription_start_date AND c2.subscription_end_date
           OR c1.subscription_end_date   BETWEEN c2.subscription_start_date AND c2.subscription_end_date
           OR c2.subscription_start_date BETWEEN c1.subscription_start_date AND c1.subscription_end_date
         THEN 'Duplicate Subscription'
         ELSE 'Valid Subscription'
    END AS comment     
FROM customer_orders c1
INNER JOIN customer_orders c2 
   ON c1.customer_id = c2.customer_id
  AND c1.product_id  = c2.product_id
GROUP BY ALL  
ORDER BY 1,2,3,4,5;

Thanks for your help in advance.

>Solution :

Try something like this:

SELECT   
    c1.customer_id 
  , c1.customer_order_id 
  , c1.product_id 
  , c1.subscription_start_date 
  , c1.subscription_end_date  
  , IFF(c2.customer_id IS NOT NULL, 'Duplicate Subscription', 'Valid Subscription') AS comment
  , IFF(DATEDIFF(DAY, c2.subscription_start_date, c2.subscription_end_date) >= DATEDIFF(DAY, c1.subscription_start_date, c1.subscription_end_date), 'INVALID ORDER', 'VALID ORDER') AS Status
FROM customer_orders c1
LEFT JOIN customer_orders c2 
   ON c1.customer_id = c2.customer_id
  AND c1.product_id  = c2.product_id
  AND c1.customer_order_id <> c2.customer_order_id
  AND c1.subscription_start_date <= c2.subscription_end_date
  AND c2.subscription_start_date <= c1.subscription_end_date  
ORDER BY 1,2,3,4,5;