logo

Back to questions

Weekly Churn Rates

Hard

Facebook is analyzing its user signup data for June 2022. Write a query to generate the churn rate by week in June 2022. Output the week number (1, 2, 3, 4, ...) and the corresponding churn rate rounded to 2 decimal places.

For example, week number 1 represents the dates from 30 May to 5 Jun, and week 2 is from 6 Jun to 12 Jun.

Assumptions:

  • If the last_login date is within 28 days of the signup_date, the user can be considered churned.
  • If the last_login is more than 28 days after the signup date, the user didn't churn.

Table:

Column NameType
user_idinteger
signup_datedatetime
last_logindatetime

Example Input:

user_idsignup_datelast_login
100106/01/2022 12:00:0007/05/2022 12:00:00
100206/03/2022 12:00:0006/15/2022 12:00:00
100406/02/2022 12:00:0006/15/2022 12:00:00
100606/15/2022 12:00:0006/27/2022 12:00:00
101206/16/2022 12:00:0007/22/2022 12:00:00

Example Output:

signup_weekchurn_rate
166.67
350.00

User ids 1001, 1002, and 1004 signed up in the first week of June 2022. Out of the 3 users, 1002 and 1004's last login is within 28 days from the signup date, hence they are churned users.

To calculate the churn rate, we take churned users divided by total users signup in the week. Hence 2 users / 3 users = 66.67%.

PostgreSQL 14

Refer friends to get bonus content & cool prizes.