Task
You are given customer booking activity and asked to track how behavior changes over time. Write a PostgreSQL query that returns each customer’s monthly booking count, the previous month’s booking count, the month-over-month change, and the cumulative bookings to date for that customer. Include only months where the customer had at least one booking.
Schema
| table | column | type | description |
|---|
| customer_bookings | booking_id | INT | Primary key for each booking |
| customer_bookings | customer_id | INT | Customer identifier |
| customer_bookings | customer_name | VARCHAR(100) | Customer name |
| customer_bookings | booking_date | DATE | Date the booking was made |
| customer_bookings | booking_status | VARCHAR(20) | Booking status such as confirmed or cancelled |
| customer_bookings | booking_value | NUMERIC(10,2) | Booking value in AUD |
Sample data
| booking_id | customer_id | customer_name | booking_date | booking_status | booking_value |
|---|
| 1 | 101 | Ava Chen | 2024-01-05 | confirmed | 240.00 |
| 2 | 101 | Ava Chen | 2024-02-10 | confirmed | 180.00 |
| 3 | 101 | Ava Chen | 2024-02-18 | cancelled | 0.00 |
| 4 | 102 | Ben Taylor | 2024-01-12 | confirmed | 310.00 |
| 5 | 102 | Ben Taylor | 2024-03-03 | confirmed | 155.00 |
| 6 | 103 | Chloe Nguyen | 2024-02-01 | confirmed | 120.00 |
| 7 | 103 | Chloe Nguyen | 2024-03-15 | confirmed | 220.00 |
| 8 | 104 | Daniel Wright | 2024-01-20 | cancelled | 0.00 |
Expected output
| customer_id | customer_name | activity_month | monthly_bookings | previous_month_bookings | booking_change | cumulative_bookings |
|---|
| 101 | Ava Chen | 2024-01 | 1 | null | null | 1 |
| 101 | Ava Chen | 2024-02 | 2 | 1 | 1 | 3 |
| 102 | Ben Taylor | 2024-01 | 1 | null | null | 1 |
| 102 | Ben Taylor | 2024-03 | 1 | 1 | 0 | 2 |
| 103 | Chloe Nguyen | 2024-02 | 1 | null | null | 1 |
| 103 | Chloe Nguyen | 2024-03 | 1 | 1 | 0 | 2 |
