Apple-style Company ChallengeBeginnerVerified answerSQLite live

Active Apple Music Subscribers

Which customers currently hold an active Apple Music subscription, and what plan are they on?

  • Joins
  • Filtering
  • Sorting

Challenge brief

Understand the request

Apple Music Team needs a list of all paying subscribers for a campaign targeting active Apple Music users.

List active Apple Music subscribers with customer full name, email, plan type, and monthly price.

Return

  • full_name (first + last)
  • email
  • plan_type
  • monthly_price

Constraints

  • Return active Apple Music subscriptions only
  • Show higher monthly prices first and resolve ties by customer ID

Data you will use

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

customers

  • customer_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • emailVARCHAR(100)

apple_music_subscriptions

  • subscription_idINTEGER
  • customer_idINTEGER
  • plan_typeVARCHAR(30)
  • monthly_priceREAL
  • statusVARCHAR(20)

Hints, when you need them

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

Hint 1

Customer names are in customers. Subscription details are in apple_music_subscriptions. Connect on customer_id. Filter to status = 'active'.

Hint 2

INNER JOIN customers to apple_music_subscriptions on customer_id. WHERE ams.status = 'active'. Concatenate name with || ' ' ||. ORDER BY monthly_price DESC.

Hint 3

Build question 2 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 c.first_name || ' ' || c.last_name AS full_name, c.email, ams.plan_type, ams.monthly_price FROM customers c INNER JOIN apple_music_subscriptions ams ON c.customer_id = ams.customer_id WHERE ams.status = 'active' ORDER BY ams.monthly_price DESC, c.customer_id ASC;

Why this works

status = 'active' excludes David Brown whose subscription was cancelled. INNER JOIN connects each customer to their subscription row via customer_id.

Success check

5 active subscribers — David Brown is the only cancelled subscriber and is excluded

Expected result

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

full_nameemailplan_typemonthly_price
Bob Smithbob.s@gmail.comFamily16.99
Emma Davisemma.d@icloud.comFamily16.99
Alice Johnsonalice.j@icloud.comIndividual10.99
Frank Millerfrank.m@gmail.comIndividual10.99
Carol Whitecarol.w@icloud.comStudent5.99

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.