I recently had an Analytics Engineer technical screen at a company and encountered a SQL + data pipeline question. Here's the approach.
Table structure: entry_uuid | context_uuid | visitor_id | created_at | platform.
Users can ask consecutive questions in the same chat/context. Each entry needs an entry_number indicating which entry it is within that context.
- How do you calculate it?
ROW_NUMBER() OVER (PARTITION BY context_uuid ORDER BY created_at, entry_uuid)
The interviewer followed up: If this runs incrementally each day, how do you avoid scanning all historical data?
Maintain a context_state table:
context_uuid | max_entry_number.
Process only new data each day: prev_max + ROW_NUMBER() within that day's data.
Update max_entry_number after the run.
- Follow-up: If several days of upstream data are missing, but the later data has already been processed, how do you backfill?
For example, data from T-30 to T-25 is missing, while T-24 to T-1 has already been processed.
First use the state before the gap, the maximum as of T-31, to number the gap data: max_entry_number_as_of_T-31 + ROW_NUMBER() within the gap.
Then shift the entry_number of the already processed later data forward by the number of gap entries for each context.
Example: T-28 → 1, T-27 → 2, T-24 → 3, T-10 → 4, T-1 → 5.
Remember: Don't use today's max_entry_number to number a historical gap.
This offset approach assumes that the missing interval is one continuous block entirely before the already processed later data. If missing data can be interleaved, you need to recalculate the window for the affected contexts.
Discussion
Loading comments…