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.
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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| company_id | INT | Company associated with the user |
| user_name | VARCHAR(100) | User display name |
| is_internal | BOOLEAN | Whether the account is internal |
| Column | Type | Description |
|---|---|---|
| company_idPK | INT | Unique company identifier |
| company_name | VARCHAR(100) | Company name |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| user_id | INT | User who triggered the event |
| event_time | TIMESTAMP | Timestamp of the event |
| session_id | VARCHAR(50) | Session identifier |
| event_name | VARCHAR(50) | Raw event type |
| product_surface | VARCHAR(100) | Raw product surface name |
| user_id | company_id | user_name | is_internal |
|---|---|---|---|
| 4 | 103 | Nina Patel | false |
| 1 | 101 | Ava Chen | false |
| 6 | Maya Singh | false | |
| 2 | 102 | Leo Martinez | false |
| 8 | 104 | Owen Brooks | false |
| 3 | 101 | Iris Kim | true |
| 5 | 103 | Sam Carter | false |
| 7 | 105 | Ethan Cole | true |
| 1 | 29 | James Long | false |
| 2 | 52 | Zoe Morris | true |
| 3 | 35 | Ava Chen | true |
| 4 | 87 | Leo Martinez | true |
| 5 | 53 | Benjamin Ross | true |
| 6 | 22 | Nina Patel | true |
| 7 | 75 | Elijah Price | true |
| 8 | 40 | Noah Bennett | false |
| 9 | 7 | Grace Powell | true |
| 10 | 67 | Ethan Cole | false |
| 11 | 58 | Mason Gray | true |
| 12 | 99 | Henry Flores | false |
| 13 | 83 | Lily Sanders | null |
| 14 | 4 | Harper Diaz | true |
| 15 | 91 | Iris Kim | null |
| 16 | 79 | James Long | true |
| 17 | 11 | Mila Hughes | null |
| 18 | 24 | Grace Powell | null |
| company_id | company_name |
|---|---|
| 103 | Crest Haul |
| 101 | Apex Logistics |
| 105 | Drift Transport |
| 102 | Beacon Freight |
| 106 | Echo Carriers |
| 104 | Northline Movers |
| 107 | Summit Fleet |
| 108 | Pioneer Dispatch |
| 1 | Granite Transport |
| 2 | Atlas Trucking |
| 3 | Beacon Freight |
| 4 | Titan Fleet |
| 5 | Frontier Logistics |
| 6 | Drift Transport |
| 7 | Evergreen Fleet |
| 8 | Liberty Haul |
| 9 | Silver Roadways |
| 10 | Beacon Freight |
| 11 | Redwood Logistics |
| 12 | Redwood Logistics |
| 13 | Redwood Logistics |
| 14 | Jetstream Freight |
| 15 | Blue Ridge Freight |
| 16 | Echo Carriers |
| 17 | Titan Fleet |
| event_id | user_id | event_time | session_id | event_name | product_surface |
|---|---|---|---|---|---|
| 112 | 2 | 2024-04-02 08:10:00 | s4 | submit | fleet_dashboard_reports |
| 101 | 1 | 2024-04-01 09:00:00 | s1 | view | driver_app_home |
| 118 | 8 | 2024-04-02 11:00:00 | s8 | click | |
| 104 | 2 | 2024-04-01 10:00:00 | s2 | view | fleet_dashboard_overview |
| 109 | 4 | 2024-04-01 12:00:00 | s5 | submit | safety_hub |
| 115 | 5 | 2024-04-02 09:00:00 | s6 | driver_app_home | |
| 103 | 1 | 2024-04-01 09:07:00 | s1 | submit | driver_app_home |
| 120 | 99 | 2024-04-02 13:00:00 | s10 | view | fleet_dashboard_overview |
| 107 | 3 | 2024-04-01 11:05:00 | s3 | click | driver_app_settings |
| 111 | 2 | 2024-04-02 08:00:00 | s4 | view | fleet_dashboard_reports |
| 116 | 2024-04-02 09:30:00 | s7 | view | driver_app_home | |
| 119 | 1 | 2024-04-02 12:00:00 | s9 | heartbeat | driver_app_home |
| 102 | 1 | 2024-04-01 09:05:00 | s1 | click | driver_app_home |
| 106 | 3 | 2024-04-01 11:00:00 | s3 | view | driver_app_settings |
| 108 | 4 | 2024-04-01 12:05:00 | s5 | view | safety_hub |
| 110 | 4 | 2024-04-01 12:10:00 | s5 | click | unknown_surface |
| 113 | 2 | 2024-04-02 08:15:00 | s4 | heartbeat | fleet_dashboard_reports |
| 114 | 5 | 2024-04-02 09:00:00 | s6 | view | driver_app_home |
| 117 | 6 | 2024-04-02 10:00:00 | view | fleet_dashboard_overview | |
| 105 | 2 | 2024-04-01 10:10:00 | s2 | heartbeat | fleet_dashboard_overview |
| 1 | 13 | 2024-04-04 23:25:24 | drv_a1 | approve | driver_coaching |
| 2 | 79 | 2024-04-03 01:35:58 | s5 | submit | null |
| 3 | 2 | 2024-03-30 01:04:26 | s8 | save | null |
| 4 | 70 | 2024-03-31 11:03:30 | sess_106 | null | null |
| 5 | 6 | 2024-03-30 13:08:38 | flt_b2 | export_report | driver_app_documents |
| 6 | 49 | 2024-03-31 10:45:39 | s10 | submit | billing_portal |
| 7 | 36 | 2024-03-31 04:23:25 | ops_c2 | create_report | unknown_surface |
| 8 | 37 | 2024-04-01 04:00:05 | s10 | search | compliance_center |
| 9 | 99 | 2024-04-03 20:54:43 | sess_108 | enable_alert | billing_portal |
| 10 | 43 | 2024-04-01 05:20:30 | s10 | refresh | route_planner |
| event_date | user_id | company_name | cleaned_surface | valid_event_count | distinct_session_count | converted_that_day |
|---|---|---|---|---|---|---|
| 2024-03-31 | 49 | null | Other | 1 | 1 | 1 |
| 2024-04-01 | 1 | Apex Logistics | Driver App | 3 | 1 | 1 |
| 2024-04-01 | 2 | Beacon Freight | Fleet Dashboard | 1 | 1 | 0 |
| 2024-04-01 | 4 | Crest Haul | Other | 3 | 1 | 1 |
| 2024-04-02 | 2 | Beacon Freight | Fleet Dashboard | 2 | 1 | 1 |
| 2024-04-02 | 5 | Crest Haul | Driver App | 1 | 1 | 0 |
| 2024-04-02 | 6 | null | Fleet Dashboard | 1 | 0 | 0 |
| 2024-04-02 | 8 | Northline Movers | null | 1 | 1 | 0 |
| 2024-04-02 | 99 | null | Fleet Dashboard | 1 | 1 | 0 |
| 2024-04-03 | 79 | null | null | 1 | 1 | 1 |
