Bilt·Software Engineer·Technical Phone Screen
- Given a CSV file with merchant data and a database transactions table, how would you load the CSV into a staging table?
- Write a single SQL query that joins transactions to merchants and returns user_id, merchant_code, category, base_points as FLOOR(amount), and final_points where restaurant transactions get 3x and everything else gets 1x. Unmatched merchants should return 'UNKNOWN' for code and category.
- Why might doing this transformation in SQL with JOINs and CASE expressions be better than handling it in application code after fetching the raw data?
- What indexes would you add and what data-cleaning steps would you apply? Also, how would you handle approximate or inconsistent merchant name matching?
“Pretty mechanical but I fumbled explaining whether I'd validate the CSV before loading or after.”