SQL - Conditions between 2 dates

Question

I have a table with 3 columns: user_id, start_date, cancellation_date

I would like to get 4 columns of: Date, Number of Active Users, Number of Users Who Canceled, Cancellation Rate

Any ideas on how to write a query of this?

Thanks in advance!

Answer 1

Here's a way to measure new users, cancelled users, active users:

SELECT  calendar_date,
        (SELECT COUNT(*) FROM users AS usr WHERE cal.calendar_date BETWEEN usr.start_date AND IFNULL(usr.cancellation_date, '3000-01-01')) AS active_users,
        (SELECT COUNT(*) FROM users AS usr WHERE usr.start_date = cal.calendar_date) AS new_users,
        (SELECT COUNT(*) FROM users AS usr WHERE usr.cancellation_date = cal.calendar_date) AS users_cancelled
FROM    my_calendar AS  cal

(assuming you have table "my_calendar" which contains all dates)

Answer 2

If we define active user as cancellation_date is null, the query will be like this;

SELECT 
( SELECT CURDATE() ) AS `DATE`,
( SELECT COUNT(*) FROM users WHERE cancellation_date IS NULL) AS `Number of active users`,
( SELECT COUNT(*) FROM users WHERE cancellation_date IS NOT NULL) AS `number of users who cancelled`,
( SELECT `number of users who cancelled` / (`Number of active users` + `number of users who cancelled`)) AS `cancellation rate`

Tested on MySQL 8, https://www.db-fiddle.com/f/6QPLn2C79rnzrfN71i1GMV/0