Apple-style Company ChallengeHardVerified answerSQLite live

Apple Music Cancellation Rate by Plan

For each Apple Music plan type, what is the total number of subscribers, how many have cancelled, and what is the cancellation rate?

  • Aggregation
  • CASE expressions
  • Numeric functions
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Apple Music Retention is investigating churn patterns by plan type to decide where to invest in retention campaigns.

Compute cancellation rate per plan type using conditional aggregation and percentage calculation.

Return

  • plan_type
  • total_subs
  • cancelled
  • cancel_rate_pct (rounded 1, % of total)

Constraints

  • Count cancelled subscriptions within each plan population
  • The rate calculation must remain safe for an empty denominator
  • Show the highest cancellation rates first, then subscriber count, then plan type

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

apple_music_subscriptions

  • plan_typeVARCHAR(30)
  • statusVARCHAR(20)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

All data is in apple_music_subscriptions. GROUP BY plan_type. Use SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) to count cancellations within each group. Divide by COUNT(*) for the rate.

Hint 2

SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled. Multiply by 100.0 (not 100) and divide by COUNT(*) for floating-point percentage. ROUND to 1 decimal.

Hint 3

Build question 20 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT plan_type, COUNT(*) AS total_subs, SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled, ROUND(SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 1) AS cancel_rate_pct FROM apple_music_subscriptions GROUP BY plan_type ORDER BY cancel_rate_pct DESC, total_subs DESC, plan_type

Why this works

Multiplying by 100.0 ensures floating-point division. David Brown's Individual subscription is the only cancelled one — 1 of 3 Individual subscribers = 33.3%. Family and Student plans have no cancellations. Single-table query — no JOIN needed.

Success check

3 plans — Individual has 33.3% churn (1 of 3), Family and Student have 0% churn

Expected result

Use this output to verify values, aliases, ordering, and row count.

plan_typetotal_subscancelledcancel_rate_pct
Individual3133.3
Family200
Student100

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.