Your question is Monthly Active Asset Growth Rate. 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 deployment records for hardware assets and a reference table of assets. Write a PostgreSQL query to calculate the month-over-month growth rate of active hardware assets deployed in the field. Treat an asset as active in a month if it was deployed on or before the end of that month and has not been retired before that month ends. Only include assets whose current status is active and whose deployment location is field.
Return one row per month with the month, the count of active deployed assets, the previous month's count, and the month-over-month growth rate as a percentage. For the first month in the result, the previous month count and growth rate should be NULL.
| Column | Type | Description |
|---|---|---|
| asset_idPK | INT | Unique hardware asset identifier |
| asset_name | VARCHAR(100) | Human-readable asset name |
| asset_type | VARCHAR(50) | Type of hardware asset |
| status | VARCHAR(20) | Current asset status such as active, retired, or maintenance |
| Column | Type | Description |
|---|---|---|
| deployment_idPK | INT | Unique deployment record identifier |
| asset_id | INT | References hardware_assets.asset_id |
| deployed_at | DATE | Date the asset was deployed |
| retired_at | DATE | Date the deployment ended, if retired |
| location_type | VARCHAR(20) | Deployment location classification such as field, lab, or staging |