Clean Quora Event Logs | Dataford Interview Questions

You are given messy event logs from Quora surfaces and asked to prepare a clean daily dataset for analysis. Write a PostgreSQL query that returns, for each event date and Quora surface, the number of valid events, the number of unique valid users, and the number of valid question views. Treat event names case-insensitively, trim extra whitespace from text fields, map question_view, Question View, and view_question to question_view, and map answer_click and Answer Click to answer_click. Exclude rows where the event timestamp is null, the user is inactive, the user does not exist, or the surface is blank/null after trimming.

Use the surface_map table to standardize surface names, keeping only rows that successfully map to a canonical Quora surface.

Schema

raw_events

Column	Type	Description
event_idPK	INT	Unique event identifier
user_id	INT	User who generated the event
event_name	VARCHAR(100)	Raw event name captured in logs
event_ts	TIMESTAMP	Timestamp when the event occurred
surface_raw	VARCHAR(100)	Raw product surface name from the client

users

Column	Type	Description
user_idPK	INT	Unique user identifier
is_active	BOOLEAN	Whether the user is considered active

surface_map

Column	Type	Description
surface_rawPK	VARCHAR(100)	Observed raw surface label
canonical_surface	VARCHAR(100)	Canonical Quora surface name

Your question is Clean Quora Event Logs. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem