Solve Python and SQL data tasks
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Complete both tasks:
1) 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.
2) 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.
Overview: 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.
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.
Tables
events(user_id INT, event_time TIMESTAMP, event_type VARCHAR(50))
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.