Count Users Whose First Daily Event Is App Launch

Given the PostgreSQL table: ```sql app_events ( event_id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, event_ts TIMESTAMP NOT NULL, event_name TEXT NOT NULL, os_type TEXT NOT NULL, device_type TEXT NOT NULL ) ``` Write one read-only `SELECT`/CTE query that returns, for each calendar date, `os_type`, and `device_type`, the number of distinct users whose first recorded event on that date is `app_open`. Return exactly: ```text event_date, os_type, device_type, user_count ``` ### Constraints and Clarifications - Derive `event_date` as `event_ts::date`. - “First” is per user and calendar date across every event name, not merely the first `app_open` row. - Break equal timestamps by smaller `event_id`. - Attribute a qualifying user to the OS and device on that first event. - Omit groups with zero qualifying users and sort by all three grouping columns ascending. ```hint Rank before filtering Identify one first row per user-date over the complete event stream, then retain rows whose event name is `app_open`. ``` ### Evaluation Focus - Correct partitioning and deterministic ordering. - Filtering after, rather than before, selecting the first daily event. - Distinct-user counting without double counting. - Valid PostgreSQL date handling and exact output columns. ### Extension How would the query change if “day” were defined in each user's local time zone?