Last Mile — SQL + case studies + experiment design

Background

I have about 5.5 years of experience as a Senior Business Analyst at an e-commerce product company. I applied for a Senior Product Analyst role on Zepto's Last Mile team in Bengaluru in November 2024.

How I Got the Interview

I came in through a referral. Someone who knew about the opening at my level put me forward, and I think what got me shortlisted was the domain overlap — product analytics in e-commerce maps cleanly onto quick-commerce work.

I prepped for about a week, though fairly lightly. In hindsight, I spent that week on the wrong things: more technical practice when the loop was actually much more about structured problem-solving and case studies.

Rounds

Round 1 — Screening (HR Phone)

Format: Phone
Duration: ~15–20 minutes

A conversational screen with the recruiter. They walked through my current experience, the areas my projects covered, and how I handled stakeholders. A good chunk of it was understanding whether I'd been contributing within a team or operating as an individual contributor. They also explained what the role would encompass.

Verdict: Cleared

---

Round 2 — Data Handling + Guesstimate

Format: Video
Duration: ~45 minutes

Interviewer was a Lead Product Analyst. Two SQL questions first, then a guesstimate, then time for my questions.

Q1 — Window Functions

Explain the difference between DENSE_RANK, ROW_NUMBER, and RANK.

They framed it around a class of students with marks across different subjects, and had me reason through how each function would number/rank the rows differently — especially how ties are handled and whether ranks skip.

Q2 — Cross Join Behavior

Given table_a with a single column and rows 1,2,3,4,5, and table_b with a single column and rows 1,2,2,3,3,3,4,4,5,6 — what does SELECT a.* FROM a JOIN b WHERE 1=1 return?

I stalled for a moment here. The trick is there's no join condition — WHERE 1=1 is always true — so it's effectively a cross join, and the output is every row of a repeated once per row of b. I asked for a moment, worked through it, and got there.

-- table_a            table_b
-- a                  b
-- 1                  1
-- 2                  2
-- 3                  2
-- 4                  3
-- 5                  3
--                    3
--                    4
--                    4
--                    5
--                    6

SELECT a.* FROM a JOIN b WHERE 1=1;   -- cross join: 5 × 10 = 50 rows

Q3 — Guesstimate

How many dark stores should Zepto open across India?

I bifurcated the country by city tier, then layered in income parity and population density per tier. I initially anchored on existing quick-commerce order demand as a reference, but they asked me not to assume that — so I pivoted to using the demand patterns of leading supermarkets and superstores as a proxy instead. Throughout, I kept it a discussion rather than a monologue, checking each hypothesis with the interviewer to confirm I was heading in the right direction. I landed on a rough figure and closed by asking what the day-to-day of the role looked like — a question pitched appropriately for the interviewer's level.

Where I got stuck: The cross-join question — brief pause before it clicked. On the guesstimate, the interviewer was actively collaborative on the hypotheses.

Verdict: Cleared

---

Round 3 — SQL Joins + Project Deep-Dive

Format: Virtual
Duration: ~45 minutes

Interviewer was an Associate Director, Product Analytics.

SQL Exercise

I was given a few basic tables in an Excel sheet and asked for the row count and exact output of an inner join, left join, right join, and cross join — and crucially, which join you'd reach for in which scenario. It was as much about judgment as mechanics.

Project Deep-Dive

They asked me to pick the project I felt added the most value. I chose a detailed analysis I'd done to estimate whether the buffer applied to delivery lanes was optimally sized. We went deep on the metrics I tracked and the teams and people I'd worked with to land it.

Mini Case — Experiment Design

They ran a short experiment-design case: suppose you test two ETA treatments to lift conversion — one arm shows a tighter ETA (smaller buffer), the other shows the exact ETA. They wanted my success metrics, check/guardrail metrics, and how I'd set the experiment up.

Verdict: Cleared

---

Round 4 — Case Study + Experiment Design

Format: Onsite
Duration: ~1 hour (scheduled 45 minutes)

Interviewer was a Director, Product. This was the meatiest round and ran over.

Case — Surge Pricing

Think about applying a surge / delivery fee from the customer's perspective. Where and why would you apply it? Once applied, how would you set up the experiment and what would you measure?

My approach:

• Design: A user-level A/B test.
• Primary metrics: GOV / GPPO, ASV (average sales value), and customer retention — since users would be paying a delivery fee when they didn't cross a minimum order value.
• Check metrics: Conversion drop from cart → order, and any shift in the population distribution.
• When to apply it: When the system is stressed — i.e. a supply crunch on packers or riders.
• Tiering: Grade the surge by stress level rather than a flat fee (e.g. ~₹30 above one stress threshold, ~₹60 at a higher one).
• I also constructed an overall OPD-loss metric derived from the conversion drop, to quantify the downside.

Then they pushed: how would you handle network impact? I didn't have an immediate answer. I suggested comparing similar stores, similar cities, and similar time frames. The interviewer asked whether I understood time-slicing experiments — I didn't. He walked me through the concept, and we then discussed how it would help neutralise network effects.

Where I got stuck: The network-impact question and not knowing time-slicing. The interviewer was generous about it and turned it into a teaching moment rather than a gotcha.

Verdict: Cleared

---

Round 5 — HRBP Closeout

Format: Onsite
Duration: ~15 minutes

A short conversation with the HRBP about the company's expectations from me and what I should expect from the role. More alignment than evaluation.

Verdict: Cleared

Final Outcome

I received an offer three days after the final round and accepted it. The total time from screening to offer was approximately 10 days — impressive given it spanned Diwali week. The package was in the ₹30–40 LPA range.

What I'd Do Differently

I'd spend the prep week studying how to structure case studies rather than on technical practice. I over-invested in the technical side — the SQL was never the hard part — and under-invested in problem-solving frameworks. The bar here is about thought process, not query syntax.

Tips for Future Candidates

1. Structure everything, especially case studies. Frame the problem before you solve it. A clean structure is what's actually being graded.
2. Don't assume — confirm. State your hypotheses and check them with the interviewer as you go. They want the back-and-forth; the loop is built as a discussion to understand your full thought process, not to watch you produce a final answer in silence.
3. Weight your prep toward problem-solving, not technical. The real signal is in how you break down ambiguous problems. Get comfortable with experiment design — success vs. check metrics, and concepts like time-slicing to control for network effects.