iCloud Multi-Device Storage UsersCompany: Apple | Difficulty: medium

Apple's iCloud team wants to identify users who spread their storage across different kinds of devices.

Write a query that returns every user who owns devices of more than one distinct device_type and whose devices use more than 50 GB of iCloud storage in total.

Only devices that have a row in storage_usage count toward the device total, the device type check, and the storage total. Each device has at most one row in storage_usage.
A NULL value in storage_used_gb contributes 0 GB.
total_devices is the number of counted devices the user owns, and total_storage_gb is the sum of storage_used_gb across those devices.

Output user_id, user_name, total_devices, and total_storage_gb, in that column order. Order the results by total_storage_gb descending, then by user_id ascending to break ties.

Table Schema

Table	Column	Type
`users`	`user_id`	integer
`users`	`user_name`	string
`users`	`email`	string
`users`	`country`	string
`devices`	`device_id`	integer
`devices`	`user_id`	integer
`devices`	`device_type`	string
`devices`	`purchase_date`	date
`storage_usage`	`device_id`	integer
`storage_usage`	`storage_used_gb`	integer
`storage_usage`	`last_update`	date

Example Input: `users`

user_id	user_name	email	country
101	Priya Raghavan	priya.raghavan@icloud.com	India
102	Marcus Hollis	m.hollis@icloud.com	United States
103	Sofia Marchetti	sofia.marchetti@icloud.com	Italy

Example Input: `devices`

device_id	user_id	device_type	purchase_date
1	101	iPhone	2023-09-22
2	101	MacBook	2022-11-05
3	102	iPhone	2021-10-08
4	102	iPhone	2023-09-15
5	103	iPad	2022-03-18
6	103	iPhone	2024-09-20

Example Input: `storage_usage`

device_id	storage_used_gb	last_update
1	38	2025-06-01
2	27	2025-06-01
3	40	2025-06-01
4	35	2025-06-01
5	20	2025-06-01
6	35	2025-06-01

Example Output Explanation

User 101 owns an iPhone and a MacBook (2 distinct types) using 38 + 27 = 65 GB, so they qualify with total_devices of 2. User 102 owns two devices using 75 GB in total, but both are an iPhone, so there is only one distinct type and they are excluded. User 103 owns an iPad and an iPhone using 20 + 35 = 55 GB, so they qualify. Sorting by total_storage_gb descending returns user 101 (65 GB) before user 103 (55 GB).

SchemaDROP TABLE IF EXISTS storage_usage; DROP TABLE IF EXISTS devices; DROP TABLE IF EXISTS users; CREATE TABLE users (user_id INT, user_name VARCHAR(100), email VARCHAR(150), country VARCHAR(60)); CREATE TABLE devices (device_id INT, user_id INT, device_type VARCHAR(50), purchase_date DATE); CREATE TABLE storage_usage (device_id INT, storage_used_gb INT, last_update DATE); INSERT INTO users VALUES (101, 'Priya Raghavan', 'priya.raghavan@icloud.com', 'India'), (102, 'Marcus Hollis', 'm.hollis@icloud.com', 'United States'), (103, 'Sofia Marchetti', 'sofia.marchetti@icloud.com', 'Italy'); INSERT INTO devices VALUES (1, 101, 'iPhone', '2023-09-22'), (2, 101, 'MacBook', '2022-11-05'), (3, 102, 'iPhone', '2021-10-08'), (4, 102, 'iPhone', '2023-09-15'), (5, 103, 'iPad', '2022-03-18'), (6, 103, 'iPhone', '2024-09-20'); INSERT INTO storage_usage VALUES (1, 38, '2025-06-01'), (2, 27, '2025-06-01'), (3, 40, '2025-06-01'), (4, 35, '2025-06-01'), (5, 20, '2025-06-01'), (6, 35, '2025-06-01');← All SQL Challenges · Apple Questions · Learn SQL Free