SELECT
NVL(SUM(mem_count),0) AS repeat_shoppers,
CASE WHEN SUM(revenue_d) > 0 AND SUM(mem_count) > 0 THEN SUM(revenue_d)/SUM(mem_count) ELSE 0 END avg_spend_repeat_d
FROM
(SELECT
COUNT( DISTINCT mbr.membership_number) AS mem_count ,
SUM(demand_revenue) AS revenue_d
FROM dw_summary.ddh_product_detail_new pd,
( ( SELECT membership_number FROM(
SELECT membership_number, COUNT(DISTINCT order_number) AS order_count
FROM dw_summary.ddh_product_detail_new
WHERE report_date BETWEEN '2023-02-02' AND '2020-09-19' --dynamic
AND digital_channel = 'OPIC'
AND membership_number IS NOT NULL
GROUP BY membership_number
HAVING COUNT(DISTINCT order_number) > 1)
MINUS
SELECT DISTINCT membership_number
FROM dw_summary.ddh_product_detail_new
WHERE report_date < '2020-02-02' AND report_date >= '2020-02-02'-180 --dynamic
AND digital_channel = 'OPIC'
AND membership_number IS NOT NULL
)
UNION
( SELECT DISTINCT membership_number
FROM dw_summary.ddh_product_detail_new
WHERE report_date BETWEEN '2020-02-02' AND '2020-09-19' --dynamic
AND digital_channel = 'OPIC'
AND membership_number IS NOT NULL
INTERSECT
SELECT DISTINCT membership_number
FROM dw_summary.ddh_product_detail_new
WHERE report_date < '2020-02-02' AND report_date >= '2020-02-02'-180 --dynamic
AND digital_channel = 'OPIC'
AND membership_number IS NOT NULL
)
) mbr
WHERE pd.membership_number = mbr.membership_number
AND pd.digital_channel = 'OPIC'
AND report_date BETWEEN '2020-02-02' AND '2020-09-19' --dynamic
);
