8KLciczz

1CREATE TABLE IF NOT EXISTS payment(
2    customer_id INT NOT NULL,
3    payment_dt DATE NOT NULL,
4    close_dt DATE NOT NULL
5);
6
7INSERT INTO payment (customer_id, payment_dt, close_dt)
8VALUES
9(1, '2020-09-01', '2021-03-01'),
10(1, '2020-10-01', '2021-03-01'),
11(1, '2020-11-01', '2021-03-01'),
12(1, '2021-03-01', '2021-03-01'),
13(2, '2020-10-01', '2021-02-01'),
14(2, '2020-12-01', '2021-02-01'),
15(2, '2021-02-01', '2021-02-01'),
16(3, '2020-10-01', '2020-12-01'),
17(3, '2020-11-01', '2020-12-01'),
18(3, '2020-12-01', '2020-12-01'),
19(4, '2020-09-01', '2021-03-01'),
20(4, '2021-03-01', '2021-03-01');
21
22SELECT count(DISTINCT customer_id) - SUM(CASE WHEN payment_dt between '2020-10-01' and '2020-10-31' THEN 1 ELSE 0 END)
23    as oct FROM payment
24WHERE payment_dt <= '2020-10-31'
25AND close_dt > '2020-10-31';
26
27SELECT count(DISTINCT customer_id) - SUM(CASE WHEN payment_dt between '2020-11-01' and '2020-11-30' THEN 1 ELSE 0 END)
28           as nov FROM payment
29WHERE payment_dt <= '2020-11-30'
30  AND close_dt > '2020-11-30';
31
32SELECT count(DISTINCT customer_id) - SUM(CASE WHEN payment_dt between '2020-12-01' and '2020-12-31' THEN 1 ELSE 0 END)
33           as dec FROM payment
34WHERE payment_dt <= '2020-12-31'
35  AND close_dt > '2020-12-31';
36
37SELECT count(DISTINCT customer_id) - SUM(CASE WHEN payment_dt between '2021-01-01' and '2021-01-31' THEN 1 ELSE 0 END)
38           as jan FROM payment
39WHERE payment_dt <= '2021-01-31'
40  AND close_dt > '2021-01-31';
41