Google: Accounts Over Their Storage Quota

Easy
675.

Google: Accounts Over Their Storage Quota

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

ColumnTypeValues
user_idint
emailvarchar
planenumfree, basic, premium
quota_mbint

Primary key: user_id.

One row per account; quota_mb is the storage its plan allows.

Table: StorageUsage

ColumnTypeValues
user_idint
productenumgmail, drive, photos
used_mbint

Primary key (together): user_id, product.

Current usage of one product by one account; a product the account never used has no row.

Example

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

Account

user_idemailplanquota_mb
1asha@gmail.comfree15360
2ravi@gmail.comfree15360
3meera@gmail.combasic102400
4kabir@gmail.comfree15360
5zara@gmail.compremium204800

StorageUsage

user_idproductused_mb
1gmail6000
1drive4000
1photos7000
2gmail15360
3photos99000
3drive5000
5drive1200

Output

user_idemailused_mbquota_mb
1asha@gmail.com1700015360
3meera@gmail.com104000102400
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.

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

ColumnType
user_idint
emailvarchar
planenum(free, basic, premium)
quota_mbint

Primary key: user_id.

One row per account; quota_mb is the storage its plan allows.

Table: StorageUsage

ColumnType
user_idint
productenum(gmail, drive, photos)
used_mbint

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_idemailplanquota_mb
1asha@gmail.comfree15360
2ravi@gmail.comfree15360
3meera@gmail.combasic102400
4kabir@gmail.comfree15360
5zara@gmail.compremium204800

StorageUsage

user_idproductused_mb
1gmail6000
1drive4000
1photos7000
2gmail15360
3photos99000
3drive5000
5drive1200

Output

user_idemailused_mbquota_mb
1asha@gmail.com1700015360
3meera@gmail.com104000102400

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_mb

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

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