Coding questions
Number Entries Within Each Conversation
Assign one-based entry numbers per conversation using timestamp and identifier ordering in a PostgreSQL window-function query.
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. |
A conversation can contain several entries. Write one read-only PostgreSQL query that assigns each entry its position within its own context_uuid.
Start numbering at 1 for each context. Within a context, order entries by created_at ascending and then by entry_uuid ascending. The entry identifier resolves a tie in creation timestamps.
Return the original columns followed by the new entry_number column, in this order:
entry_uuid, context_uuid, visitor_id, created_at, platform, entry_number.
The required order determines the numbering within each context. The final result rows may be returned in any order.
Number Entries Within Each Conversation
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-000000000501 | web | 11 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 13:00:00 | 00000000-0000-4000-8000-000000000101 | ios | 12 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 09:00:00 | 00000000-0000-4000-8000-000000000902 | web | 11 |
| aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 2026-09-28 11:00:00 | 00000000-0000-4000-8000-000000000301 | android | 13 |
| bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 2026-09-28 12:00:00 | 00000000-0000-4000-8000-000000000055 | web | 11 |
| bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 2026-09-28 00:00:00 | 00000000-0000-4000-8000-000000000999 | web | 11 |
| bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 2026-09-27 23:59:59 | 00000000-0000-4000-8000-000000000202 | ios | 14 |
| cccccccc-cccc-4ccc-8ccc-cccccccccccc | 2025-12-31 23:59:59 | 00000000-0000-4000-8000-000000000007 | web | 11 |
| dddddddd-dddd-4ddd-8ddd-dddddddddddd | 2026-09-28 10:00:00 | 00000000-0000-4000-8000-000000000801 | web | NULL |
| dddddddd-dddd-4ddd-8ddd-dddddddddddd | 2026-09-28 10:00:00 | 00000000-0000-4000-8000-000000000800 | NULL | 15 |
Expected output
| entry_uuid | context_uuid | visitor_id | created_at | platform | entry_number |
|---|---|---|---|---|---|
| 00000000-0000-4000-8000-000000000501 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 11 | 2026-09-28 11:00:00 | web | 3 |
| 00000000-0000-4000-8000-000000000101 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 12 | 2026-09-28 13:00:00 | ios | 4 |
| 00000000-0000-4000-8000-000000000902 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 11 | 2026-09-28 09:00:00 | web | 1 |
| 00000000-0000-4000-8000-000000000301 | aaaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa | 13 | 2026-09-28 11:00:00 | android | 2 |
| 00000000-0000-4000-8000-000000000055 | bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 11 | 2026-09-28 12:00:00 | web | 3 |
| 00000000-0000-4000-8000-000000000999 | bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 11 | 2026-09-28 00:00:00 | web | 2 |
| 00000000-0000-4000-8000-000000000202 | bbbbbbbb-bbbb-4bbb-8bbb-bbbbbbbbbbbb | 14 | 2026-09-27 23:59:59 | ios | 1 |
| 00000000-0000-4000-8000-000000000007 | cccccccc-cccc-4ccc-8ccc-cccccccccccc | 11 | 2025-12-31 23:59:59 | web | 1 |
| 00000000-0000-4000-8000-000000000801 | dddddddd-dddd-4ddd-8ddd-dddddddddddd | NULL | 2026-09-28 10:00:00 | web | 2 |
| 00000000-0000-4000-8000-000000000800 | dddddddd-dddd-4ddd-8ddd-dddddddddddd | 15 | 2026-09-28 10:00:00 | NULL | 1 |