Solve SQL and Python Data Manipulation Tasks
Company: Adobe
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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:
1) Count distinct daily active users.
2) Return the three most common event_type values yesterday.
3) 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].
Overview: 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.
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:
1. result_set = 'daily_active_users': count distinct active users by event_date.
2. 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.
3. 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.
Tables
UserEvents(id INTEGER, user_id INTEGER, event_type VARCHAR(50), event_date DATE)
Hints
- Use COUNT(DISTINCT user_id) for DAU.
- Tie-break top event types deterministically when counts are equal.