Split comma tags into rows

SQL data engineering interview problem. Difficulty: intermediate. Pattern: String Functions. About 12 minutes. Part of the Pro drill bank.

Support tickets store their tags in one text column, separated by commas, like 'a, b,c'. To count or filter by tag, you need 1 row for each tag. From tickets, return 1 row for each ticket and tag, with the spaces around each tag removed. Skip blank tags and NULL values. A ticket with no usable tag must not appear. Return ticket_id and tag, sorted by ticket_id and then tag.

Requirements

• Split tags at each comma into separate values.
• Trim the spaces around each tag.
• Skip tags that are empty after trimming.
• A NULL or blank tags value gives no rows.
• Sort by ticket_id, then tag.

Constraints

• The table is tickets with the columns ticket_id and tags.
• Tags are separated only by commas. A tag is never split further.
• Use string_split with UNNEST (or an equivalent in your database), then TRIM.

Examples

Input: tickets ticket_id | tags 1 | a, b,c 2 | 3 | NULL 4 | billing ,refund 5 | x,,y Output: ticket_id | tag 1 | a 1 | b 1 | c 4 | billing 4 | refund 5 | x 5 | y Why this passes: Ticket 1 gives a, b, c. Tickets 2 (spaces only) and 3 (NULL) give nothing. Ticket 4 gives billing and refund without spaces. Ticket 5 gives x and y; the empty item is skipped.

Input: tickets ticket_id | tags 1 | red,blue 2 | green Output: ticket_id | tag 1 | blue 1 | red 2 | green Why this passes: Simple list with no spaces.

Topics: lakebench, sql, string_split, unnest, trim.

More SQL interview questions · All interview problems · Learn data engineering