Apple: iCloud Accounts Using More Than Their Plan

Easy
599.

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.

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

ColumnTypeValues
account_idint
apple_idvarchar
planenumfree_5gb, plus_50gb, plus_200gb, plus_2tb
used_gbdecimal

Primary key: account_id.

One row per Apple Account with iCloud. used_gb is the storage in use today, to one decimal place.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

ICloudAccount

account_idapple_idplanused_gb
1aarav@icloud.comfree_5gb6.4
2diya@icloud.comfree_5gb5
3kabir@icloud.complus_50gb48.2
4meera@icloud.complus_50gb61.7
5rohan@icloud.complus_200gb199.9
6zara@icloud.complus_2tb2050.5
7ishaan@icloud.complus_2tb1200

Output

account_idplanused_gbover_by_gb
1free_5gb6.41.4
4plus_50gb61.711.7
6plus_2tb2050.52.5
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
account_idint
apple_idvarchar
planenum(free_5gb, plus_50gb, plus_200gb, plus_2tb)
used_gbdecimal

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_idapple_idplanused_gb
1aarav@icloud.comfree_5gb6.4
2diya@icloud.comfree_5gb5
3kabir@icloud.complus_50gb48.2
4meera@icloud.complus_50gb61.7
5rohan@icloud.complus_200gb199.9
6zara@icloud.complus_2tb2050.5
7ishaan@icloud.complus_2tb1200

Output

account_idplanused_gbover_by_gb
1free_5gb6.41.4
4plus_50gb61.711.7
6plus_2tb2050.52.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_gb

Another 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_gb

Another 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 →