Extract fields from JSON events

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

raw_events.payload holds JSON text such as {"user_id":"u1","event_type":"click","device":{"os":"ios"}}. Some rows are malformed or NULL, some have no device, and one has "os": null. Ignoring rows whose payload is not valid JSON, return per operating system (device.os): os: the value, or the text 'unknown' when the event has no OS event_count: number of events distinct_users: distinct user_id values Columns: os, event_count, distinct_users. Order by event_count descending, then os.

Requirements

• Rows that are not valid JSON are excluded from every count.

Constraints

• raw_events.payload: JSON text, may be malformed or NULL.

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: Eight 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