Ads Campaign Creation Log SQL: Submit Count, Weekly Submit Rate, Time on Site

An ads platform lets advertisers create a campaign in a self-serve flow of three pages, followed by a submission: ```text objective -> adgroup -> creative -> submit ``` Every interaction with the flow is logged to an event table, `ttam_logger`: | Column | Type | Meaning | | --- | --- | --- | | `date` | STRING | Date partition, formatted `YYYYMMDD` | | `timestamp` | TIMESTAMP | Time of the event | | `advertiser_id` | BIGINT | Advertiser ID | | `event` | STRING | One of `enter`, `next`, `back`, `submit` | | `page` | STRING | One of `objective`, `adgroup`, `creative` | Write SQL for the three questions below. The queries do not need to run, but write the code and explain its logic as you go. Any mainstream SQL dialect is acceptable; note that the `date` partition is a string. ### Clarifying Questions - Can the same event be logged twice, for example by a client retry, and should duplicates be removed? - Is `enter` logged every time a page opens, including after `back`, or only once when the flow starts? - Does each event sit in the `date` partition of its own calendar date? ### Part 1 — Submissions on a given date Count the total number of submissions on a given date. When asked, the interviewer defined this as the number of `submit` events, not the number of distinct advertisers who submitted. ```hint Read only what you need The table is partitioned by `date`; decide which predicate lets the engine skip every other day. ``` #### What This Part Should Cover - Filtering on the partition column with a value in the right string format - Restricting to `submit` events and counting events, not advertisers - How duplicate events or a distinct-advertiser definition would change the query ### Part 2 — Overall submit rate for a given week Compute the overall submit rate for a given week. When asked about the denominator, the interviewer chose the number of `enter` events, so the rate is the number of `submit` events divided by the number of `enter` events in that week. ```hint Watch the division Think about the type of each count, what dividing one by the other returns in your dialect, and what happens if the week has no `enter` events. ``` #### What This Part Should Cover - Expressing the week as a range of `date` partitions, and why comparison works on this string format - Computing numerator and denominator in one pass - Decimal division and a zero denominator - What this event-over-event ratio does and does not measure ### Part 3 — Time on site per advertiser For a given date, compute each advertiser's total time on the site. Activity is observed only through logged events, and the site's maximum inactivity gap is 30 minutes: when more than 30 minutes pass between two consecutive events of the same advertiser, the advertiser is treated as having left in between, and none of that gap counts as time on site. A gap of exactly 30 minutes still counts. Taking `MAX(timestamp) - MIN(timestamp)` per advertiser is therefore not acceptable. The interviewer said sessions that cross midnight do not need to be handled. ```hint Pair up events Time on site accrues between consecutive events of the same advertiser. Think about how to put each event next to the one before it. ``` #### Clarifying Questions for this Part - Should an advertiser with a single event that day appear with zero time, or be left out? - Is any time credited after an advertiser's last event, or does time on site end at the last logged event? #### What This Part Should Cover - Ordering events within each advertiser and pairing each with its predecessor - Computing each gap in seconds and keeping only gaps of at most 30 minutes - The first event of each advertiser, single-event advertisers and tied timestamps - The output grain and units: one row per advertiser, with the unit of time stated ### What a Strong Answer Covers - Metric definitions settled before any SQL is written, and the confirmed definitions applied - Partition pruning on `date` and correct handling of the `YYYYMMDD` string - A window function partitioned by advertiser and ordered by time, with the 30-minute rule applied to each gap - The logic explained in words, with a small worked example - Data-quality caveats: duplicate events, late-arriving events, and per-page `enter` events inflating the denominator ### Follow-up Questions - The Part 2 ratio counts events. How would you instead report the share of advertisers who entered the flow during the week and submitted at least once? - How would you extend Part 3 so that a session running across midnight is measured correctly? - How would you split Part 3's total into sessions and report sessions per advertiser and the average session length? - Using the same table, how would you find the page where advertisers most often leave the flow without submitting?