Ageing report for overdue invoices
Collections at Cloudpeak work from an ageing report. Today is 2026-06-30. An invoice is overdue when it is unpaid (paid_on is NULL) and its due_on is before today. Days overdue is the number of days between due_on and 2026-06-30.
Dataset: Cloudpeak. Every plan change writes a new subscriptions row: the old row gets an end_date and the new row starts on that same date. A row with end_date NULL is the current plan. A customer who cancels has a last row whose end_date has no follow-up row.
Task: put every overdue invoice into one bucket by days overdue: 01-30 days, 31-60 days, 61-90 days or 90+ days. Return bucket, the number of invoices as invoices, the total as amount_usd (ROUND(x, 2)) and the average days overdue as avg_days_overdue (ROUND(x, 2)). Sort by bucket A to Z. Buckets with no invoice may be missing.
The data you can query
Each table is shown with its column types and the first rows, so you can see exactly what the data looks like.
| id INTEGER | company TEXT | country TEXT | industry TEXT | signup_date TEXT |
|---|---|---|---|---|
| 1 | Aurora Freight | Germany | Logistics | 2025-09-22 |
| 2 | Bluegrid Energy | United Kingdom | Energy | 2025-10-13 |
| 3 | Cedar Health Labs | United States | Healthcare | 2026-03-07 |
| 4 | Delta Pay Systems | Nigeria | Fintech | 2025-12-26 |
| 5 | Evergreen Retail Group | Canada | Retail | 2025-02-12 |
| 6 | Falcon Aerospace | United Arab Emirates | Aerospace | 2025-02-12 |
Showing the first 6 of 40 rows.
| id INTEGER | account_id INTEGER | user_id INTEGER | feature TEXT | event_ts TEXT |
|---|---|---|---|---|
| 1 | 1 | 12 | api | 2025-09-24 16:05:34 |
| 2 | 1 | 13 | api | 2026-04-29 12:15:40 |
| 3 | 2 | 21 | api | 2025-10-15 15:14:48 |
| 4 | 2 | 24 | sso | 2025-10-14 13:06:32 |
| 5 | 2 | 24 | alerts | 2025-10-16 10:57:11 |
| 6 | 3 | 33 | alerts | 2026-06-01 10:37:06 |
Showing the first 6 of 149 rows.
| id INTEGER | account_id INTEGER | issued_on TEXT | due_on TEXT | paid_on TEXT | amount_usd REAL |
|---|---|---|---|---|---|
| 1 | 1 | 2026-01-01 | 2026-01-31 | 2026-01-22 | 499 |
| 2 | 1 | 2026-02-01 | 2026-03-03 | 2026-02-13 | 499 |
| 3 | 1 | 2026-03-01 | 2026-03-31 | 2026-04-23 | 499 |
| 4 | 1 | 2026-04-01 | 2026-05-01 | 2026-04-23 | 499 |
| 5 | 1 | 2026-05-01 | 2026-05-31 | 2026-05-20 | 499 |
| 6 | 1 | 2026-06-01 | 2026-07-01 | NULL | 499 |
Showing the first 6 of 168 rows.
| id INTEGER | account_id INTEGER | plan TEXT | mrr REAL | start_date TEXT | end_date TEXT |
|---|---|---|---|---|---|
| 1 | 1 | Scale | 499 | 2025-09-22 | NULL |
| 2 | 2 | Starter | 49 | 2025-10-13 | NULL |
| 3 | 3 | Growth | 199 | 2026-03-07 | NULL |
| 4 | 4 | Growth | 199 | 2025-12-26 | NULL |
| 5 | 5 | Starter | 49 | 2025-02-12 | 2026-02-01 |
| 6 | 5 | Growth | 199 | 2026-02-01 | 2026-05-01 |
Showing the first 6 of 65 rows.
| id INTEGER | account_id INTEGER | opened_ts TEXT | resolved_ts TEXT | priority TEXT |
|---|---|---|---|---|
| 1 | 2 | 2025-11-28 08:44:00 | NULL | Normal |
| 2 | 37 | 2026-01-29 15:51:00 | 2026-01-31 09:55:00 | Low |
| 3 | 18 | 2025-05-01 13:33:00 | 2025-05-04 09:13:00 | Low |
| 4 | 11 | 2025-09-24 16:09:00 | 2025-09-24 19:40:00 | Normal |
| 5 | 39 | 2025-12-06 15:34:00 | 2025-12-07 14:17:00 | High |
| 6 | 15 | 2026-03-20 11:06:00 | 2026-03-23 20:44:00 | Low |
Showing the first 6 of 40 rows.
Your query runs on SQLite. Write a single SELECT; use LIMIT instead of TOP.
Need a hint?
julianday() turns a date into a number, so subtracting two of them gives days. Compute days overdue once in a CTE.
Log in or create a free account to run your code, submit and earn points.
Run your code to see the output here. Ctrl + Enter also runs it.