0J41fgTz

1
2CREATE TABLE IF NOT EXISTS plan_communication(
3    customer_id INT,
4    plan_comm_dt DATE,
5    comm_type VARCHAR(5)
6);
7
8CREATE TABLE IF NOT EXISTS fact_communication(
9    customer_id INT,
10    fact_comm_dt DATE,
11    comm_type VARCHAR(5)
12);
13
14INSERT INTO plan_communication (customer_id, plan_comm_dt, comm_type)
15VALUES
16(1, '2020-09-01', 'SMS'),
17(2, '2020-09-01', 'SMS'),
18(3, '2020-09-01', 'CALL'),
19(4, '2020-09-01', 'CALL'),
20(5, '2020-09-01', 'CALL'),
21(6, '2020-09-01', 'EMAIL'),
22(1, '2020-09-02', 'SMS'),
23(2, '2020-09-02', 'CALL'),
24(3, '2020-09-02', 'CALL'),
25(4, '2020-09-02', 'SMS'),
26(5, '2020-09-02', 'EMAIL'),
27(6, '2020-09-02', 'SMS');
28
29
30INSERT INTO fact_communication (customer_id, fact_comm_dt, comm_type)
31VALUES
32(1, '2020-09-01', 'SMS'),
33(2, '2020-09-01', 'CALL'),
34(3, '2020-09-01', 'SMS'),
35(4, '2020-09-01', 'CALL'),
36(5, '2020-09-01', 'CALL'),
37(6, '2020-09-01', 'EMAIL'),
38(1, '2020-09-02', 'CALL'),
39(2, '2020-09-02', 'CALL'),
40(3, '2020-09-02', 'EMAIL'),
41(4, '2020-09-02', 'SMS'),
42(5, '2020-09-02', 'SMS'),
43(6, '2020-09-02', 'CALL');
44
45
46SELECT CAST( SUM(case fact_communication.comm_type WHEN 'SMS' THEN 1 ELSE 0 END) AS DECIMAL(4,1))/count(*) * 100
47           as SMS_2020_09_01
48FROM plan_communication LEFT JOIN fact_communication
49                                  ON plan_communication.customer_id = fact_communication.customer_id
50WHERE fact_comm_dt = '2020-09-01' AND plan_comm_dt = '2020-09-01'
51  AND plan_communication.comm_type = 'SMS';
52
53SELECT CAST( SUM(case fact_communication.comm_type WHEN 'CALL' THEN 1 ELSE 0 END) AS DECIMAL(4,1))/count(*) * 100
54           as CALL_2020_09_01
55FROM plan_communication LEFT JOIN fact_communication
56                                  ON plan_communication.customer_id = fact_communication.customer_id
57WHERE fact_comm_dt = '2020-09-01' AND plan_comm_dt = '2020-09-01'
58  AND plan_communication.comm_type = 'CALL';
59
60SELECT CAST( SUM(case fact_communication.comm_type WHEN 'EMAIL' THEN 1 ELSE 0 END) AS DECIMAL(4,1))/count(*) * 100
61           as EMAIL_2020_09_01
62FROM plan_communication LEFT JOIN fact_communication
63                                  ON plan_communication.customer_id = fact_communication.customer_id
64WHERE fact_comm_dt = '2020-09-01' AND plan_comm_dt = '2020-09-01'
65  AND plan_communication.comm_type = 'EMAIL';
66
67SELECT CAST( SUM(case fact_communication.comm_type WHEN 'SMS' THEN 1 ELSE 0 END) AS DECIMAL(4,1))/count(*) * 100
68           as SMS_2020_09_02
69FROM plan_communication LEFT JOIN fact_communication
70                                  ON plan_communication.customer_id = fact_communication.customer_id
71WHERE fact_comm_dt = '2020-09-02' AND plan_comm_dt = '2020-09-02'
72  AND plan_communication.comm_type = 'SMS';
73
74SELECT CAST( SUM(case fact_communication.comm_type WHEN 'CALL' THEN 1 ELSE 0 END) AS DECIMAL(4,1))/count(*) * 100
75           as CALL_2020_09_02
76FROM plan_communication LEFT JOIN fact_communication
77                                  ON plan_communication.customer_id = fact_communication.customer_id
78WHERE fact_comm_dt = '2020-09-02' AND plan_comm_dt = '2020-09-02'
79  AND plan_communication.comm_type = 'CALL';
80
81
82SELECT CAST( SUM(case fact_communication.comm_type WHEN 'EMAIL' THEN 1 ELSE 0 END) AS DECIMAL(4,1))/count(*) * 100
83           as EMAIL_2020_09_02
84FROM plan_communication LEFT JOIN fact_communication
85                                  ON plan_communication.customer_id = fact_communication.customer_id
86WHERE fact_comm_dt = '2020-09-02' AND plan_comm_dt = '2020-09-02'
87  AND plan_communication.comm_type = 'EMAIL';