Signups by Month
The growth chart in Loopwire's monthly deck is a simple bar per month: how many new companies came on board. The signup dates are stored to the day, which is too fine a grain to plot — eight bars of height one tells nobody anything — so they need collapsing to the month they fall in, written as year and month with a hyphen between, like 2023-04.
accounts — one row per customer company. industry is the sector that company works in; signup_date is the day it created its Loopwire account.
| id | company_name | industry | signup_date |
|---|---|---|---|
| 1 | Northwind Traders | retail | 2022-11-03 |
| 2 | Vertex Analytics | software | 2022-12-15 |
| 3 | Bluepeak Logistics | logistics | 2023-01-20 |
| 4 | Fernwood Studio | media | 2023-02-05 |
| 5 | Cobalt Health | healthcare | 2023-02-18 |
| 6 | Ridgeline Capital | finance | 2023-03-01 |
| 7 | Sable & Co | retail | 2023-03-22 |
| 8 | Hearth Robotics | manufacturing | 2023-04-10 |
The table already exists in the database — there is nothing to create or load.
Task: Write a query that returns one row per month in which at least one company signed up, with columns month (text in YYYY-MM form) and signups (how many companies signed up that month), oldest month first. A month with no signups does not appear.
Example output
Shape only — these months and figures are invented:
| month | signups |
|---|---|
| 2021-07 | 1 |
| 2021-08 | 3 |
| 2021-09 | 2 |
| 2021-10 | 1 |
| 2021-11 | 4 |
(...1 more row not shown)
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in