Extract fields from JSON events

SQL data engineering interview problem. Difficulty: intermediate. Pattern: JSON. About 16 minutes. Part of the Pro drill bank.

Event data often arrives as JSON text in a single column. The raw_events table has a payload column with text like {"user_id":"u1","event_type":"click","device":{"os":"ios"}}. Real data is messy: some payloads are broken JSON, some are NULL, some have no device part, and one has "os": null. Skip every row whose payload is not valid JSON. For the rest, group by the operating system (device.os) and return the os (use the text unknown when the event has no OS), the number of events as event_count, and the number of different users as distinct_users. Order by event_count from high to low, then by os.

Requirements

• Ignore rows where payload is NULL or is not valid JSON, without raising an error.
• Read the OS from the path $.device.os and the user from $.user_id.
• Show unknown when the OS is missing or null.
• Count all events and the distinct users for each OS.
• Order by event_count descending, then os.

Constraints

• payload is JSON text, and it may be malformed or NULL.
• Return os, event_count, distinct_users.
• Check validity with json_valid(payload) before you read values from it.

Examples

Input: raw_events event_id | payload 1 | {"user_id":"u1","event_type":"click","device":{"os":"ios"}} 2 | {"user_id":"u2","event_type":"view","device":{"os":"android"}} 3 | {"user_id":"u1","event_type":"view","device":{"os":"ios"}} 4 | {bad json 5 | {"user_id":"u3","event_type":"click"} 6 | {"user_id":"u4","event_type":"click","device":{"os":"android"}} 7 | NULL 8 | {"user_id":"u2","event_type":"click","device":{"os":"ios"}} 9 | not json at all 10 | {"user_id":"u5","event_type":"view","device":{"os":null}} Output: os | event_count | distinct_users ios | 3 | 2 android | 2 | 2 unknown | 2 | 2 Why this passes: 8 rows are valid JSON. ios has 3 events from 2 users, android 2 events from 2 users, and 2 events (no device, null os) are unknown. Rows 4, 7 and 9 are skipped.

Input: raw_events event_id | payload 1 | {"user_id":"u1","device":{"os":"ios"}} 2 | {"user_id":"u1","device":{"os":"ios"}} Output: os | event_count | distinct_users ios | 2 | 1 Why this passes: Two valid events from the same os and user.

Topics: lakebench, sql, json, json_extract, malformed.

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