Write SQL and Python, run instantly in your browser, and track your progress.
As a Product Marketing Manager at Slack, you're preparing for the quarterly pricing review and need to analyze the company's self-serve subscription plans that include overage capabilities. The pricing team is evaluating how overage-enabled plans perform in terms of customer acquisition and revenue generation.
Your task is to create a comprehensive list of all currently available self-serve plans that include overage functionality. This analysis will help the team understand which plans offer flexibility for customers who exceed their base limits, and how these plans are positioned in terms of pricing.
The business requires you to focus specifically on plans that customers can sign up for without sales involvement (is_self_serve = 1) and that include overage charges (includes_overage = 1). Only plans with an active status should be considered, as discontinued plans are no longer relevant for customer acquisition.
To support pricing strategy discussions, the results must be sorted by base price from highest to lowest, allowing the team to easily identify premium plans first. If multiple plans have the same base price, they should be ordered by plan ID in ascending order for consistent reporting.
As a Product Marketing Manager at Slack, you're preparing for the quarterly pricing review and need to analyze the company's self-serve subscription plans that include overage capabilities. The pricing team is evaluating how overage-enabled plans perform in terms of customer acquisition and revenue generation.
Your task is to create a comprehensive list of all currently available self-serve plans that include overage functionality. This analysis will help the team understand which plans offer flexibility for customers who exceed their base limits, and how these plans are positioned in terms of pricing.
The business requires you to focus specifically on plans that customers can sign up for without sales involvement (is_self_serve = 1) and that include overage charges (includes_overage = 1). Only plans with an active status should be considered, as discontinued plans are no longer relevant for customer acquisition.
To support pricing strategy discussions, the results must be sorted by base price from highest to lowest, allowing the team to easily identify premium plans first. If multiple plans have the same base price, they should be ordered by plan ID in ascending order for consistent reporting.
| Column Name | Type |
|---|
| Column Name | Type |
|---|
| plan_id | INTEGER |
| plan_code | TEXT |
| plan_name | TEXT |
| billing_period | TEXT |
| base_price_cents | INTEGER |
| per_seat_cents | INTEGER |
| includes_overage | INTEGER |
| overage_per_unit_cents | INTEGER |
| trial_days | INTEGER |
| plan_id | INTEGER |
| plan_code | TEXT |
| plan_name | TEXT |
| billing_period | TEXT |
| base_price_cents | INTEGER |
| per_seat_cents | INTEGER |
| includes_overage | INTEGER |
| overage_per_unit_cents | INTEGER |
| trial_days | INTEGER |
| plan_id | plan_code | plan_name | billing_period | base_price_cents | per_seat_cents | includes_overage | overage_per_unit_cents | trial_days | is_self_serve | status | currency | created_at |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 |
| plan_id | plan_code | plan_name | billing_period | base_price_cents | per_seat_cents | includes_overage | overage_per_unit_cents | trial_days | is_self_serve | status | currency | created_at |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 |
| plan_id | plan_code | plan_name | billing_period | currency |
|---|---|---|---|---|
| 7 | PLAN-TEAM-ANN | Team (Annual) | annual | USD |
| 5 | PLAN-PROFESSIONAL-ANN | Professional (Annual) | annual | USD |
| 6 | PLAN-TEAM-MON | Team (Monthly) | monthly | USD |
| plan_id | plan_code | plan_name | billing_period | currency |
|---|---|---|---|---|
| 7 | PLAN-TEAM-ANN | Team (Annual) | annual | USD |
| 5 | PLAN-PROFESSIONAL-ANN | Professional (Annual) | annual | USD |
| 6 | PLAN-TEAM-MON | Team (Monthly) | monthly | USD |
4 rows returned. Self-serve plans with overage enabled, sorted by base price descending.
4 rows returned. Self-serve plans with overage enabled, sorted by base price descending.
Your final output should be a result set that meets the following requirements:
1. Filtering:
is_self_serve = 1).includes_overage = 1).status = 'active').2. Columns:
plan_idplan_codeYour final output should be a result set that meets the following requirements:
1. Filtering:
is_self_serve = 1).includes_overage = 1).status = 'active').2. Columns:
plan_idplan_code| is_self_serve | INTEGER |
| is_self_serve | INTEGER |
| status | TEXT |
| status | TEXT |
| currency | TEXT |
| currency | TEXT |
| created_at | TEXT |
| created_at | TEXT |
| PLAN-FREE-MON |
| PLAN-FREE-MON |
| Free (Monthly) |
| Free (Monthly) |
| monthly |
| monthly |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 14 |
| 14 |
| 1 |
| 1 |
| active |
| active |
| USD |
| USD |
| 2024-04-10 |
| 2024-04-10 |
| 2 | PLAN-STARTER-MON | Starter (Monthly) | monthly | 2900 | 900 | 0 | 0 | 14 | 1 | active | USD | 2024-05-12 |
| 2 | PLAN-STARTER-MON | Starter (Monthly) | monthly | 2900 | 900 | 0 | 0 | 14 | 1 | active | USD | 2024-05-12 |
| 3 | PLAN-STARTER-ANN | Starter (Annual) | annual | 29000 | 800 | 0 | 0 | 14 | 1 | active | USD | 2024-06-09 |
| 3 | PLAN-STARTER-ANN | Starter (Annual) | annual | 29000 | 800 | 0 | 0 | 14 | 1 | active | USD | 2024-06-09 |
| 4 | PLAN-PROFESSIONAL-MON | Professional (Monthly) | monthly | 7900 | 2500 | 1 | 25 | 14 | 1 | active | USD | 2024-09-24 |
| 4 | PLAN-PROFESSIONAL-MON | Professional (Monthly) | monthly | 7900 | 2500 | 1 | 25 | 14 | 1 | active | USD | 2024-09-24 |
| 5 | PLAN-PROFESSIONAL-ANN | Professional (Annual) | annual | 79000 | 2200 | 1 | 5 | 14 | 1 | active | USD | 2024-09-12 |
| 5 | PLAN-PROFESSIONAL-ANN | Professional (Annual) | annual | 79000 | 2200 | 1 | 5 | 14 | 1 | active | USD | 2024-09-12 |
| 4 | PLAN-PROFESSIONAL-MON | Professional (Monthly) | monthly | USD |
| 4 | PLAN-PROFESSIONAL-MON | Professional (Monthly) | monthly | USD |
plan_nameplan_namebilling_periodbilling_periodcurrencycurrency3. Ordering:
3. Ordering:
base_price_cents in descending order (highest price first).plan_id in ascending order.base_price_cents in descending order (highest price first).plan_id in ascending order.