Apple: Define what counts as an active subscriber and report the monthly count for the first half of the year.
The Services finance team needs a monthly active-subscriber count for a media subscription, January through June, for the quarterly review. The billing system exports one row per account per billing period, and the team has asked you to decide how "active" should be defined, apply it consistently across the six months, and give them the numbers.
Note: a billing period is the span of days an account has access for one payment, usually a month. An account's status for a period is trial (free access before the first payment), paid, grace (the payment failed and access was kept for up to 14 days while the card is retried), or cancelled (the customer cancelled during the period but keeps access until the period ends).
apple_subscription_periods.csv has one row per account per billing period:
account_idplan - individual or familyhousehold_size - number of people with access on the planperiod_startperiod_end - last day of access for the periodstatus - trial, paid, grace or cancelledYou can solve this in Python, SQL, or a mix of both — StrataScratch notebooks support both languages in the same notebook.
Assume this data comes directly from the billing system and hasn't been cleaned, so review it carefully before relying on the numbers. When something looks off, work out what it is and decide how to handle it; when a definition or a final decision is genuinely ambiguous, choose a reasonable option and explain why. There's often more than one valid way to compute a result, so explain the approach you take and the reasoning behind your choices.
What's assessed: whether your definition of "active" fits the finance team's purpose, whether you apply it correctly and consistently across months, and how clearly you justify it. There is no single correct number: a different, well-argued definition is a valid answer.