Apple: iCloud Accounts Using More Than Their Plan
The iCloud growth team wants to send an upgrade prompt to accounts that are already storing more than their plan allows.
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #599
Problem statement
The iCloud growth team wants to send an upgrade prompt to accounts that are already storing more than their plan allows.
The plans hold free_5gb → 5 GB, plus_50gb → 50 GB, plus_200gb → 200 GB and plus_2tb → 2048 GB. Return every account whose used_gb is strictly greater than its plan's quota, with the columns account_id, plan, used_gb and over_by_gb (used minus quota, rounded to 1 decimal place). An account sitting exactly at its quota is not over. Return the rows in any order.
A practice scenario set at Apple: the tables and data are invented, and this is not a question from, or endorsed by, Apple.
Tables
Table: ICloudAccount
| Column | Type |
|---|---|
| account_id | int |
| apple_id | varchar |
| plan | enum(free_5gb, plus_50gb, plus_200gb, plus_2tb) |
| used_gb | decimal |
Primary key: account_id.
One row per Apple Account with iCloud. used_gb is the storage in use today, to one decimal place.
Examples
Example 1
ICloudAccount
| account_id | apple_id | plan | used_gb |
|---|---|---|---|
| 1 | aarav@icloud.com | free_5gb | 6.4 |
| 2 | diya@icloud.com | free_5gb | 5 |
| 3 | kabir@icloud.com | plus_50gb | 48.2 |
| 4 | meera@icloud.com | plus_50gb | 61.7 |
| 5 | rohan@icloud.com | plus_200gb | 199.9 |
| 6 | zara@icloud.com | plus_2tb | 2050.5 |
| 7 | ishaan@icloud.com | plus_2tb | 1200 |
Output
| account_id | plan | used_gb | over_by_gb |
|---|---|---|---|
| 1 | free_5gb | 6.4 | 1.4 |
| 4 | plus_50gb | 61.7 | 11.7 |
| 6 | plus_2tb | 2050.5 | 2.5 |
How to solve Apple: iCloud Accounts Using More Than Their Plan
The table holds the plan's name, not its size, so the first step is to translate each plan into its quota in gigabytes. A simple CASE plan WHEN 'free_5gb' THEN 5 … END does it; computing it inside a derived table lets the outer query both filter on it and subtract it without writing the CASE twice.
The filter is strict: an account at exactly 5.0 GB on the free plan is full but not over, so used_gb > quota_gb excludes it. over_by_gb is the difference rounded to one decimal place — rounding matters because the stored values are decimals and a subtraction such as 6.4 − 5 is not always exact in floating point.
Two other shapes give the same rows: a tiny lookup table built with UNION ALL and joined on the plan name (handy once the plan list grows), or nested IFs. All of them read the table once.
Reference solution (MySQL)
SELECT account_id, plan, used_gb, ROUND(used_gb - quota_gb, 1) AS over_by_gb
FROM (
SELECT account_id, plan, used_gb,
CASE plan WHEN 'free_5gb' THEN 5 WHEN 'plus_50gb' THEN 50 WHEN 'plus_200gb' THEN 200 ELSE 2048 END AS quota_gb
FROM ICloudAccount
) a
WHERE used_gb > quota_gbAnother way
SELECT a.account_id, a.plan, a.used_gb, ROUND(a.used_gb - q.quota_gb, 1) AS over_by_gb
FROM ICloudAccount a
JOIN (SELECT 'free_5gb' AS plan, 5 AS quota_gb UNION ALL SELECT 'plus_50gb', 50 UNION ALL SELECT 'plus_200gb', 200 UNION ALL SELECT 'plus_2tb', 2048) q ON q.plan = a.plan
WHERE a.used_gb > q.quota_gbAnother way
SELECT account_id, plan, used_gb, ROUND(used_gb - IF(plan = 'free_5gb', 5, IF(plan = 'plus_50gb', 50, IF(plan = 'plus_200gb', 200, 2048))), 1) AS over_by_gb FROM ICloudAccount WHERE used_gb > IF(plan = 'free_5gb', 5, IF(plan = 'plus_50gb', 50, IF(plan = 'plus_200gb', 200, 2048)))← Apple: Genius Bar No-Shows at Every Store · Apple: Developer Teams With No Approved App Yet →