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.
booking_date, ship_name, and venue_name.booking_date is NULL.booking_date, then ship_name, then venue_name.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 |
| 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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| booking_idPK | 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 onboard venue |
| guest_count | INT | Number of guests in the booking |
| booking_status | VARCHAR(20) | Booking status such as confirmed, cancelled, or waitlist |
| 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 | Ocean Star | Main Dining | 2 | confirmed | |
| 9 | 2024-06-03 | Ocean Star | Theater | confirmed | |
| 10 | 2024-06-02 | Coral Queen | Sky Lounge | 3 | |
| 11 | 2024-06-02 | Coastal Breeze | Theater | 4 | confirmed |
| 12 | 2024-05-29 | Tidal Echo | Observation Lounge | 0 | unpaid |
| 13 | 2024-05-29 | Coastal Breeze | Main Dining | 4 | checked_in |
| 14 | 2024-05-31 | Golden Current | Main Dining | 4 | manual_review |
| 15 | 2024-06-02 | Lagoon Majesty | Piano Bar | 2 | partially_paid |
| 16 | 2024-06-05 | Emerald Sky | Art Gallery | 1 | null |
| 17 | null | Sunset Pearl | Theater | -1 | no_show |
| 18 | null | Aurora Bay | Coffee Loft | -1 | no_show |
| 19 | 2024-06-01 | Celestial Wake | Main Dining | 0 | null |
| 20 | 2024-06-05 | Aurora Bay | Rooftop Cinema | -1 | standby |
| 21 | 2024-06-01 | Northern Star | Steakhouse | 5 | unpaid |
| 22 | 2024-06-02 | Sunset Pearl | Buffet Hall | 2 | transferred |
| 23 | 2024-05-30 | Pacific Dream | Sushi Corner | 4 | hold |
| 24 | 2024-05-31 | Voyager Spirit | Piano Bar | 6 | requested |
| 25 | 2024-06-02 | Starlight Deck | Sports Deck | null | approved |
| booking_date | ship_name | venue_name | total_bookings | total_guests |
|---|---|---|---|---|
| 2024-05-29 | Coastal Breeze | Main Dining | 1 | 4 |
| 2024-05-29 | Tidal Echo | Observation Lounge | 1 | 0 |
| 2024-05-30 | Pacific Dream | Sushi Corner | 1 | 4 |
| 2024-05-31 | Golden Current | Main Dining | 1 | 4 |
| 2024-05-31 | Voyager Spirit | Piano Bar | 1 | 6 |
| 2024-06-01 | Celestial Wake | Main Dining | 1 | 0 |
| 2024-06-01 | Coral Queen | Sky Lounge | 2 | 6 |
| 2024-06-01 | Northern Star | Steakhouse | 1 | 5 |
| 2024-06-01 | Ocean Star | Main Dining | 2 | 4 |
| 2024-06-02 | Coastal Breeze | Theater | 1 | 4 |
| 2024-06-02 | Coral Queen | Main Dining | 1 | 0 |
| 2024-06-02 | Coral Queen | Sky Lounge | 1 | 3 |
| 2024-06-02 | Lagoon Majesty | Piano Bar | 1 | 2 |
| 2024-06-02 | Ocean Star | Main Dining | 1 | 2 |
| 2024-06-02 | Ocean Star | Sky Lounge | 1 | 5 |
| 2024-06-02 | Starlight Deck | Sports Deck | 1 | null |
| 2024-06-02 | Sunset Pearl | Buffet Hall | 1 | 2 |
| 2024-06-03 | Ocean Star | Theater | 1 | null |
| 2024-06-05 | Aurora Bay | Rooftop Cinema | 1 | -1 |
| 2024-06-05 | Emerald Sky | Art Gallery | 1 | 1 |
