Cloud Storage Limit
ASKED IN INTERVIEWAdobe
Problem Statement
Adobe Creative Cloud provides users with a 20GB storage limit. To improve user experience, the system needs to identify power users who are nearing their limit so they can receive a notification to upgrade.
Write an SQL query to find all users who have used 90% or more of their 20GB storage.
Rules:
- Total capacity is fixed at 20GB.
- Usage percentage is calculated as (storage_used_gb / 20.0) * 100.
- Only include users where the usage percentage is >= 90.
- Output columns: user_id, storage_used_gb, usage_percentage.
- Sort the results by usage_percentage in descending order.
Table Schema:
- UserStorage: user_id, storage_used_gb (DECIMAL), last_sync_at.
Tests your understanding of
Basic SQL, Arithmetic and Filtering
Input Tables
UserStorage
| user_id(INTEGER) | storage_used_gb(DECIMAL) |
|---|---|
| 101 | 18.5 |
| 102 | 15 |
| 103 | 19.8 |
| 104 | 17.9 |
| 105 | 18 |
| 106 | 5.2 |
Expected Output
| user_id(INTEGER) | storage_used_gb(DECIMAL) | usage_percentage(DECIMAL) |
|---|---|---|
| 103 | 19.8 | 99 |
| 101 | 18.5 | 92.5 |
| 105 | 18 | 90 |
Tags
EasyASKED IN INTERVIEWBasic SQLArithmeticFiltering
10 min
76%