Netflix-style Company ChallengeHardVerified answerSQLite live

Plan Revenue Summary

Return plan, subscriber_count, revenue, and revenue_per_subscriber for each plan, ordered by revenue descending.

  • Subqueries
  • Aggregation
  • Numeric functions
  • NULL handling
  • Sorting

Challenge brief

Understand the request

Finance — Unit Economics Finance is building a unit-economics dashboard and needs ARPU (average revenue per user) alongside headcount and total revenue per plan.

Show subscriber count, total revenue, and revenue per subscriber for each plan.

Return

  • plan
  • subscriber_count
  • revenue
  • revenue_per_subscriber

Constraints

  • Return subscriber count, revenue, and average revenue per subscriber for each plan
  • Protect the ratio when a plan has zero subscribers
  • Order by revenue descending, then plan

Data you will use

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

subscriptions

  • planVARCHAR(20)
  • monthly_feeINTEGER

Hints, when you need them

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

Hint 1

Build subscriber count and revenue at plan grain first.

Hint 2

Protect the count before using it as a ratio denominator.

Hint 3

Round the derived metric and order plans deterministically.

Verified SQL answer

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

Reveal solution and explanation
WITH sub_counts AS (SELECT plan, COUNT(*) AS subscriber_count, SUM(monthly_fee) AS revenue FROM subscriptions GROUP BY plan) SELECT plan, subscriber_count, revenue, ROUND(revenue * 1.0 / NULLIF(subscriber_count, 0), 2) AS revenue_per_subscriber FROM sub_counts ORDER BY revenue DESC, plan

Why this works

The CTE computes headcount and revenue in one pass. The outer SELECT derives revenue_per_subscriber by dividing the two aggregated values. CAST(revenue AS FLOAT) prevents integer division truncation in SQLite.

Success check

Returns the complete deterministic result for plan revenue summary

Expected result

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

plansubscriber_countrevenuerevenue_per_subscriber
Premium36020
Standard46015
Basic3248

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.