Task
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.
Schema
| 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 |
Sample data
| 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 |
Expected output
| payment_bucket | payment_record_count |
|---|
| On Time | 4 |
| Late | 4 |
| Unpaid | 2 |
