Generate Weekly Revenue and Engagement Summary with Pandas
Company: DoorDash
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: 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.
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.
Tables
events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR(20), platform VARCHAR(20), revenue DECIMAL(10,2))
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.
Tables
events(user_id INTEGER, event_time TIMESTAMP, event_type VARCHAR(20), platform VARCHAR(20), revenue DECIMAL(10,2))
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.