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.
raw_events| column | type | description |
|---|---|---|
| event_id | INT | Event identifier |
| user_id | INT | User who generated the event |
| event_name | VARCHAR(100) | Raw event name |
| event_ts | TIMESTAMP | Event timestamp |
| surface_raw | VARCHAR(100) | Raw surface name |
users| column | type | description |
|---|---|---|
| user_id | INT | User identifier |
| is_active | BOOLEAN | Whether the user is active |
surface_map| column | type | description |
|---|---|---|
| surface_raw | VARCHAR(100) | Raw surface label |
| canonical_surface | VARCHAR(100) | Standardized Quora surface |
raw_events| event_id | user_id | event_name | event_ts | surface_raw |
|---|---|---|---|---|
| 101 | 1 | question_view | 2024-04-01 09:00:00 | Home Feed |
| 102 | 1 | Answer Click | 2024-04-01 09:02:00 | home feed |
| 103 | 2 | view_question | 2024-04-01 11:00:00 | Question Page |
| 104 | 3 | question_view | 2024-04-01 12:00:00 | Spaces |
| event_date | canonical_surface | valid_events | unique_valid_users | valid_question_views |
|---|---|---|---|---|
| 2024-04-01 | Home Feed | 2 | 1 | 1 |
| 2024-04-01 | Question Page | 1 | 1 | 1 |
| 2024-04-02 | Home Feed | 2 | 2 | 1 |
| 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 |
| Column | Type | Description |
|---|---|---|
| user_idPK | INT | Unique user identifier |
| is_active | BOOLEAN | Whether the user is considered active |
| Column | Type | Description |
|---|---|---|
| surface_rawPK | VARCHAR(100) | Observed raw surface label |
| canonical_surface | VARCHAR(100) | Canonical Quora surface name |
| user_id | is_active |
|---|---|
| 1 | true |
| 2 | true |
| 3 | false |
| 4 | false |
| 5 | true |
| 6 | true |
| 7 | true |
| 8 | true |
| 9 | true |
| 10 | false |
| 1 | true |
| 2 | false |
| 3 | true |
| 4 | false |
| 5 | false |
| 6 | true |
| 7 | true |
| 8 | true |
| 9 | false |
| 10 | true |
| 11 | true |
| 12 | true |
| 13 | true |
| 14 | true |
| 15 | false |
| 16 | false |
| 17 | true |
| 18 | false |
| 19 | true |
| event_id | user_id | event_name | event_ts | surface_raw |
|---|---|---|---|---|
| 101 | 1 | question_view | 2024-04-01 09:00:00 | Home Feed |
| 102 | 1 | Answer Click | 2024-04-01 09:02:00 | home feed |
| 103 | 2 | view_question | 2024-04-01 11:00:00 | Question Page |
| 104 | 3 | question_view | 2024-04-01 12:00:00 | Spaces |
| 105 | 4 | question_view | 2024-04-01 12:30:00 | Home Feed |
| 106 | 2 | answer_click | 2024-04-02 08:00:00 | Home Feed |
| 107 | 5 | Question View | 2024-04-02 08:05:00 | Question Page |
| 108 | 6 | question_view | Home Feed | |
| 109 | 7 | question_view | 2024-04-02 10:00:00 | |
| 110 | 8 | question_view | 2024-04-02 10:05:00 | Unknown Surface |
| 111 | 9 | question_view | 2024-04-02 11:00:00 | Home Feed |
| 112 | 2 | upvote | 2024-04-02 11:30:00 | Question Page |
| 1 | 35 | open_notification | 2024-03-31 06:39:46 | Writer Dashboard |
| 2 | 89 | question_view | 2024-03-30 17:12:32 | Writer Dashboard |
| 3 | 13 | upvote | 2024-04-05 07:29:25 | |
| 4 | 70 | follow_topic | 2024-04-03 13:54:29 | null |
| 5 | 5 | Bookmark | 2024-04-03 20:31:15 | Home Feed |
| 6 | 11 | Search Query | 2024-03-29 17:28:09 | home feed |
| 7 | 29 | Open Notification | 2024-03-30 13:17:13 | Question Page |
| 8 | 8 | profile_view | 2024-04-04 10:07:32 | spaces |
| 9 | 45 | profile_view | 2024-03-30 12:16:39 | Home Feed |
| 10 | 27 | Share | 2024-03-29 16:00:55 | Login Wall |
| 11 | 73 | question_view | 2024-04-01 20:40:00 | Topic Hub |
| 12 | 91 | Profile View | 2024-04-04 14:35:33 | Unknown Surface |
| 13 | 5 | comment_submit | 2024-04-01 20:26:35 | Desktop Feed |
| 14 | 7 | answer_click | 2024-03-30 08:18:55 | Notifications |
| 15 | 58 | Open Notification | 2024-04-03 19:13:14 | Digest |
| 16 | 42 | upvote | 2024-04-03 12:30:23 | Ad Unit |
| surface_raw | canonical_surface |
|---|---|
| Home Feed | Home Feed |
| home feed | Home Feed |
| Question Page | Question Page |
| question page | Question Page |
| Email Digest | Email Digest |
| Notifications | Notifications |
| Spaces | Spaces |
| spaces | Spaces |
| Home Feed_1 | Topics |
| Spaces_2 | Digest |
| spaces_3 | Login Wall |
| Question Page_4 | Desktop Feed |
| Spaces_5 | Desktop Feed |
| home feed_6 | Email Digest |
| Email Digest_7 | Desktop Feed |
| Question Page_8 | Mobile Feed |
| Question Page_9 | Questions |
| Question Page_10 | Desktop Feed |
| home feed_11 | Recommendations |
| Notifications_12 | Writer Dashboard |
| Spaces_13 | Notifications |
| question page_14 | Profile |
| home feed_15 | Answer Page |
| Email Digest_16 | Onboarding |
| home feed_17 | Writer Dashboard |
| question page_18 | Questions |
| Question Page_19 | Email Digest |
| home feed_20 | Recommendations |
| Email Digest_21 | Search Results |
| event_date | canonical_surface | valid_events | unique_valid_users | valid_question_views |
|---|---|---|---|---|
| 2024-03-29 | Home Feed | 2 | 1 | 0 |
| 2024-03-30 | Notifications | 1 | 1 | 0 |
| 2024-04-01 | Home Feed | 4 | 1 | 2 |
| 2024-04-01 | Question Page | 2 | 1 | 2 |
| 2024-04-02 | Home Feed | 4 | 2 | 2 |
| 2024-04-02 | Question Page | 4 | 2 | 2 |
| 2024-04-03 | Home Feed | 2 | 1 | 0 |
| 2024-04-04 | Spaces | 2 | 1 | 0 |
