blob: e7e0b1a05259da58ee6fedbbf971bcfc0f67384c (
plain) (
blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
WITH ssci AS (
SELECT
ss_customer_sk customer_sk,
ss_item_sk item_sk
FROM store_sales, date_dim
WHERE ss_sold_date_sk = d_date_sk
AND d_month_seq BETWEEN 1200 AND 1200 + 11
GROUP BY ss_customer_sk, ss_item_sk),
csci AS (
SELECT
cs_bill_customer_sk customer_sk,
cs_item_sk item_sk
FROM catalog_sales, date_dim
WHERE cs_sold_date_sk = d_date_sk
AND d_month_seq BETWEEN 1200 AND 1200 + 11
GROUP BY cs_bill_customer_sk, cs_item_sk)
SELECT
sum(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NULL
THEN 1
ELSE 0 END) store_only,
sum(CASE WHEN ssci.customer_sk IS NULL AND csci.customer_sk IS NOT NULL
THEN 1
ELSE 0 END) catalog_only,
sum(CASE WHEN ssci.customer_sk IS NOT NULL AND csci.customer_sk IS NOT NULL
THEN 1
ELSE 0 END) store_and_catalog
FROM ssci
FULL OUTER JOIN csci ON (ssci.customer_sk = csci.customer_sk
AND ssci.item_sk = csci.item_sk)
LIMIT 100
|
