Katkat
Back

Number Entries Within Each Conversation

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

Last updated

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:

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.

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.