Coding questions
Manipulate data efficiently in Python
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.
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.
Per-country 7-day conversion rate and revenue
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:
- 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.
- 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).
Table events
| column | type | constraints |
|---|---|---|
| event_id | INT | PRIMARY KEY |
| user_id | INT | NOT NULL |
| country | VARCHAR(2) | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(20) | NOT NULL |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| country | event_date | event_id | event_type | revenue | user_id |
|---|---|---|---|---|---|
| US | 2025-08-26 | 1 | signup | 0 | 1 |
| US | 2025-08-27 | 2 | purchase | 50 | 1 |
| US | 2025-08-28 | 3 | signup | 0 | 2 |
| US | 2025-09-02 | 4 | purchase | 30 | 2 |
| CA | 2025-08-29 | 5 | signup | 0 | 3 |
| CA | 2025-08-30 | 6 | purchase | 20 | 3 |
| CA | 2025-08-30 | 7 | signup | 0 | 4 |
| CA | 2025-08-25 | 8 | signup | 0 | 5 |
| CA | 2025-08-27 | 9 | purchase | 15 | 5 |
| FR | 2025-08-27 | 10 | signup | 0 | 6 |
| FR | 2025-09-01 | 11 | purchase | 40 | 6 |
| FR | 2025-08-28 | 12 | signup | 0 | 7 |
| FR | 2025-08-30 | 13 | purchase | 25 | 8 |
Expected output
One row per country for the 7-day window from 2025-08-26 to 2025-09-01 (inclusive), with the 7-day conversion rate (distinct signup users who also purchased in the window divided by distinct signup users in the window) and total revenue (sum of revenue of all events in the window). Based on the sample data, users and events outside this window are ignored for the calculations.
| conversion_rate_7d | country | total_revenue_7d |
|---|---|---|
| 0.5 | CA | 35 |
| 0.5 | FR | 65 |
| 0.5 | US | 50 |
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.
- Remember to cast one side of the division to a decimal to avoid integer division.