Question: Content Streaming Patterns
"Given watch history data (user_id, content_id, timestamp), find which hours of the day have the highest number of streams."

Step-by-Step Explanation:

You have a table called watch_history with:

user_id
content_id
watch_time (timestamp when the content was streamed)

You need to:

Find which hours of the day (like 0, 1, ..., 23) have the highest number of streams.

Sample Data (`watch_history`):

user_id	content_id	watch_time
1	101	2024-02-01 08:00:00
2	102	2024-02-01 21:00:00
1	103	2024-02-02 08:30:00
3	104	2024-02-02 23:45:00

Step 1: Extract the Hour from the Timestamp

Use HOUR(watch_time) to get the hour part (0 to 23) from the timestamp.

Example:

08:00 → hour 8
21:00 → hour 21
23:45 → hour 23

Step 2: Count the Number of Streams Per Hour

Group by the extracted hour and count how many watch events happened in each hour.

Step 3: Sort by Stream Count (Optional)

If you want to see which hours have the most streams, sort the results by the count in descending order.

SQL Query:

SELECT
  HOUR(watch_time) AS watch_hour,
  COUNT(*) AS stream_count
FROM
  watch_history
GROUP BY
  watch_hour
ORDER BY
  stream_count DESC;

Explanation of SQL:

HOUR(watch_time) extracts the hour from the timestamp.
COUNT(*) counts how many watch records happened during each hour.
GROUP BY watch_hour groups the counts by hour.
ORDER BY stream_count DESC shows the busiest hours first.

Expected Output:

watch_hour	stream_count
8	2
21	1
23	1

Explanation:

2 streams happened at 8 AM.
1 stream at 9 PM (21st hour).
1 stream at 11:45 PM (23rd hour).

Important Points:

HOUR() function works in most SQL databases (MySQL, Postgres, BigQuery).
Midnight is hour 0, noon is hour 12.
You can use ORDER BY watch_hour ASC instead if you want the hours in order from 0 to 23.