Katkat
Back

Continue Entry Numbers for a Daily Incremental Batch

OtherAnalytics Engineer · Technical Screen
MediumAnalytics EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

Continue per-context entry numbering for a daily batch using stored maximums, ordered row numbers, and correct new-context handling.

The source relation is named entries for this exercise and has these columns:

ColumnMeaning
entry_uuidIdentifier of an entry.
context_uuidChat or conversation containing the entry.
visitor_idVisitor associated with the entry.
created_atEntry creation timestamp.
platformPlatform on which the entry was created.

Here, entries contains only the new entries in one daily processing batch. A second table, context_state, records the last processed entry number for each context:

ColumnMeaning
context_uuidContext identifier.
max_entry_numberLargest entry number already processed for that context.

This is the ordinary incremental case: the new entries follow the previously processed entries. Historical-gap recovery is a separate case. For a context with no previously processed entries, the previous maximum is zero.

Write one read-only PostgreSQL query that assigns the next entry numbers to the new batch. For each context, the numbering continues immediately after its recorded max_entry_number. Within the batch, order entries of that context by created_at ascending and then entry_uuid ascending. A new context starts at 1.

Return only the entries in the current batch with these columns in this order:

entry_uuid, context_uuid, visitor_id, created_at, platform, entry_number.

The final result row order is unrestricted. Calculate the new numbers without modifying entries or context_state.