Task
BlueWave Cruises wants a daily summary of activity by ship and venue. Write a SQL query to aggregate booking data by day, ship, and venue.
Requirements
- Return one row per
booking_date, ship_name, and venue_name.
- Calculate the total number of bookings and total guest count for each group.
- Exclude rows where
booking_date is NULL.
- Sort the output by
booking_date, then ship_name, then venue_name.
Table Definition
venue_bookings
| Column | Type | Description |
|---|
| booking_id | INT | Unique booking record ID |
| booking_date | DATE | Date the booking was made |
| ship_name | VARCHAR(100) | Name of the ship |
| venue_name | VARCHAR(100) | Name of the venue on the ship |
| guest_count | INT | Number of guests in the booking |
| booking_status | VARCHAR(20) | Status of the booking |
Sample Data
| booking_id | booking_date | ship_name | venue_name | guest_count | booking_status |
|---|
| 1 | 2024-06-02 | Ocean Star | Main Dining | 2 | confirmed |
| 2 | 2024-06-01 | Coral Queen | Sky Lounge | 4 | confirmed |
| 3 | 2024-06-01 | Ocean Star | Main Dining | 3 | confirmed |
| 4 | 2024-06-01 | Ocean Star | Main Dining | 1 | cancelled |
| 5 | 2024-06-02 | Ocean Star | Sky Lounge | 5 | confirmed |
| 6 | 2024-06-01 | Coral Queen | Sky Lounge | 2 | waitlist |
| 7 | 2024-06-02 | Coral Queen | Main Dining | 0 | confirmed |
| 8 | NULL | Ocean Star | Main Dining | 2 | confirmed |
Expected Output
| booking_date | ship_name | venue_name | total_bookings | total_guests |
|---|
| 2024-06-01 | Coral Queen | Sky Lounge | 2 | 6 |
| 2024-06-01 | Ocean Star | Main Dining | 2 | 4 |
| 2024-06-02 | Coral Queen | Main Dining | 1 | 0 |
| 2024-06-02 | Ocean Star | Main Dining | 1 | 2 |
| 2024-06-02 | Ocean Star | Sky Lounge | 1 | 5 |
