Write a query to identify the busiest hour of the day for support ticket creation using a Tickets table.

Answer

Extract the hour from each ticket's creation timestamp, count tickets per hour, and find the hour with the highest count.

Solution

SELECT HOUR(created_at) AS ticket_hour, COUNT(*) AS ticket_count
FROM Tickets
GROUP BY HOUR(created_at)
ORDER BY ticket_count DESC
LIMIT 1;

Step by Step

1. HOUR(created_at) extracts just the hour component (0-23) from each ticket's full timestamp.
2. GROUP BY HOUR(created_at) buckets every ticket into one of 24 hourly groups, regardless of the actual date.
3. Ordering by ticket_count DESC and taking the first row gives the single busiest hour across the entire dataset.

Alternative Method: Window Function, Returning All Hours Ranked

SELECT ticket_hour, ticket_count FROM (
  SELECT HOUR(created_at) AS ticket_hour, COUNT(*) AS ticket_count,
         RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
  FROM Tickets
  GROUP BY HOUR(created_at)
) x
WHERE rnk = 1;

This variant using RANK() correctly returns multiple hours if there's an exact tie for busiest - a plain LIMIT 1 approach would arbitrarily return just one of the tied hours and silently hide the tie.

Key Exam Traps

• Merging all dates into 24 hour-buckets is a deliberate design choice here - if the actual need were "busiest hour on a specific day" rather than "busiest hour of day overall," the query would need a WHERE date filter or additional grouping by date.
• Time zone consistency: If created_at is stored in UTC but the business operates across time zones, "hour of day" derived directly from a UTC column may not reflect local business hours - a common real-world gotcha behind this exact question.
• LIMIT 1 silently hides ties - always mention the RANK() alternative when asked "what if two hours tie for busiest?"