Compute monthly signups, conversion, and YoY growth

You work at a subscription company and are given a user-level table.

Table

company_users

• id (INT, PK) — user/customer id
• signup_date (DATE) — date the user signed up (created an account)
• subscription_date (DATE, NULL) — date the user first started a subscription (NULL if never subscribed)
• termination_date (DATE, NULL) — date the subscription ended (NULL if still active)
• subscription_type (VARCHAR) — 'free' or 'paid' (assume this reflects the first subscription started)

Assumptions:

• Dates are in UTC.
• A “subscription” means subscription_date IS NOT NULL .
• “Conversion rate by month” is defined as: for users who signed up in a given calendar month, the fraction who ever subscribed (at any later time).
• Report results for months starting 2017-01-01 (inclusive).

Tasks

1. For each calendar month (by signup_date month), return:
   • month (e.g., 2017-01-01 for Jan 2017)
   • signups (count of users who signed up that month)
   • subscribers (count of those signups who ever subscribed)
   • conversion_rate = subscribers / signups
2. Compute YoY subscription rate growth for each month as:
   • yoy_subscription_rate_growth = $\frac{conversion\_rate - conversion\_rate\_{same\_month\_last\_year}}{conversion\_rate\_{same\_month\_last\_year}}$
   • Return NULL if the prior-year month is not available.
3. Now assume the dataset also includes free subscriptions ( subscription_type='free' ). Recompute (2) but only for paid subscriptions (i.e., treat a user as “subscribed” only if subscription_type='paid' ).

Output

A monthly table with columns:

• month , signups , subscribers , conversion_rate , yoy_subscription_rate_growth

(And a paid-only version for task #3.)