You are given customer account records and monthly Google Cloud usage data. Write a SQL query to find the customers in the top 10% of usage growth during the last completed quarter. Define growth as the difference between total usage in the last completed quarter and total usage in the quarter immediately before it. Exclude any account that churned within 30 days of signup. Return each qualifying customer's ID, name, prior-quarter usage, last-quarter usage, absolute growth, and percentile bucket, ordered by growth descending.
Assume the analysis date is 2024-10-15, so the last completed quarter is 2024-07-01 to 2024-09-30 and the prior quarter is 2024-04-01 to 2024-06-30.
customers| column | type | description |
|---|---|---|
| customer_id | INT | Unique customer ID |
| customer_name | VARCHAR(100) | Customer name |
| signup_date | DATE | Account signup date |
| churn_date | DATE | Account churn date, if any |
| account_tier | VARCHAR(20) | Contract tier |
monthly_usage| column | type | description |
|---|---|---|
| usage_id | INT | Unique usage row ID |
| customer_id | INT | Customer tied to the usage row |
| usage_month | DATE | First day of the usage month |
| product_name | VARCHAR(100) | Google product generating usage |
| usage_units | INT | Monthly usage units |
customers| customer_id | customer_name | signup_date | churn_date | account_tier |
|---|---|---|---|---|
| 101 | Acme Retail | 2024-01-10 | NULL | Enterprise |
| 102 | Bluebird Health | 2024-03-15 | 2024-04-10 | SMB |
| 103 | Cedar Logistics | 2024-02-01 | NULL | Mid-Market |
monthly_usage| usage_id | customer_id | usage_month | product_name | usage_units |
|---|---|---|---|---|
| 1 | 101 | 2024-04-01 | BigQuery | 100 |
| 2 | 101 | 2024-07-01 | BigQuery | 180 |
| 3 | 103 | 2024-04-01 | Google Kubernetes Engine | 50 |
| customer_id | customer_name | prior_quarter_usage | last_quarter_usage | usage_growth | growth_percentile |
|---|---|---|---|---|---|
| 106 | Foxtrot Media | 15 | 450 | 435 | 1 |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer account name |
| signup_date | DATE | Date the customer account was created |
| churn_date | DATE | Date the customer churned, if applicable |
| account_tier | VARCHAR(20) | Customer contract tier |
| Column | Type | Description |
|---|---|---|
| usage_idPK | INT | Unique usage record identifier |
| customer_id | INT | Customer linked to the usage record |
| usage_month | DATE | Month of usage, stored as the first day of the month |
| product_name | VARCHAR(100) | Google product associated with the usage |
| usage_units | INT | Measured monthly usage units |
| customer_id | customer_name | signup_date | churn_date | account_tier |
|---|---|---|---|---|
| 101 | Acme Retail | 2024-01-10 | Enterprise | |
| 102 | Bluebird Health | 2024-03-15 | 2024-04-10 | SMB |
| 103 | Cedar Logistics | 2024-02-01 | Mid-Market | |
| 104 | Delta Finance | 2024-05-20 | 2024-08-25 | Enterprise |
| 105 | Evergreen Travel | 2024-06-01 | SMB | |
| 106 | Foxtrot Media | 2024-01-05 | Enterprise | |
| 107 | Granite Foods | 2024-04-12 | 2024-05-05 | SMB |
| 108 | Helios Energy | 2024-02-18 | Mid-Market | |
| 109 | Indigo Labs | 2024-07-01 | ||
| 110 | Juniper Apps | 2024-03-01 | 2024-04-20 | SMB |
| 1 | Quartz Payments | 2024-06-14 | null | null |
| 2 | Indigo Labs | 2024-04-24 | null | Startup |
| 3 | Beacon Learning | 2024-02-08 | 2024-04-17 | Partner |
| 4 | Vertex Retail | 2024-01-31 | 2024-08-18 | Reseller |
| 5 | Yellowstone Data | 2024-01-27 | 2024-04-25 | Basic |
| 6 | Xenon Bio | 2024-05-05 | 2024-06-08 | Standard |
| 7 | Cedar Logistics | 2024-01-10 | 2024-07-17 | Manufacturing |
| 8 | Yellowstone Data | 2024-06-06 | 2024-07-28 | Nonprofit |
| 9 | Helios Energy | 2024-04-19 | 2024-07-28 | Manufacturing |
| 10 | Umbra Studios | 2024-05-18 | 2024-07-30 | Mid-Market |
| 11 | Xenon Bio | 2024-04-27 | 2024-07-18 | Education |
| 12 | Evergreen Travel | 2024-06-08 | 2024-04-27 | Financial Services |
| 13 | Vertex Retail | 2024-06-09 | 2024-05-08 | Premier |
| 14 | Pioneer Motors | 2024-03-01 | 2024-05-21 | Energy |
| 15 | Northstar Telecom | 2024-01-26 | null | Financial Services |
| 16 | Northstar Telecom | 2024-05-22 | null | Reseller |
| 17 | Northstar Telecom | 2024-06-13 | 2024-06-28 | Growth |
| 18 | Atlas Markets | 2024-06-08 | 2024-04-22 | Basic |
| 19 | Orchid Systems | 2024-04-08 | 2024-07-14 | Enterprise |
| usage_id | customer_id | usage_month | product_name | usage_units |
|---|---|---|---|---|
| 1 | 101 | 2024-04-01 | BigQuery | 100 |
| 2 | 101 | 2024-05-01 | BigQuery | 120 |
| 3 | 101 | 2024-06-01 | Looker | 130 |
| 4 | 101 | 2024-07-01 | BigQuery | 180 |
| 5 | 101 | 2024-08-01 | Looker | 190 |
| 6 | 101 | 2024-09-01 | BigQuery | 210 |
| 7 | 102 | 2024-04-01 | Google Analytics 4 | 300 |
| 8 | 102 | 2024-07-01 | Google Analytics 4 | 50 |
| 9 | 103 | 2024-04-01 | Google Kubernetes Engine | 200 |
| 10 | 103 | 2024-05-01 | Google Kubernetes Engine | 220 |
| 11 | 103 | 2024-06-01 | BigQuery | 210 |
| 12 | 103 | 2024-07-01 | Google Kubernetes Engine | 230 |
| 13 | 103 | 2024-08-01 | BigQuery | 240 |
| 14 | 103 | 2024-09-01 | Looker | 250 |
| 15 | 104 | 2024-05-01 | BigQuery | 50 |
| 16 | 104 | 2024-06-01 | Looker | 60 |
| 17 | 104 | 2024-07-01 | BigQuery | 70 |
| 18 | 104 | 2024-08-01 | Looker | 80 |
| 19 | 105 | 2024-06-01 | Google Analytics 4 | 40 |
| 20 | 105 | 2024-07-01 | Google Analytics 4 | 40 |
| 21 | 105 | 2024-08-01 | Google Analytics 4 | 40 |
| 22 | 105 | 2024-09-01 | Google Analytics 4 | 40 |
| 23 | 106 | 2024-04-01 | BigQuery | 5 |
| 24 | 106 | 2024-05-01 | Looker | 5 |
| 25 | 106 | 2024-06-01 | BigQuery | 5 |
| 26 | 106 | 2024-07-01 | BigQuery | 150 |
| 27 | 106 | 2024-08-01 | Looker | 150 |
| 28 | 106 | 2024-09-01 | Google Kubernetes Engine | 150 |
| 29 | 107 | 2024-04-01 | BigQuery | 500 |
| 30 | 108 | 2024-04-01 | BigQuery | 0 |
| 31 | 108 | 2024-05-01 | Looker | 0 |
| 32 | 108 | 2024-06-01 | BigQuery | 0 |
| 33 | 108 | 2024-07-01 | BigQuery | 10 |
| 34 | 108 | 2024-08-01 | Looker | 20 |
| 35 | 108 | 2024-09-01 | BigQuery | 30 |
| 36 | 109 | 2024-07-01 | Google Ads Data Hub | 100 |
| 37 | 109 | 2024-08-01 | Google Ads Data Hub | 100 |
| 38 | 109 | 2024-09-01 | Google Ads Data Hub | 100 |
| 39 | 110 | 2024-04-01 | BigQuery | 10 |
| 40 | 110 | 2024-05-01 | Looker | |
| 41 | 110 | 2024-07-01 | BigQuery | 20 |
| 42 | 2024-07-01 | BigQuery | 999 |
| customer_id | customer_name | prior_quarter_usage | last_quarter_usage | usage_growth | growth_percentile |
|---|---|---|---|---|---|
| 106 | Foxtrot Media | 15 | 450 | 435 | 1 |
| 109 | Indigo Labs | 0 | 300 | 300 | 1 |
| 101 | Acme Retail | 350 | 580 | 230 | 1 |
