Advertisers Active in Both the Current and Previous Week from an Ads Activity Log

An advertising platform logs every action advertisers take in its ads-management tool in a table named `ttam_logger`. Besides the event time and the advertiser, every row carries two hardcoded reporting dates: the start of the current week and the start of the previous week. Return the advertisers who were active in both the current week and the previous week, meaning they have at least one logged event in each of the two weeks. The report lists the table's columns, notes that the two week-start columns hold hardcoded dates, and says the task is to pin down the date ranges first and then aggregate by advertiser. This version fixes the week boundaries as follows: - The previous week covers every event with `previous_week_start <= event_timestamp < current_week_start`. - The current week covers every event with `current_week_start <= event_timestamp < current_week_start + 7 days`. - Events outside both ranges are ignored. ### Input Table(s) **`ttam_logger`**: one row per logged advertiser action. The table has no primary key. | Column | Type | Meaning | | --- | --- | --- | | `event_timestamp` | TIMESTAMP | When the action happened, in UTC, stored without a time zone, to whole seconds | | `advertiser_id` | BIGINT | The advertiser who performed the action | | `current_week_start` | DATE | First day of the current week; the same value on every row | | `previous_week_start` | DATE | First day of the previous week, exactly 7 days before `current_week_start`; the same value on every row | ### Output Contract Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per qualifying advertiser, with exactly this column: | Column | Type | Meaning | | --- | --- | --- | | `advertiser_id` | BIGINT | An advertiser with at least one event in the previous week and at least one event in the current week | Each qualifying advertiser appears exactly once. Order the rows by `advertiser_id` ascending. If no advertiser qualifies, including when the table is empty, return no rows. ### Example `ttam_logger`: | event_timestamp | advertiser_id | current_week_start | previous_week_start | | --- | --- | --- | --- | | 2026-03-03 10:15:00 | 101 | 2026-03-09 | 2026-03-02 | | 2026-03-10 09:00:00 | 101 | 2026-03-09 | 2026-03-02 | | 2026-03-04 12:00:00 | 102 | 2026-03-09 | 2026-03-02 | | 2026-03-04 12:30:00 | 102 | 2026-03-09 | 2026-03-02 | | 2026-03-08 23:59:59 | 103 | 2026-03-09 | 2026-03-02 | | 2026-03-09 00:00:00 | 103 | 2026-03-09 | 2026-03-02 | | 2026-03-01 18:00:00 | 104 | 2026-03-09 | 2026-03-02 | | 2026-03-12 08:00:00 | 104 | 2026-03-09 | 2026-03-02 | | 2026-03-05 07:45:00 | 105 | 2026-03-09 | 2026-03-02 | | 2026-03-16 00:00:00 | 105 | 2026-03-09 | 2026-03-02 | | 2026-03-02 00:00:00 | 106 | 2026-03-09 | 2026-03-02 | | 2026-03-15 23:59:59 | 106 | 2026-03-09 | 2026-03-02 | | 2026-03-15 23:59:59 | 106 | 2026-03-09 | 2026-03-02 | Here the previous week runs from 2026-03-02 00:00:00 up to (not including) 2026-03-09 00:00:00, and the current week from 2026-03-09 00:00:00 up to (not including) 2026-03-16 00:00:00. Expected result: | advertiser_id | | --- | | 101 | | 103 | | 106 | - Advertiser 101: one event in each week, so it qualifies. - Advertiser 102: both events fall in the previous week, so it does not qualify. - Advertiser 103: 2026-03-08 23:59:59 is the last second of the previous week and 2026-03-09 00:00:00 is the first second of the current week, so it qualifies. - Advertiser 104: 2026-03-01 is before the previous week starts, so only its current-week event counts and it does not qualify. - Advertiser 105: 2026-03-16 00:00:00 is the first second after the current week ends, so only its previous-week event counts and it does not qualify. - Advertiser 106: events on the first second of the previous week and the last second of the current week, so it qualifies; the duplicated row does not make it appear twice. ### Constraints and Clarifications - No column contains NULL. - `current_week_start` and `previous_week_start` hold the same pair of dates on every row, and `previous_week_start` is always `current_week_start` minus 7 days. Read the week boundaries from these columns: the test data may use different dates than the example, so do not type literal dates into the query. - Identical rows can occur, and an advertiser can have any number of events in a week; one event in each week is enough to qualify. - Events before `previous_week_start` or at or after `current_week_start + 7 days` count toward neither week. - Week boundaries are midnight UTC on the stated dates, compared with `event_timestamp` as stored; no time-zone conversion is needed. ```hint Bound each week on both sides Write each week as a half-open range of timestamps, and check the example's boundary rows against your ranges before you aggregate. ```