Bank of America·Data Scientist·Technical Phone Screen
- Given a users table and an events table, compute the 7-day conversion rate by country as of a fixed date. Define conversion rate as users with at least one purchase divided by users with at least one visit in the same window, counting each user at most once in numerator and denominator.
- For each user, return their first purchase date and the number of days between signup and first purchase. Use window functions to handle any duplicate events.
- Using a self join on the events table, identify users who had at least one visit that occurred strictly before their first purchase.
- Explain how the choice between LEFT JOIN and RIGHT JOIN would change the conversion rate results if certain countries have no purchases during the window.
“This took me longer than I expected to set up correctly.”