Number Entries Within Each Conversation
Role: Analytics Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
## Number Entries Within Each Conversation
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.
Overview: Assign one-based entry numbers per conversation using timestamp and identifier ordering in a PostgreSQL window-function query.
Read the full Analytics Engineer interview experience this question came from
## Number Entries Within Each Conversation
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.
Tables
entries(entry_uuid TEXT, context_uuid TEXT, visitor_id INTEGER, created_at TIMESTAMP, platform TEXT)