-1

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

Table

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!

Iurii Tkachenko
  • 3,106
  • 29
  • 34
David Ribert
  • 23
  • 2
  • 2
    See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry May 31 '21 at 07:50
  • @DavidRibert . .. I would suggest that you ask a *new* question. In addition to sample data, show the desired results. Be quite clear on how each column is defined. – Gordon Linoff May 31 '21 at 12:12

2 Answers2

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)

Yair Maron
  • 1,860
  • 9
  • 22
0

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

sddk
  • 1,115
  • 1
  • 10
  • 20
  • Hi, thank you! in this query, I will get only the current date. any ideas on how can I get a list of all the dates? (Let's say from 1/1/2020) – David Ribert May 31 '21 at 09:29