我需要为用户确定不同订单的有效订阅。
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;
在此数据中,我们有一个无效订单order_id_004,因为后续订单order_id_005涵盖了整个月的订阅。我需要标记此记录以用于审计目的。
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
这是正在进行的工作,产生无效结果:
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;
提前感谢您的帮助。
尝试这样的事情: