0001 WITH ssci AS (
0002 SELECT
0003 ss_customer_sk customer_sk,
0004 ss_item_sk item_sk
0005 FROM store_sales, date_dim
0006 WHERE ss_sold_date_sk = d_date_sk
0007 AND d_month_seq BETWEEN 1200 AND 1200 + 11
0008 GROUP BY ss_customer_sk, ss_item_sk),
0009 csci AS (
0010 SELECT
0011 cs_bill_customer_sk customer_sk,
0012 cs_item_sk item_sk
0013 FROM catalog_sales, date_dim
0014 WHERE cs_sold_date_sk = d_date_sk
0015 AND d_month_seq BETWEEN 1200 AND 1200 + 11
0016 GROUP BY cs_bill_customer_sk, cs_item_sk)
0017 SELECT
0018 sum(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NULL
0019 THEN 1
0020 ELSE 0 END) store_only,
0021 sum(CASE WHEN ssci.customer_sk IS NULL AND csci.customer_sk IS NOT NULL
0022 THEN 1
0023 ELSE 0 END) catalog_only,
0024 sum(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NOT NULL
0025 THEN 1
0026 ELSE 0 END) store_and_catalog
0027 FROM ssci
0028 FULL OUTER JOIN csci ON (ssci.customer_sk = csci.customer_sk
0029 AND ssci.item_sk = csci.item_sk)
0030 LIMIT 100
