Your question is Top Customer Usage Growth Quartile. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| 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 |