Coding questions · Meta
Solve Python and SQL data tasks
This question evaluates data manipulation and engineering competencies by testing Python proficiency with nested data structure handling, complexity analysis and test-case design, alongside SQL proficiency in UTC-aware aggregation and per-user deduplication to compute daily active users; it is categorized as Data Manipulation (SQL/Python) within the data engineering domain. Such problems are commonly asked to assess practical implementation ability and robustness under edge cases, as well as conceptual understanding of time/space complexity and correct date-time handling, reflecting a primarily practical application with elements of conceptual analysis.
Complete both tasks:
- Python: Implement a function flatten(nested) that takes a list whose elements are integers or arbitrarily nested lists of integers and returns a single flat list of integers in left-to-right order. Avoid recursion if nesting depth may be large. State time and space complexity and include simple tests covering empty input, deep nesting, and invalid element types.
- SQL: Given events(user_id INT, event_time TIMESTAMP, event_type STRING), write a query that returns, for each UTC calendar date, the count of distinct active users (DAU). Deduplicate multiple events per user per day and ensure event_time is interpreted in UTC. Output columns: event_date, dau.
Daily Active Users (DAU) by UTC Calendar Date
You are given an events table that tracks user activity. Each row represents a single event generated by a user at a specific time in UTC.
Table: events(user_id INT, event_time TIMESTAMP, event_type VARCHAR)
Write a SQL query that returns, for each UTC calendar date present in the data, the count of distinct active users (DAU). A user is considered active on a given date if they have at least one event on that date. Deduplicate multiple events per user per day.
Output columns:
- event_date (DATE): UTC calendar date derived from event_time.
- dau (INT): number of distinct users active on that date.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_time | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
Sample data:
| event_time | event_type | user_id |
|---|---|---|
| 2025-05-31 10:15:00 | login | 1 |
| 2025-05-31 12:00:00 | click | 1 |
| 2025-05-31 23:59:59 | purchase | 2 |
| 2025-06-01 00:00:01 | login | 1 |
| 2025-06-01 09:30:00 | click | 3 |
| 2025-06-01 09:45:00 | logout | 3 |
| 2025-06-02 15:00:00 | login | 2 |
| 2025-06-02 16:00:00 | login | 4 |
| 2025-06-02 17:00:00 | click | 4 |
Expected output
One row per UTC calendar date present in events, with the count of distinct users who had at least one event on that date.
| dau | event_date |
|---|---|
| 2 | 2025-05-31 |
| 2 | 2025-06-01 |
| 2 | 2025-06-02 |
Hints
- Convert the event_time timestamp to a DATE to group events by UTC calendar day.
- Use COUNT(DISTINCT user_id) so that multiple events from the same user on the same day are only counted once.