Here is a T-SQL puzzle about New Year’s Eve that Copilot created for you:-- You have a table called PartyGuests with the following columns:
-- GuestID int, Name varchar(50), RSVP bit, ArrivalTime time, DepartureTime time
-- The RSVP column indicates whether the guest has confirmed their attendance (1) or not (0)
-- The ArrivalTime and DepartureTime columns store the time when the guest arrived and left the party, respectively
-- Write a query to find the name and arrival time of the guest who arrived last before midnight
-- Assume that the party started at 8:00 PM and ended at 2:00 AM
-- If there is more than one guest who arrived at the same time, return all of them
-- If no guest arrived before midnight, return 'No guest arrived before midnight' as the name and NULL as the arrival time
Here is a table definition and some sample data:
-- Create the PartyGuests table
CREATE TABLE PartyGuests (
GuestID int PRIMARY KEY,
Name varchar(50) NOT NULL,
RSVP bit NOT NULL,
ArrivalTime time NULL,
DepartureTime time NULL
);
-- Insert some sample data
INSERT INTO PartyGuests (GuestID, Name, RSVP, ArrivalTime, DepartureTime)
VALUES
(1, 'Alice', 1, '21:15:00', '01:30:00'),
(2, 'Bob', 0, NULL, NULL),
(3, 'Charlie', 1, '22:45:00', '00:30:00'),
(4, 'David', 1, '23:59:00', '01:00:00'),
(5, 'Eve', 1, '23:59:00', '02:00:00'),
(6, 'Frank', 1, '00:15:00', '01:45:00');
How many rows does this query return?
SELECT Name, ArrivalTime
FROM PartyGuests
WHERE ArrivalTime = (
SELECT MAX(ArrivalTime)
FROM PartyGuests
WHERE ArrivalTime < '00:00:00'
)
I hope you enjoy this puzzle and have a happy new year! |
