Google: Accounts Over Their Storage Quota — SQL Problem
Storage is shared across Gmail, Drive and Photos. The Google One team mails a warning to every account whose combined usage has gone past its plan's quota.
- Difficulty: Easy
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #675
Problem statement
Storage is shared across Gmail, Drive and Photos. The Google One team mails a warning to every account whose combined usage has gone past its plan's quota.
Return user_id, email, used_mb (the account's usage summed over all products) and quota_mb for every account whose used_mb is strictly greater than its quota_mb. An account exactly at its quota is not over it, and an account with no usage rows has used nothing. Return the rows in any order.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: Account
| Column | Type |
|---|---|
| user_id | int |
| varchar | |
| plan | enum(free, basic, premium) |
| quota_mb | int |
Primary key: user_id.
One row per account; quota_mb is the storage its plan allows.
Table: StorageUsage
| Column | Type |
|---|---|
| user_id | int |
| product | enum(gmail, drive, photos) |
| used_mb | int |
Primary key: user_id, product.
Current usage of one product by one account; a product the account never used has no row.
Examples
Example 1
Account
| user_id | plan | quota_mb | |
|---|---|---|---|
| 1 | asha@gmail.com | free | 15360 |
| 2 | ravi@gmail.com | free | 15360 |
| 3 | meera@gmail.com | basic | 102400 |
| 4 | kabir@gmail.com | free | 15360 |
| 5 | zara@gmail.com | premium | 204800 |
StorageUsage
| user_id | product | used_mb |
|---|---|---|
| 1 | gmail | 6000 |
| 1 | drive | 4000 |
| 1 | photos | 7000 |
| 2 | gmail | 15360 |
| 3 | photos | 99000 |
| 3 | drive | 5000 |
| 5 | drive | 1200 |
Output
| user_id | used_mb | quota_mb | |
|---|---|---|---|
| 1 | asha@gmail.com | 17000 | 15360 |
| 3 | meera@gmail.com | 104000 | 102400 |
How to solve Google: Accounts Over Their Storage Quota
An account's usage is spread over up to three rows of StorageUsage, so it has to be summed before it can be compared. Joining Account to StorageUsage and grouping by the account gives one group per account with usage; SUM(s.used_mb) is its total. The quota is a column of the account, constant inside the group, so it is listed in GROUP BY and can be selected and compared.
The comparison is on an aggregate, so it lives in HAVING: SUM(s.used_mb) > a.quota_mb. The strict > keeps an account sitting exactly on its quota out of the warning list. An account with no usage rows never forms a group in the inner join — correct, because zero usage is never over a quota.
The alternative sums usage in a derived table first and joins the totals back, which avoids grouping by the account's columns; a correlated scalar subquery per account works too. Each is one pass over StorageUsage with an index on user_id.
Reference solution (MySQL)
SELECT a.user_id, a.email, SUM(s.used_mb) AS used_mb, a.quota_mb
FROM Account a
JOIN StorageUsage s ON s.user_id = a.user_id
GROUP BY a.user_id, a.email, a.quota_mb
HAVING SUM(s.used_mb) > a.quota_mbAnother way
SELECT a.user_id, a.email, t.used_mb, a.quota_mb FROM Account a JOIN (SELECT user_id, SUM(used_mb) AS used_mb FROM StorageUsage GROUP BY user_id) t ON t.user_id = a.user_id WHERE t.used_mb > a.quota_mbAnother way
SELECT * FROM (SELECT user_id, email, (SELECT COALESCE(SUM(used_mb), 0) FROM StorageUsage s WHERE s.user_id = a.user_id) AS used_mb, quota_mb FROM Account a) x WHERE used_mb > quota_mb← Google: Published Play Apps With No Installs · Google: Well-Rated Places With Enough Reviews →