Supply Chain Analytics — SQL + case studies + ML depth

Background

I have 5 years of experience at a pharma company. I applied for the Senior Business Analyst role in the Supply Chain Analytics team at Flipkart, based in Bengaluru.

How I Got the Interview

I was referred by a Flipkart Data Engineer on 15 Jan. HR responded on 22 Jan with a pre-round discussion on my current CTC and experience; I had a 30-day notice period at my previous company.

Timeline

Rounds

Round 1 — Data Handling

Format: Video | Duration: ~1 hr | Interviewer: Analytics Lead

The round opened with "Tell me about yourself" and then covered a lot of ground across multiple tools and languages.

Database Design

• Design a database (tables & columns) for a restaurant — tested for normalization thinking, metric derivability, and identifying which columns and tables are important vs. derivable

Excel & Aggregation

• Three aggregation questions using pivot tables and SUMIF, AVERAGEIF, MAXIF

SQL

• Same three aggregation questions using GROUP BY, CASE WHEN, and aggregations
• Follow-ups on CTEs and JOINs

VLOOKUP & XLOOKUP

• Questions on lookup functions

Python

• Replicate the SQL solutions using iloc and Pandas
• Optimize the code for faster execution

Verdict: Cleared

---

Round 2 — Problem Solving & Case Study

Format: Video | Duration: ~1 hr | Interviewer: Analytics Manager

The round opened with "Tell me about yourself" and moved into mental aptitude before the main case study.

Mental Aptitude

• Probability: coin-flipping scenario
• Geometry: angle between clock hands — the answer had to account for the hour hand's continuous movement, not just its position on the hour

Q1 — This February's sales are 20% lower than February last year. What are the possible reasons why?

We spent about 40 minutes building and debating hypotheses across 20–25 factors.

Where I got stuck: The RCA was intensely grilling — building that many hypotheses across different factors was difficult, especially coming from a pharma background and moving into e-commerce. I didn't have the domain intuition to quickly identify all the levers.

Verdict: Cleared

---

Round 3 — Hiring Manager Round

Format: Video | Duration: ~1 hr | Interviewer: Senior Analytics Manager

The round opened with "Tell me about yourself."

Mental Aptitude

• Two-trains problem: two trains approaching from opposite directions, known distance apart, given speeds

Q1 — Design a sales campaign as a Domino's Pizza manager: if we don't deliver within x minutes, we give a y discount. Calculate x and y.

My approach: I built a mathematical model around identifiable constants and scaling assumptions. Kitchen prep and packaging time formed the base; for volume, I assumed 3 pizzas take roughly the same time as 1, and 3+ take about 2x. I layered in traffic estimates via Google APIs plus a safety buffer. I set constraints at both ends — the minimum discount (y) should never fall below the base food cost, and the maximum should only reach that floor, to avoid penalising the delivery person or hurting margins through excessive discounts. Finally, I made the model dynamic based on user distance, building an equation around these variables.

ML

• Precision vs. recall
• Multicollinearity
• Bagging and boosting techniques
• Random Forest vs. other models

I'd looked up the hiring manager on LinkedIn beforehand but couldn't find him. I made a point of asking each interviewer why they'd stayed at Flipkart and asked them to share something about themselves.

Verdict: Cleared

---

Round 4 — HR

Format: Video

Package

• Cash component: 66% hike on previous CTC
• ESOP: equal to salary amount (e.g., 30L salary = 30L ESOP) · 4-year vesting period
• Joining bonus: available but attached to a mandatory 1-year bond — I declined the bonus to avoid the bond
• Competing offer: I had a 100% hike on base salary elsewhere, but chose not to pursue it — being locked into one metric and one team didn't appeal to me

Verdict: Cleared — offer extended

Final Outcome

I received the offer within 2–3 days of the HR round — about three weeks from referral to offer letter. The package was competitive with the other offer I had, but the Flipkart role and team felt like the better fit.

What I'd Do Differently

The Round 2 RCA was the hardest part — building and debating 20+ hypotheses across e-commerce factors when I came from a pharma background. I'd prep harder on case-study booklets and McKinsey-style cases before an interview like this; domain knowledge in e-commerce levers (seasonality, inventory, logistics, customer acquisition costs) would have helped me move faster. I also noticed that every round opened with "Tell me about yourself" and closed with "Any questions for us?" — preparing both thoroughly meant interviewers didn't dig deeper into my CV, which worked in my favour.

Tips for Future Candidates

1. Master case studies, not data wrangling. Data wrangling is increasingly handled by AI tools — focus your prep on McKinsey-style case studies and RCA frameworks. Study case-study booklets (an IIM-A friend's booklet was invaluable) and work through similar problems online. The Domino's problem and the Round 2 RCA were where the real difficulty lay.
2. Build domain intuition before your interview. If you're coming from a different industry, spend time understanding e-commerce levers — inventory dynamics, logistics costs, customer acquisition, seasonality — so you can generate hypotheses quickly in a live case study.
3. Prepare "Tell me about yourself" and "Any questions for us?" thoroughly. Every single round opened and closed with these. A strong self-intro can signal confidence and reduce CV-digging; thoughtful questions for the interviewer (e.g., "Why have you stayed at Flipkart?") create a two-way conversation.