User Subscription Analytics

You are provided with a user-level table from a subscription company and tasked with calculating key growth metrics.

Table Schema

company_users

• id (INT, PK): Unique identifier for the user/customer.
• signup_date (DATE): The date the user created an account.
• subscription_date (DATE, NULL): The date the user first started a subscription. This is NULL if the user has never subscribed.
• termination_date (DATE, NULL): The date the subscription ended. This is NULL if the subscription is still active.
• subscription_type (VARCHAR): Indicates the type of the first subscription, either 'free' or 'paid'.

Assumptions

• All dates are in UTC.
• A user is considered to have a "subscription" if their subscription_date is not NULL.
• "Conversion rate by month" is defined as: for users who signed up in a given calendar month, the fraction of those users who ever subscribed (at any point after their signup).
• All calculations and reports should begin with months starting from 2017-01-01 (inclusive).

Tasks

1. Monthly Signups and Conversion: For each calendar month, based on the signup_date, calculate and return the following:

   • month: The first day of the calendar month (e.g., 2017-01-01 for January 2017).
   • signups: The total count of users who signed up in that month.
   • subscribers: The count of users who signed up in that month and subsequently subscribed at any time.
   • conversion_rate: Calculated as subscribers / signups.

2. Year-over-Year (YoY) Subscription Rate Growth: Compute the YoY growth for the conversion_rate for each month. The formula is: yoy_subscription_rate_growth = (current_month_conversion_rate - prior_year_same_month_conversion_rate) / prior_year_same_month_conversion_rate If the prior_year_same_month_conversion_rate is not available (e.g., for months in the first year of data), return NULL for yoy_subscription_rate_growth.

3. Paid Subscription Analysis: Re-perform Task 2, but this time, consider a user as "subscribed" only if their subscription_type is 'paid'.

Expected Output

The final output should be a monthly table containing the columns: month, signups, subscribers, conversion_rate, and yoy_subscription_rate_growth. For Task 3, a separate, similar table focusing exclusively on paid subscriptions should be provided.