CREATE TABLE Table1
(`year` int, `customer_key` varchar(1), `purchase_channel` varchar(6), `spend` int, `transactions` int)
;
INSERT INTO Table1
(`year`, `customer_key`, `purchase_channel`, `spend`, `transactions`)
VALUES
(2020, 'a', 'online', 5, 1),
(2020, 'a', 'store', 5, 1),
(2020, 'b', 'online', 10, 1),
(2020, 'c', 'store', 10, 1),
(2021, 'a', 'online', 5, 1),
(2021, 'a', 'store', 5, 1)
;
SELECT * FROM Table1
;
CREATE TABLE Table2
(channel_behavior_2020 varchar(20),2020_cohort_size varchar(20),of_which_shopped_2021 varchar(20))
;
INSERT INTO Table2 (channel_behavior_2020)
VALUES
('multi_channel'),
('online_only'),
('store_only')
;
SELECT * FROM Table2
;
SELECT customer_key FROM (
SELECT DISTINCT customer_key, purchase_channel FROM
Table1
WHERE (purchase_channel='online' or purchase_channel='store') and year=2020
) as T
GROUP BY customer_key
HAVING COUNT(customer_key)>=2
;
Telegram: DB developers channel