Task
You are given raw event logs from Motive products and asked to reshape them into a user-day product activity table for analysis. Write a PostgreSQL query that returns one row per user per event date for valid product events only, with the users company name, a cleaned product surface, counts of total valid events and distinct sessions, and a flag showing whether the user triggered a conversion event that day.
Treat view, click, and submit as valid events. Exclude internal users, events with missing user_id, and events whose event_name is null. Map driver_app_home and driver_app_settings to Driver App, fleet_dashboard_overview and fleet_dashboard_reports to Fleet Dashboard, and all other non-null surfaces to Other. A conversion is any valid submit event.
Schema
users
| column | type | description |
|---|
| user_id | INT | User identifier |
| company_id | INT | Company identifier |
| user_name | VARCHAR(100) | User name |
| is_internal | BOOLEAN | Whether the user is an internal account |
| | |
companies
| column | type | description |
|---|
| company_id | INT | Company identifier |
| company_name | VARCHAR(100) | Company name |
| | |
product_events
| column | type | description |
|---|
| event_id | INT | Event identifier |
| user_id | INT | User who triggered the event |
| event_time | TIMESTAMP | Event timestamp |
| session_id | VARCHAR(50) | Session identifier |
| event_name | VARCHAR(50) | Event type |
| product_surface | VARCHAR(100) | Raw product surface |
Sample data
product_events
| event_id | user_id | event_time | session_id | event_name | product_surface |
|---|
| 101 | 1 | 2024-04-01 09:00:00 | s1 | view | driver_app_home |
| 102 | 1 | 2024-04-01 09:05:00 | s1 | click | driver_app_home |
| 103 | 1 | 2024-04-01 09:07:00 | s1 | submit | driver_app_home |
| 104 | 2 | 2024-04-01 10:00:00 | s2 | view | fleet_dashboard_overview |
| 105 | 2 | 2024-04-01 10:10:00 | s2 | heartbeat | fleet_dashboard_overview |
| 106 | 3 | 2024-04-01 11:00:00 | s3 | view | driver_app_settings |
Expected output
| event_date | user_id | company_name | cleaned_surface | valid_event_count | distinct_session_count | converted_that_day |
|---|
| 2024-04-01 | 1 | Apex Logistics | Driver App | 3 | 1 | 1 |
| 2024-04-01 | 2 | Beacon Freight | Fleet Dashboard | 1 | 1 | 0 |
| 2024-04-02 | 2 | Beacon Freight | Fleet Dashboard | 2 | 1 | 1 |
