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