skip to content

A click can arrive a day after the impression — when may the nightly job label that impression a non-click?

level: seniorimportance: must knowfreq 70%

answer

  1. absence of a click is a claim about the future
  2. two time conditions, not one
  3. the job lags the log
  4. the newest rows lose their positives
  5. maturity condition on served_at

basics

~20 s

Only after the impression's attribution window has fully closed. Until then a click can still attach, so the assembly job must lag the log by at least the window length or it labels recent impressions as non-clicks that simply have not been clicked yet.

solid answer

~50 s

A negative is a claim that no click arrived, and that claim is only checkable once no further click *can* arrive. So the label join has two time conditions, not one: the click must fall inside `[served_at, served_at + window)`, and the impression itself must be older than the window before the job may emit a zero for it. If the job labels right up to the current hour, the newest rows are systematically wrong — their clicks are still in flight — and the measured click rate decays towards the edge of the data. The cost is freshness: with a 24-hour window, tonight's training set ends a day behind the log. The alternatives are to emit provisional labels and correct them later, or to model the censoring explicitly; both still require the pipeline to record which rows were immature.

code

sql · 11 lines
sql
SELECT i.impression_id,
       i.served_at,
       i.context_key,
       MAX(CASE WHEN c.impression_id IS NULL THEN 0 ELSE 1 END) AS label
FROM impressions i
LEFT JOIN clicks c
       ON c.impression_id = i.impression_id
      AND c.clicked_at  >= i.served_at
      AND c.clicked_at  <  i.served_at + INTERVAL '24' HOUR
WHERE i.served_at < CURRENT_TIMESTAMP - INTERVAL '24' HOUR
GROUP BY i.impression_id, i.served_at, i.context_key

go deeper

for a junior

Hold on to the core fact: a non-click label says no click ever arrived, and that can only be known once the attribution window has passed. Until then the row's outcome is simply unknown.

for a middle

Explain both time conditions in the join — the click must fall inside the window, and the impression must be older than the window before a zero may be written — and why event time rather than arrival time governs the match.

for a senior

Diagnose the shape of the failure: the click rate decaying towards the newest rows, the corruption concentrated in the freshest slice, and the freshness-versus-correctness trade you consciously made.

for a principal

The judgment call is how much freshness the organisation buys with complexity: a lagging build that is write-once, against provisional labels that must be corrected and re-frozen, and who owns the consistency of consumers that read the provisional version.

## The window is a business rule the pipeline has to honour An attribution window is a product and measurement decision — a click counts for an impression if it lands within some stated interval of the serve, and a conversion counts within a longer one. The assembly job does not get to choose it; it has to implement it exactly, because the same window governs what advertisers are billed for and what the model is trained to predict. If the two disagree, the model is optimising a target the business does not buy. The join therefore carries **two** distinct time conditions, and they do different jobs: 1. **The match condition** — a click attaches to an impression only if `served_at <= clicked_at < served_at + window`. Without it, a click weeks later would turn an old impression positive. 2. **The maturity condition** — the impression may only be *labelled at all* if `served_at < now - window`. Without it, the job asserts a negative about an outcome that has not finished arriving. ## Why a missing maturity condition is not a small error The defect is not random noise; it is a gradient. The older an impression is when the job runs, the more of its window has elapsed and the more of its clicks have landed. Rows near the edge of the data have had almost no time at all. | impression age at build time | share of window elapsed | labels produced | |---|---|---| | older than the window | 100% | correct positives and correct negatives | | about half the window | ~50% | roughly half the positives are mislabelled zero | | minutes old | ~0% | almost every row is a false negative | Two consequences follow. First, the measured click rate **falls steadily towards the newest rows**, so any base rate computed over the whole set is biased low. Second, and worse, the corruption is concentrated exactly in the slice that most resembles serving conditions — the most recent data, the part a stakeholder will ask you to weight most heavily. If any feature correlates with recency, the model learns that recency predicts *no click*, which is an artefact of the build, not of user behaviour. ## Three ways to handle it, and what each costs - **Lag the job.** Build rows for day `D` only after `D + window`. Simple, fully correct, and it costs exactly one window of freshness. This is the default answer and it is a good one. - **Emit provisional labels, correct later.** Build the tail immediately with a `mature: false` marker and rewrite those rows once the window closes. Freshness improves; the cost is that the set is no longer write-once, and any downstream consumer that read the provisional rows now holds a different dataset than the one that exists today. Freezing and versioning the corrected set is a separate concern with its own owner. - **Model the delay explicitly.** Treat the unmatched recent rows as censored rather than negative and carry the elapsed-window fraction as an input. That is a modelling choice; the pipeline's obligation is unchanged — it must record, per row, how much of the window had elapsed when the label was taken, or the modelling side has nothing to condition on. ## Details the join has to get right - **Collapse repeated clicks.** Two clicks on one impression are still one positive row. A plain outer join emits two, which double-counts the positive and shifts the base rate. - **Drop out-of-window clicks rather than reassigning them.** A late click is not evidence for the next impression of the same ad. - **Use event time, not processing time.** A beacon that arrives late but happened inside the window is a positive; one that happened outside it is not, however promptly it was ingested. - **Nest the windows for multi-stage outcomes.** If conversions attribute over a much longer interval than clicks, a conversion model's maturity condition is the longer one, and the two datasets legitimately end on different days. - **Record the window with the dataset.** A set built under a 24-hour rule and a set built under a 7-day rule are different targets and must not be concatenated. ## What an interviewer is listening for The short answer — "wait for the window to close" — is correct but incomplete. The strong answer names the direction of the error (the newest rows lose their positives, not the oldest), names the freshness price being paid, and states what the pipeline records so a downstream reader can tell a real negative from an unresolved one.

  • The business wants the model trained on data through this morning. What can you offer without corrupting the labels?
    Fresh **features** through this morning with **labels** only through the last closed window. Feature recency and label maturity are separate axes, and the pipeline can advance one without the other. If rows from the open window must be included, mark them immature and carry the elapsed fraction of the window per row, so the modelling side can down-weight or censor them rather than reading a zero as a real negative.
  • Conversions attribute over seven days while clicks attribute over one. What does that do to the two datasets?
    They end on different days. The conversion set's maturity condition is the longer window, so it lags the click set by six extra days and is always the smaller, older set. They cannot be assembled in one pass with one cutoff, and joining them into a single row set forces the shorter-window labels to inherit the longer lag. The window length belongs in each dataset's own description.
  • Why is a click that arrives late but happened inside the window still a positive?
    Because attribution is defined on event time, not on when the record reached the warehouse. A beacon delayed by a flaky network describes something that really happened inside the window. If the join filters on ingestion time, the same user behaviour becomes positive or negative depending on network conditions, which makes the label a property of the infrastructure rather than of the user.

saying these in an interview costs you the question

  • Labels every impression up to the current hour as clicked or not.
  • Says late clicks can just be dropped, so the labels are fine.
  • Uses ingestion time instead of the event's own timestamp in the window.
  • Lets two clicks on one impression produce two positive rows.
  • Thinks the bias falls on the oldest rows rather than the newest.
  • Concatenates datasets built under different attribution windows.