HR screen -> Round 1
30-min SQL round: 4 old/frequently-asked questions, with my basic approach:
Q1. Percentage of high frequency customers (order>30) each month.
Get cus_id, month, order_count. Group by month, cus;
count(distinct case when oc> then cus end)/count(distinct cus)
Q2. Customers who ordered the most, excluding high-frequency customers, each month (follow-up: the most frequent customer across all months)
- filter_cus( rk() (pb mth order by ordercount DESC) as rk, order_count<=30)
- Where rk=1
Q3. Month-over-month sales change for a specific restaurant in 2021, excluding the first month
(follow-up: how would the query change if done for all restaurants)
Year, month, res_id, month sum sales; sum(sale) group by y, m, res
tot_sale - LAG(tot_sale) OVER (partition by res_id order by month ASC)
Q4. Percentage of customers who ordered from restaurants in the bottom sales quartile:
- year, month, res, tot_sales
- ntile(4) over (pb mth order by tot_sales) as rq
JOIN table: date_trunc('month', d.mth) = rqt.mth
count(distinct case when rq=4 then cus_id end)/count(distinct cus)
Group by mth
The case round was also 30 minutes and also a repeat question — the biker project. The interviewer maybe spent three sentences introducing the project, then asked me how I'd approach it as a DS. I started giving a whole speech — going from goal to metrics to causal inference — but halfway through, the interviewer cut me off and said okay okay, he just wanted a quick sense of my initial approach. Then he started drilling into specifics: walk through the biker-side business process, at each step what data becomes available to use as metrics, how metric misclassification and precision/recall trade off in this context, and for A/B testing, besides city-level clustering what other randomization units could be used, what the pros and cons of each are, when you'd use them, and what else they could be swapped for.
Discussion
Loading comments…