Coding questions
Continue Entry Numbers for a Daily Incremental Batch
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:
| Column | Meaning |
|---|---|
entry_uuid | Identifier of an entry. |
context_uuid | Chat or conversation containing the entry. |
visitor_id | Visitor associated with the entry. |
created_at | Entry creation timestamp. |
platform | Platform 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:
| Column | Meaning |
|---|---|
context_uuid | Context identifier. |
max_entry_number | Largest 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.
Continue Entry Numbers for a Daily Incremental Batch
Table entries
| column | type | constraints |
|---|---|---|
| entry_uuid | TEXT | |
| context_uuid | TEXT | |
| visitor_id | INTEGER | |
| created_at | TIMESTAMP | |
| platform | TEXT |
Sample data:
| context_uuid | created_at | entry_uuid | platform | visitor_id |
|---|---|---|---|---|
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 11:00:00 | 00000000-0000-4000-8000-000000000003 | web | 101 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 13:00:00 | 00000000-0000-4000-8000-000000000001 | ios | 102 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 09:00:00 | 00000000-0000-4000-8000-000000000004 | web | 101 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 11:00:00 | 00000000-0000-4000-8000-000000000002 | android | 103 |
| bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 2026-09-28 12:00:00 | 00000000-0000-4000-8000-000000000006 | web | 101 |
| bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 2026-09-28 10:00:00 | 00000000-0000-4000-8000-000000000005 | web | 101 |
| cccccccc-cccc-4ccc-8ccc-cccccccccccc | 2026-09-28 08:00:00 | 00000000-0000-4000-8000-000000000008 | android | 201 |
| cccccccc-cccc-4ccc-8ccc-cccccccccccc | 2026-09-28 08:00:00 | 00000000-0000-4000-8000-000000000007 | ios | 202 |
| dddddddd-dddd-4ddd-8ddd-dddddddddddd | 2026-09-28 10:00:00 | 00000000-0000-4000-8000-000000000010 | NULL | 301 |
| dddddddd-dddd-4ddd-8ddd-dddddddddddd | 2026-09-28 07:00:00 | 00000000-0000-4000-8000-000000000009 | web | 301 |
Table context_state
| column | type | constraints |
|---|---|---|
| context_uuid | TEXT | |
| max_entry_number | BIGINT |
Sample data:
| context_uuid | max_entry_number |
|---|---|
| bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 12 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 5 |
| dddddddd-dddd-4ddd-8ddd-dddddddddddd | 0 |
| ffffffff-ffff-4fff-8fff-ffffffffffff | 99 |
Expected output
| entry_uuid | context_uuid | visitor_id | created_at | platform | entry_number |
|---|---|---|---|---|---|
| 00000000-0000-4000-8000-000000000004 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 101 | 2026-09-28 09:00:00 | web | 6 |
| 00000000-0000-4000-8000-000000000002 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 103 | 2026-09-28 11:00:00 | android | 7 |
| 00000000-0000-4000-8000-000000000003 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 101 | 2026-09-28 11:00:00 | web | 8 |
| 00000000-0000-4000-8000-000000000001 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 102 | 2026-09-28 13:00:00 | ios | 9 |
| 00000000-0000-4000-8000-000000000005 | bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 101 | 2026-09-28 10:00:00 | web | 13 |
| 00000000-0000-4000-8000-000000000006 | bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 101 | 2026-09-28 12:00:00 | web | 14 |
| 00000000-0000-4000-8000-000000000007 | cccccccc-cccc-4ccc-8ccc-cccccccccccc | 202 | 2026-09-28 08:00:00 | ios | 1 |
| 00000000-0000-4000-8000-000000000008 | cccccccc-cccc-4ccc-8ccc-cccccccccccc | 201 | 2026-09-28 08:00:00 | android | 2 |
| 00000000-0000-4000-8000-000000000009 | dddddddd-dddd-4ddd-8ddd-dddddddddddd | 301 | 2026-09-28 07:00:00 | web | 1 |
| 00000000-0000-4000-8000-000000000010 | dddddddd-dddd-4ddd-8ddd-dddddddddddd | 301 | 2026-09-28 10:00:00 | NULL | 2 |