SaaS: Seat Expansion
Slack
Problem Statement
In SaaS, net retention and expansion are vital. Slack monitors companies that are rapidly scaling their workspace.
Your goal is to find companies that have demonstrated sustained hyper-growth. Specifically, identify companies that increased their paid seat count by more than 20% compared to the previous month, and maintained this growth rate for at least 3 consecutive months.
Rules:
- Percentage Growth Formula: ((Current Month Seats - Previous Month Seats) / Previous Month Seats).
- A company must have at least 4 months of data to qualify for a 3-month growth streak (Month 1 to 2, Month 2 to 3, and Month 3 to 4).
- The growth must be strictly greater than 0.20 (20%) for each of the three transitions.
- Output columns: company_id, streak_end_month.
- streak_end_month is the month where the third consecutive 20%+ growth occurred.
- Result must be sorted by company_id ascending, then streak_end_month ascending.
Tests your understanding of
Window Functions, Percentage Change and Series Analysis
Input Tables
monthly_seats
| company_id(INTEGER) | month_date(DATE) | seat_count(INTEGER) |
|---|---|---|
| 1 | 2024-01-01 | 100 |
| 1 | 2024-02-01 | 130 |
| 1 | 2024-03-01 | 160 |
| 1 | 2024-04-01 | 200 |
| 2 | 2024-01-01 | 10 |
| 2 | 2024-02-01 | 15 |
| 2 | 2024-03-01 | 25 |
| 2 | 2024-04-01 | 40 |
Expected Output
| company_id(INTEGER) | streak_end_month(DATE) |
|---|---|
| 1 | 2024-04-01 |
| 2 | 2024-04-01 |
Tags
HardWindow FunctionsPercentage ChangeSeries Analysis
40-50 min
19%