Task
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.
Schema
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 |
Sample data
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 |
Expected output
| customer_id | customer_name | prior_quarter_usage | last_quarter_usage | usage_growth | growth_percentile |
|---|
| 106 | Foxtrot Media | 15 | 450 | 435 | 1 |
