Identify the hour with the highest user activity in the dataset.

To find the hour with the most user activity you can bucket the timestamps to the hour level, count rows per bucket, and then pick the bucket with the maximum count. A typical PostgreSQL query looks like this:

SELECT DATE_TRUNC('hour', timestamp) AS hour,

   COUNT(*) AS activity_count

FROM user_events

GROUP BY hour

ORDER BY activity_count DESC

LIMIT 1;

DATE_TRUNC('hour', timestamp) normalises each timestamp to the start of its hour, so all events in the same hour share the same value. GROUP BY aggregates them, COUNT(*) tallies the events, and ORDER BY sorts the buckets by count. LIMIT 1 returns the top bucket. If you need to handle ties you could replace LIMIT 1 with a window function:

SELECT hour, activity_count

FROM (

SELECT DATE_TRUNC('hour', timestamp) AS hour,

     COUNT(*) AS activity_count,
     RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk

FROM user_events

GROUP BY hour

) sub

WHERE rnk = 1;

This returns all hours that share the maximum count. The query runs in O(n) time where n is the number of rows, plus the cost of sorting the distinct hour values, which is negligible for typical datasets.