Coding questions · Adobe
Solve SQL and Python Data Manipulation Tasks
This question evaluates proficiency in SQL event-log aggregation and basic Python data-structure transformations, assessing competencies such as counting distinct users, identifying frequent event types, and performing dictionary/list/string manipulations.
UserEvents
+----+---------+------------+------------+
| id | user_id | event_type | event_date |
+----+---------+------------+------------+
| 1 | 101 | install | 2023-05-01 |
| 2 | 101 | open_app | 2023-05-01 |
| 3 | 102 | install | 2023-05-01 |
| 4 | 103 | install | 2023-05-02 |
| 5 | 103 | share | 2023-05-02 |
+----+---------+------------+------------+
Scenario
Technical screen requiring simple SQL queries and basic Python data-structure manipulations on app event data.
Question
SQL:
- Count distinct daily active users.
- Return the three most common event_type values yesterday.
- List users whose first recorded event was 'install'. Python: a) Given a dict, produce an inverted dict (values→keys). b) Remove duplicates from a list in one line. c) Reverse a string in one line.
Hints
Use GROUP BY with DISTINCT, ORDER BY … DESC LIMIT, window/aggregation for first_event; in Python leverage dict comprehensions, set(), slicing[::-1].
Daily active users and event-type analysis
Given the UserEvents table, return one combined result set for three event analyses.
Use columns result_set, event_date, dau, event_type, events, and user_id:
- result_set = 'daily_active_users': count distinct active users by event_date.
- result_set = 'top_event_types_2025_05_31': return the three most common event_type values on DATE '2025-05-31', ordered by event count descending and event_type ascending.
- result_set = 'first_event_install_users': list users whose first recorded event was 'install', using event_date then id as the tie-break order.
Fields that do not apply to a row should be NULL.
Table UserEvents
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY |
| user_id | INTEGER | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| event_date | DATE | NOT NULL |
Sample data:
| event_date | event_type | id | user_id |
|---|---|---|---|
| 2023-05-01 | install | 1 | 101 |
| 2023-05-01 | open_app | 2 | 101 |
| 2023-05-01 | install | 3 | 102 |
| 2023-05-02 | install | 4 | 103 |
| 2023-05-02 | share | 5 | 103 |
| 2025-05-30 | install | 6 | 201 |
| 2025-05-31 | open_app | 7 | 201 |
| 2025-05-31 | install | 8 | 202 |
| 2025-05-31 | share | 9 | 203 |
| 2025-06-01 | open_app | 10 | 202 |
Expected output
One combined result set: DAU by date, top event types for 2025-05-31, and users whose first recorded event was install.
| dau | event_date | event_type | events | result_set | user_id |
|---|---|---|---|---|---|
| 2 | 2023-05-01 | NULL | NULL | daily_active_users | NULL |
| 1 | 2023-05-02 | NULL | NULL | daily_active_users | NULL |
| 1 | 2025-05-30 | NULL | NULL | daily_active_users | NULL |
| 3 | 2025-05-31 | NULL | NULL | daily_active_users | NULL |
| 1 | 2025-06-01 | NULL | NULL | daily_active_users | NULL |
| NULL | NULL | install | 1 | top_event_types_2025_05_31 | NULL |
| NULL | NULL | open_app | 1 | top_event_types_2025_05_31 | NULL |
| NULL | NULL | share | 1 | top_event_types_2025_05_31 | NULL |
| NULL | NULL | NULL | NULL | first_event_install_users | 101 |
| NULL | NULL | NULL | NULL | first_event_install_users | 102 |
| NULL | NULL | NULL | NULL | first_event_install_users | 103 |
| NULL | NULL | NULL | NULL | first_event_install_users | 201 |
| NULL | NULL | NULL | NULL | first_event_install_users | 202 |
Hints
- Use COUNT(DISTINCT user_id) for DAU.
- Tie-break top event types deterministically when counts are equal.
- Use ROW_NUMBER to identify each user's first event.