Apple-style Company ChallengeEasyVerified answerSQLite live

iCloud Storage Plan Segments

How many customers fall into each iCloud storage tier, and what is the average storage in each group?

  • Aggregation
  • CASE expressions
  • Numeric functions
  • Sorting

Challenge brief

Understand the request

iCloud Business Team is reviewing the distribution of storage plan tiers to inform pricing strategy.

Segment customers by iCloud storage tier using CASE WHEN and show count and average per tier.

Return

  • storage_tier
  • customer_count
  • avg_storage_gb (rounded)

Constraints

  • Enterprise includes 2,000GB or more; Standard includes 200–1,999GB; Basic is below 200GB
  • Show the highest average storage first

Data you will use

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

customers

  • customer_idINTEGER
  • icloud_storage_gbINTEGER

Hints, when you need them

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

Hint 1

All data is in customers. Use CASE WHEN on the icloud_storage_gb column to assign each customer to a tier. Then GROUP BY that expression and COUNT.

Hint 2

CASE WHEN icloud_storage_gb >= 2000 THEN 'Enterprise (2TB+)' WHEN icloud_storage_gb >= 200 THEN 'Standard (200GB)' ELSE 'Basic (50GB)' END AS storage_tier. GROUP BY storage_tier. COUNT(*), ROUND(AVG(...)).

Hint 3

Build question 13 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 CASE WHEN icloud_storage_gb >= 2000 THEN 'Enterprise (2TB+)' WHEN icloud_storage_gb >= 200 THEN 'Standard (200GB)' ELSE 'Basic (50GB)' END AS storage_tier, COUNT(*) AS customer_count, ROUND(AVG(icloud_storage_gb), 0) AS avg_storage_gb FROM customers GROUP BY storage_tier ORDER BY avg_storage_gb DESC

Why this works

CASE WHEN evaluates top-down: >= 2000 is checked first (catches the 2TB customer), then >= 200 (catches the 200GB customers), then ELSE catches everyone else. GROUP BY on a CASE expression groups by the label it produces.

Success check

3 tiers — Enterprise (1 customer: Carol White at 2TB), Standard (3 customers at 200GB), Basic (2 customers at 50GB)

Expected result

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

storage_tiercustomer_countavg_storage_gb
Enterprise (2TB+)12000
Standard (200GB)3200
Basic (50GB)250

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.