Compute monthly signups, conversion, and YoY growth
Company: Intuit
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
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.)
Overview: This question evaluates a candidate's ability to perform data manipulation and time-series aggregation to compute monthly signups, subscription conversion rates, and year-over-year growth from user-level tables using SQL or Python (Data Manipulation (SQL/Python)).
Monthly signups, conversion rate, and YoY conversion-rate growth (all subscriptions)
You are given a companies table (signups) and a subscriptions table (who converted). Starting from 2017-01-01 (inclusive), compute monthly metrics by signup month:
- signups: number of companies that signed up in the month
- conversions: number of those companies that have a subscription of any plan type (free or paid)
- conversion_rate: conversions / signups
- yoy_conversion_rate_growth: the year-over-year growth of conversion_rate vs the same calendar month in the prior year, computed as:
(conversion_rate - prior_year_conversion_rate) / prior_year_conversion_rate
Return one row per signup month that exists in the data. If there is no prior-year month to compare, return NULL for yoy_conversion_rate_growth.
Output columns: month_start, signups, conversions, conversion_rate, yoy_conversion_rate_growth.
Round rates/growth to 4 decimal places.
Tables
companies(company_id INT, signup_date DATE)
subscriptions(subscription_id INT, company_id INT, start_date DATE, plan_type VARCHAR(10), termination_date DATE)
Hints
- Compute monthly signups first, then join the monthly result to itself shifted by 1 year.
- Use NULLIF to avoid division-by-zero and return NULL when prior-year data is missing.
Monthly signups and YoY growth for paid-only conversion rate
Using the same tables, compute the same monthly metrics starting from 2017-01-01 (inclusive), but only count conversions where the company has a PAID subscription (plan_type = 'paid').
- signups: companies signed up in that month
- paid_conversions: those signups that have a paid subscription
- paid_conversion_rate: paid_conversions / signups
- yoy_paid_conversion_rate_growth: year-over-year growth of paid_conversion_rate vs the same month in the prior year:
(paid_conversion_rate - prior_year_paid_conversion_rate) / prior_year_paid_conversion_rate
Return one row per signup month that exists in the data. Round rates/growth to 4 decimals.
Output columns: month_start, signups, paid_conversions, paid_conversion_rate, yoy_paid_conversion_rate_growth.
Tables
companies(company_id INT, signup_date DATE)
subscriptions(subscription_id INT, company_id INT, start_date DATE, plan_type VARCHAR(10), termination_date DATE)
Hints
- Filter paid subscriptions in the JOIN condition so signups with no paid conversion are still kept.
- Self-join the monthly aggregate on (month_start - interval '1 year') to compute YoY.
Pivot daily users and revenue by platform (web vs mobile)
You are given a purchases table with one row per purchase event.
For each file_date, compute:
- distinct purchasers and total revenue on web
- distinct purchasers and total revenue on mobile
Return one row per day with the following columns:
- day
- web_total_users
- web_total_revenue
- mobile_total_users
- mobile_total_revenue
If a platform has no purchases on a given day, return 0 for its totals.
This is a pivot-style output: one row per day, platforms as columns.
Tables
purchases(file_date DATE, user_id INT, sku VARCHAR(10), price DECIMAL(10,2), channel VARCHAR(20), customer_segment VARCHAR(20), platform VARCHAR(10))
Hints
- Use conditional aggregation (CASE WHEN) to pivot platform values into columns.
- Count distinct user_id per platform to get total users, and sum price for revenue.
Community answers
Answer by edu.usa4ever
Partial answers
df_data = company_users.copy()
df_data = df_data.sort_values(by = 'signup_date')
Get monthdf_data['month'] = df_data['signup_date'].dt.strftime('%Y-%m')# Get signupsdf_data['signups'] = df_data.groupby('month')['id'].transform('nunique')# Get subscribersdf_data['subscribed'] = df_data['subscription_date'].notna().astype(int)df_data['subscribers'] = df_data.groupby('month')['subscribed'].transform('sum')# Get conversion rate
df_data['conversion_rate'] = df_data['subscribed'] / df_data['signups'] * 100.0