Coding questions · DoorDash
Generate Weekly Revenue and Engagement Summary with Pandas
This question evaluates proficiency in data manipulation and analytics with Pandas and SQL, covering aggregations, distinct purchase counts, lambda-based user-tier classification, and platform-level conversion-rate computation.
events
| user_id | event_time | event_type | platform | revenue |
|---|---|---|---|---|
| 101 | 2024-04-01 12:01:00 | click | iOS | 0.00 |
| 101 | 2024-04-01 12:02:10 | purchase | iOS | 4.99 |
| 202 | 2024-04-01 13:05:33 | view | Android | 0.00 |
| 303 | 2024-04-01 14:20:05 | click | Web | 0.00 |
| 202 | 2024-04-01 14:45:00 | purchase | Android | 9.99 |
Scenario
You own the clickstream pipeline for a consumer app and must create a weekly revenue and engagement summary.
Question
Using Pandas, create a DataFrame that aggregates total revenue and distinct purchase counts per user from the events table. Use a lambda function inside apply to classify users into tiers based on total revenue (e.g., 0, 0–5, 5+). Write a SQL query that returns, for each platform, the daily conversion rate (purchases / clicks) for the last 30 days.
Hints
Show familiarity with groupby, apply-lambda, dictionary mapping, and SQL aggregations with conditional filtering.
User Revenue And Purchase Tiers
For each user in the events table, compute total_revenue (sum of revenue from purchase events only), purchase_count (number of purchase events), and revenue_tier: 'low' if total_revenue < 5, 'medium' if 5 <= total_revenue < 10, and 'high' if total_revenue >= 10. Return one row per user.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| event_time | TIMESTAMP | |
| event_type | VARCHAR(20) | |
| platform | VARCHAR(20) | |
| revenue | DECIMAL(10,2) |
Sample data:
| event_time | event_type | platform | revenue | user_id |
|---|---|---|---|---|
| 2025-05-10 12:01:00 | click | iOS | 0 | 101 |
| 2025-05-10 12:02:10 | purchase | iOS | 4.99 | 101 |
| 2025-05-10 13:05:33 | view | Android | 0 | 202 |
| 2025-05-10 14:20:05 | click | Web | 0 | 303 |
| 2025-05-10 14:45:00 | purchase | Android | 9.99 | 202 |
Expected output
One row per user with total purchase revenue, number of purchase events, and a revenue tier classification.
| purchase_count | revenue_tier | total_revenue | user_id |
|---|---|---|---|
| 1 | low | 4.99 | 101 |
| 1 | medium | 9.99 | 202 |
| 0 | low | 0 | 303 |
Hints
- Sum revenue only for rows where event_type = 'purchase'.
- Use a CASE expression on the aggregated total_revenue to assign the revenue_tier.
Daily Platform Conversion Rate
For each platform and calendar day between '2025-05-03' and '2025-06-01' (inclusive), return purchases (count of purchase events), clicks (count of click events), and conversion_rate = purchases / clicks (NULL if clicks = 0). Order by event_date and platform.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | |
| event_time | TIMESTAMP | |
| event_type | VARCHAR(20) | |
| platform | VARCHAR(20) | |
| revenue | DECIMAL(10,2) |
Sample data:
| event_time | event_type | platform | revenue | user_id |
|---|---|---|---|---|
| 2025-05-10 12:01:00 | click | iOS | 0 | 101 |
| 2025-05-10 12:02:10 | purchase | iOS | 4.99 | 101 |
| 2025-05-10 13:05:33 | view | Android | 0 | 202 |
| 2025-05-10 14:20:05 | click | Web | 0 | 303 |
| 2025-05-10 14:45:00 | purchase | Android | 9.99 | 202 |
Expected output
For each platform and day between '2025-05-03' and '2025-06-01', show purchases, clicks, and conversion_rate. Based on the sample rows, for 2025-05-10 the results are:
| clicks | conversion_rate | event_date | platform | purchases |
|---|---|---|---|---|
| 0 | NULL | 2025-05-10 | Android | 1 |
| 1 | 1 | 2025-05-10 | iOS | 1 |
| 1 | 0 | 2025-05-10 | Web | 0 |
Hints
- Group by DATE(event_time) and platform to get daily counts per platform.
- Use conditional SUM(CASE WHEN ...) to count purchases and clicks, and NULLIF in the denominator to avoid division by zero when computing conversion_rate.