Split comma tags into rows

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

tickets.tags holds comma-separated tags such as 'a, b,c'. Spaces around a tag are not part of the tag, and some values are blank, NULL or contain empty items ('x,,y'). Return one row per ticket and tag, with surrounding spaces removed. Skip blank and NULL tags. A ticket with no usable tag does not appear. Columns: ticket_id, tag. Order by ticket_id, then tag.

Requirements

• One output row per usable tag.
• Order by ticket_id, tag.

Constraints

• Tags are separated by a comma only.
• A tag is never split further.
• tickets.tags: comma separated, may have spaces, empty items or be NULL.

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