Welcome to the SQL screen.
The question is on your right: Unify CRM and Google Analytics View. Read through the requirements and the four tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
You’re a data engineer at a fast-growing e-commerce retailer (~3M monthly active users) that sells direct-to-consumer. Marketing spends heavily on paid search and email, but the company can’t reliably connect anonymous web behavior (Google Analytics-style events) to known customers (CRM) once they purchase or log in. This leads to mis-attributed CAC/LTV, broken retargeting audiences, and inconsistent executive reporting.
Your warehouse receives:
ga_client_id (cookie/device identifier).ga_client_id to customer_id when a user logs in or completes checkout.Because users can browse anonymously on multiple devices, a single customer may map to multiple ga_client_ids over time. You want a single customer view that rolls up web behavior and revenue.
Write a SQL query that produces a unified customer-level dataset for January 2025.
customer_id.customer_id, emailfirst_touch_channel: the channel of the customer’s earliest known web event (across all mapped GA client IDs)sessions_jan_2025: distinct session count in Jan 2025pageviews_jan_2025: total page_view events in Jan 2025orders_jan_2025: number of orders placed in Jan 2025revenue_jan_2025: sum of order totals in Jan 2025event_ts; if ties, choose lexicographically smallest channel.| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | CRM customer primary key |
| VARCHAR(255) | Customer email address | |
| created_at | TIMESTAMP | Timestamp when the customer record was created |
| Column | Type | Description |
|---|---|---|
| order_idPK | BIGINT | Order primary key |
| customer_id | INT | Customer placing the order (FK to crm_customers) |
| order_ts | TIMESTAMP | Order timestamp |
| order_total | DECIMAL(10,2) | Total charged amount for the order |
| Column | Type | Description |
|---|---|---|
| event_idPK | BIGINT | Event primary key |
| ga_client_id | VARCHAR(64) | GA client/cookie identifier |
| session_id | VARCHAR(64) | Session identifier |
| event_ts | TIMESTAMP | Event timestamp |
| event_name | VARCHAR(50) | Event type (e.g., page_view) |
| channel | VARCHAR(50) | Marketing channel attribution for the event |
| Column | Type | Description |
|---|---|---|
| ga_client_idPK | VARCHAR(64) | GA client identifier (one side of the crosswalk) |
| customer_id | INT | Mapped CRM customer_id |
| first_seen_ts | TIMESTAMP | First time this mapping was observed |