Manipulate data efficiently in Python
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Answer the following: (a) Contrast list comprehensions and generators with respect to memory and evaluation; write a generator that yields rolling windows of size k over a list. (b) In CPython 3.x, what is the maximum integer value and why? (c) Demonstrate a bug caused by Python’s pass-by-object-reference when mutating a list argument inside a function; fix it. (d) Given a pandas DataFrame of the events table (columns: user_id, event_date, event_type, revenue), compute per-country 7-day conversion on 2025-09-01 and total revenue using idiomatic groupby and boolean indexing; avoid apply for row-wise operations.
Overview: This question evaluates proficiency in Python data manipulation, including memory and evaluation differences between list comprehensions and generators, CPython integer semantics, mutable argument behavior, and idiomatic pandas groupby and boolean indexing, and is commonly asked to assess performance awareness, understanding of language internals, debugging of mutable state, and practical data-aggregation skills. It belongs to the Data Manipulation (SQL/Python) domain and tests both conceptual understanding of language semantics and practical application of Python and pandas for efficient data processing.
You are given an events table that tracks user activity in a product. Each row represents a single event.
The table has the following columns:
- user_id: the user performing the event
- country: the user's country (ISO country code)
- event_date: the date the event occurred
- event_type: type of event, e.g., 'signup', 'purchase'
- revenue: revenue generated by the event (0 for non-purchase events)
Assume that today is 2025-09-01. For this question, define the 7-day window as all events with event_date between '2025-08-26' and '2025-09-01' (inclusive).
For each country, compute:
1) conversion_rate_7d: the 7-day conversion rate in that country, defined as:
- numerator: the number of distinct users in that country who have at least one 'signup' event in the 7-day window **and** at least one 'purchase' event in the same 7-day window.
- denominator: the number of distinct users in that country who have at least one 'signup' event in the 7-day window.
- conversion_rate_7d = numerator / denominator.
2) total_revenue_7d: the total revenue in that country from all events in the 7-day window (sum of revenue).
Write an SQL query that returns one row per country with the columns: country, conversion_rate_7d, total_revenue_7d, using the explicit date range '2025-08-26' to '2025-09-01' (inclusive).
Tables
events(event_id INT, user_id INT, country VARCHAR(2), event_date DATE, event_type VARCHAR(20), revenue DECIMAL(10,2))
Hints
- First filter events to the window '2025-08-26' to '2025-09-01' using a CTE or subquery.
- Compute distinct signup users and distinct converted users per country separately, then join those aggregates and divide to get the conversion rate.