Adobe's Creative Cloud infrastructure team wants to identify accounts that exceeded their plan's included storage during March 2025. For each account, use its latest storage snapshot recorded in March 2025. Return only accounts whose latest `used_gb` is greater than the storage included in their plan. Return the columns in this exact order: `account_id`, `account_name`, `plan_name`, `used_gb`, `included_gb`, and `overage_gb`. Calculate `overage_gb` as `used_gb - included_gb`, rounded to two decimal places. If multiple snapshots have the same timestamp, use the snapshot with the greatest `snapshot_id`. Order the results by `account_id` in descending order. ### Table Schema | Table | Column | Type | |---|---|---| | `creative_accounts` | `account_id` | integer | | `creative_accounts` | `account_name` | varchar(80) | | `creative_accounts` | `plan_id` | integer | | `storage_plans` | `plan_id` | integer | | `storage_plans` | `plan_name` | varchar(40) | | `storage_plans` | `included_gb` | decimal(10,2) | | `storage_snapshots` | `snapshot_id` | integer | | `storage_snapshots` | `account_id` | integer | | `storage_snapshots` | `snapshot_at` | timestamp | | `storage_snapshots` | `used_gb` | decimal(10,2) | ### Example Input: `creative_accounts` | account_id | account_name | plan_id | |---:|---|---:| | 1 | Northstar Studio | 101 | | 2 | Pixel Forge | 102 | | 3 | Paper Crane | 101 | ### Example Input: `storage_plans` | plan_id | plan_name | included_gb | |---:|---|---:| | 101 | Teams | 100.00 | | 102 | Enterprise | 500.00 | ### Example Input: `storage_snapshots` | snapshot_id | account_id | snapshot_at | used_gb | |---:|---:|---|---:| | 11 | 1 | 2025-03-10 09:00:00 | 120.50 | | 12 | 1 | 2025-03-31 09:00:00 | 140.25 | | 13 | 2 | 2025-03-20 09:00:00 | 450.00 | | 14 | 3 | 2025-03-15 09:00:00 | 80.00 | ### Example Output Explanation Northstar Studio's latest March snapshot shows 140.25 GB used against a 100 GB allowance, resulting in a 40.25 GB overage. Pixel Forge is below its 500 GB allowance, and Paper Crane is below its 100 GB allowance, so neither is returned.
DROP TABLE IF EXISTS storage_snapshots; DROP TABLE IF EXISTS creative_accounts; DROP TABLE IF EXISTS storage_plans; CREATE TABLE creative_accounts ( account_id INT, account_name VARCHAR(80), plan_id INT ); CREATE TABLE storage_plans ( plan_id INT, plan_name VARCHAR(40), included_gb DECIMAL(10,2) ); CREATE TABLE storage_snapshots ( snapshot_id INT, account_id INT, snapshot_at TIMESTAMP, used_gb DECIMAL(10,2) ); INSERT INTO creative_accounts VALUES (1,'Northstar Studio',101), (2,'Pixel Forge',102), (3,'Paper Crane',101); INSERT INTO storage_plans VALUES (101,'Teams',100.00), (102,'Enterprise',500.00); INSERT INTO storage_snapshots VALUES (11,1,'2025-03-10 09:00:00',120.50), (12,1,'2025-03-31 09:00:00',140.25), (13,2,'2025-03-20 09:00:00',450.00), (14,3,'2025-03-15 09:00:00',80.00);