Generate Weekly Revenue and Engagement Summary with Pandas
DoorDashData Scientist · Technical Screen
Last updated
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.
Solution walkthrough is for members
Get the full walkthrough with reference code and complexity analysis, plus Kitty Coach at 300 messages a day. Hints, the code runner and community answers stay free.