You are given a quickbooks_payments table with one row per invoice payment attempt. Write a SQL query that buckets each customer by payment behavior using a CASE WHEN statement, then returns the number of payment records in each bucket. Treat payments made on or before the due date as On Time, payments made after the due date as Late, and rows with no payment date as Unpaid. Return the bucket name and the count of records in each bucket, ordered by record count descending and then bucket name.
| column_name | type | description |
|---|---|---|
| payment_id | INT | Unique payment record ID |
| customer_name | VARCHAR(100) | Customer name |
| invoice_date | DATE | Date the invoice was issued |
| due_date | DATE | Date payment was due |
| payment_date | DATE | Date payment was received; NULL if unpaid |
| amount | DECIMAL(10,2) | Invoice amount |
| payment_id | customer_name | invoice_date | due_date | payment_date | amount |
|---|---|---|---|---|---|
| 101 | Acme Plumbing | 2024-01-03 | 2024-01-17 | 2024-01-15 | 250.00 |
| 102 | North Ridge Dental | 2024-01-05 | 2024-01-20 | 2024-01-25 | 400.00 |
| 103 | Cedar Bakery | 2024-01-08 | 2024-01-22 | NULL | 180.00 |
| 104 | Blue Harbor Studio | 2024-01-10 | 2024-01-24 | 2024-01-24 | 320.00 |
| payment_bucket | payment_record_count |
|---|---|
| On Time | 4 |
| Late | 4 |
| Unpaid | 2 |
| Column | Type | Description |
|---|---|---|
| payment_idPK | INT | Unique payment record ID |
| customer_name | VARCHAR(100) | Customer name in QuickBooks |
| invoice_date | DATE | Date the invoice was created |
| due_date | DATE | Date the invoice payment was due |
| payment_date | DATE | Date payment was received; NULL if unpaid |
| amount | DECIMAL(10,2) | Invoice amount |
| payment_id | customer_name | invoice_date | due_date | payment_date | amount |
|---|---|---|---|---|---|
| 101 | Acme Plumbing | 2024-01-03 | 2024-01-17 | 2024-01-15 | 250.00 |
| 102 | North Ridge Dental | 2024-01-05 | 2024-01-20 | 2024-01-25 | 400.00 |
| 103 | Cedar Bakery | 2024-01-08 | 2024-01-22 | 180.00 | |
| 104 | Blue Harbor Studio | 2024-01-10 | 2024-01-24 | 2024-01-24 | 320.00 |
| 105 | Maple Fitness | 2024-01-02 | 2024-01-16 | 2024-01-18 | 150.00 |
| 106 | Sunset Auto | 2024-01-12 | 2024-01-26 | 0.00 | |
| 107 | Pioneer Legal | 2024-01-04 | 2024-01-19 | 2024-01-19 | 600.00 |
| 108 | Evergreen Tutors | 2024-01-06 | 2024-01-21 | 2024-01-20 | 210.00 |
| 109 | Granite Foods | 2024-01-09 | 2024-01-23 | 2024-01-30 | -50.00 |
| 110 | Luna Design Co | 2024-01-11 | 2024-01-25 | 2024-01-27 | 275.00 |
| 1 | Vista Home Repair | 2024-01-12 | 2024-01-24 | 2024-01-23 | 569 |
| 2 | Willow Pet Care | 2024-01-02 | 2024-01-17 | 2024-01-23 | 569 |
| 3 | Riverstone Cafe | 2024-01-10 | 2024-01-21 | null | 225 |
| 4 | Cobalt Creative | 2024-01-03 | 2024-01-20 | 2024-01-30 | 572 |
| 5 | Redwood Consulting | 2024-01-09 | 2024-01-18 | 2024-01-12 | -106 |
| 6 | North Ridge Dental | 2024-01-15 | 2024-01-25 | 2024-01-19 | 553 |
| 7 | Blue Harbor Studio | 2024-01-04 | 2024-01-15 | 2024-01-24 | 597 |
| 8 | Urban Bloom Florist | 2023-12-30 | 2024-01-17 | 2024-01-13 | 460 |
| 9 | Clearwater Spa | 2024-01-15 | 2024-01-21 | 2024-01-29 | 342 |
| 10 | Clearwater Spa | 2024-01-03 | 2024-01-16 | 2024-01-14 | 347 |
| 11 | Redwood Consulting | 2024-01-09 | 2024-01-19 | 2024-01-25 | 174 |
| 12 | Golden State Movers | 2023-12-30 | 2024-01-18 | 2024-01-15 | 136 |
| 13 | Luna Design Co | 2023-12-30 | 2024-01-13 | 2024-01-31 | 648 |
| 14 | Cedar Bakery | 2024-01-05 | 2024-01-21 | 2024-01-27 | 39 |
| 15 | North Star HVAC | 2024-01-10 | 2024-01-20 | null | -132 |
| 16 | Silverline Electric | 2023-12-30 | 2024-01-22 | 2024-01-31 | -0 |
| 17 | Cedar Bakery | 2024-01-09 | 2024-01-16 | 2024-01-17 | 373 |
| payment_bucket | payment_record_count |
|---|---|
| Late | 13 |
| On Time | 10 |
| Unpaid | 4 |
