Katkat
Back

Solve SQL and Python Data Manipulation Tasks

AdobeData Scientist · Onsite
MediumData ScientistOnsiteData Manipulation (SQL/Python)

Last updated

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:

  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].