Daily Active Time per Advertiser with a One-Hour Session Gap Rule

An advertising platform records every action advertisers take in its ads-management tool in an event log. Compute each advertiser's daily active time from the log, using this session rule: if more than one hour passes with no action, the advertiser's next action starts a new session. Active time is measured from the gaps between consecutive events: - Order each advertiser's events by `event_timestamp`. For every event except the advertiser's first, its gap is the number of seconds since the advertiser's previous event. - A gap of at most 3600 seconds lies inside a session and counts as active time. A gap longer than 3600 seconds ends the session: it counts as zero, and the later event starts a new session. - Each gap is credited to the UTC calendar date of the later event, the one that ends the gap, even when the earlier event was on the previous day. So a session's active time runs from its first event to its last, and no time after a session's last event is counted. The report defines the one-hour session rule and says to accumulate only the gaps within one hour; this version fixes how gaps are assigned to days and which days appear in the output. ### Input Table(s) **`advertiser_event_log`**: one row per logged advertiser action. The table has no primary key. | Column | Type | Meaning | | --- | --- | --- | | `advertiser_id` | BIGINT | The advertiser who performed the action | | `event_timestamp` | TIMESTAMP | When the action happened, in UTC, stored without a time zone, to whole seconds | ### Output Contract Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns these columns, in this order: | Column | Type | Meaning | | --- | --- | --- | | `advertiser_id` | BIGINT | The advertiser | | `activity_date` | DATE | A UTC calendar date on which the advertiser has at least one event | | `active_seconds` | BIGINT | Total active seconds credited to that advertiser on that date | Return exactly one row for every advertiser and every UTC date on which that advertiser has at least one event, including dates whose total is 0. Order the rows by `advertiser_id`, then `activity_date`, both ascending. An empty table gives no rows. ### Example `advertiser_event_log`: | advertiser_id | event_timestamp | | --- | --- | | 7 | 2026-03-10 09:00:00 | | 7 | 2026-03-10 09:20:00 | | 7 | 2026-03-10 10:20:00 | | 7 | 2026-03-10 11:30:00 | | 7 | 2026-03-10 11:45:00 | | 7 | 2026-03-10 23:50:00 | | 7 | 2026-03-11 00:10:00 | | 7 | 2026-03-11 00:10:00 | | 7 | 2026-03-11 00:40:00 | | 9 | 2026-03-10 14:00:00 | | 9 | 2026-03-12 08:00:00 | | 9 | 2026-03-12 08:59:59 | Expected result: | advertiser_id | activity_date | active_seconds | | --- | --- | --- | | 7 | 2026-03-10 | 5700 | | 7 | 2026-03-11 | 3000 | | 9 | 2026-03-10 | 0 | | 9 | 2026-03-12 | 3599 | - Advertiser 7 on 2026-03-10: the gaps 09:00 to 09:20 (1200 seconds) and 09:20 to 10:20 (3600 seconds; exactly one hour still counts) are active. The 4200-second gap to 11:30 starts a new session, the 900-second gap to 11:45 is active, and the long gap to 23:50 starts another session. Total 1200 + 3600 + 900 = 5700. - Advertiser 7 on 2026-03-11: the 1200-second gap from 23:50 the previous day to 00:10 is credited to 2026-03-11, the second 00:10 event adds a gap of 0, and 00:10 to 00:40 adds 1800. Total 1200 + 0 + 1800 = 3000. - Advertiser 9 on 2026-03-10: its only event that day is the advertiser's first event, so the total is 0 and the date still appears. - Advertiser 9 on 2026-03-12: the gap from 2026-03-10 14:00 is far longer than an hour and starts a new session; 08:00:00 to 08:59:59 adds 3599. ### Constraints and Clarifications - No column contains NULL. Rows are stored in no particular order. - An advertiser can have several rows with the same `event_timestamp`. Each row is a separate event, and the gap between two equal timestamps is 0. - The one-hour boundary is inclusive: a gap of exactly 3600 seconds counts as active time; a gap of 3601 seconds does not. - Gaps are computed across date boundaries and are never split between two dates; the whole gap goes to the later event's date. - Dates are the UTC calendar dates of `event_timestamp` as stored; no time-zone conversion is needed. - Timestamps have whole-second precision, so every total is an integer. - Each advertiser's sessions depend only on that advertiser's own events. ```hint Midnight is the trap Make sure the 23:50 to 00:10 pair in the example still produces its 1200 seconds, credited to the second day, under your approach. ```