Lyft·Data Scientist·Technical Phone Screen
- Given an events table and an orders table, compute for each city and date: unique users with at least one impression, completed orders using the latest status per order, conversion rate, and a 7-day rolling conversion rate per city.
- For each supplier and date in the same window, compute demand as unique non-cancelled orders created on that date, then compute fill rate as min(demand, units_available) divided by demand. Handle missing inventory dates by defaulting units_available to zero.
- If an order is created on one date but completes on a later date, which date should it contribute to for demand vs. completed orders? Explain your definition and justify it.
- Write SQL that handles duplicate rows in the orders table by selecting only the latest status per order using window functions. Then sketch the equivalent pandas code, including the rolling metric and safe division.
“The funnel part was fine but the rolling window is where I got tripped up.”