Analytics — SQL + Case study, offer accepted

Background

I have 3 years of experience as a Business Analyst at a Big Tech / FAANG firm. I applied for an Assistant Manager role in Analytics at Lenskart's Gurugram office.

How I Got the Interview

I started updating my resume daily on Naukri from February 15th and bought a premium subscription. Lenskart's HR reached out directly on March 3rd — just two weeks after I began the process. The recruiter discussed role fit, mentioned they needed an immediate joiner, and outlined the loop: three rounds for the Assistant Manager role (Technical, Technical + Behavioural, and HM). I had already been prepping for 4–5 weeks before the first round.

Timeline

Rounds

Round 1 — Technical (SQL)

Format: Virtual | Duration: ~1 hr 10 min | Interviewer: Manager

Q1 — Show the output for each join type (Left, Inner, Right, Full Outer) on two single-column tables, handling duplicates and nulls

I was given two single-column tables and asked to produce the output for each join type, paying close attention to how duplicates and nulls were handled.

Table A

Table B

Q2 — Return each student's highest-scoring and lowest-scoring subject

students

Expected Output

Q3 — Find the percentage of male customers who have never transacted

Schema:

• users(u_id, gender, age, city)
• transactions(u_id, o_id, sku_id, price_sku, qty_purchased)

Q4 — Multi-table aggregation and ranking

orders

customers

stores

Three sub-questions:

• Identify the 2nd highest spending customer by total revenue.
• Find the 2nd highest revenue store within each region.
• Identify customers who purchased in 3 consecutive months.

Verdict: Cleared

---

Round 2 — Technical + Case Study

Format: Virtual | Duration: ~45 min | Interviewer: General Manager (4 levels above the Assistant Manager role)

Q1 — Return only the rows present in one table but not the other (symmetric difference)

T1

T2

Expected Output

Follow-ups:

• Solve in a single query.
• Solve using joins only.

Q2 — Given daily transaction data, compute a running (cumulative) total column

revenue

Expected running totals: 120, 215, 455, 765, 1245, 1305, 1390, 1395, 1410, 1448, 1475.

Q3 — Find the maximum consecutive present period and its length per employee

attendance

Expected output: emp_id 7788 has a maximum consecutive period from 2025-09-08 to 2025-09-11 with 4 days.

Q4 — Cricket analytics: compute cumulative run rate and rolling run rate over the most recent 6 balls

Given ball count and runs scored per ball in schema deliveries(num_balls, runs_scored), compute: (1) cumulative run rate up to the last ball, and (2) run rate over the most recent 6 balls.

Q5 — Behavioural and domain

Self-intro

• Walk through my resume and discuss one impactful project using STAR (Situation, Task, Action, Result/Impact).

Power BI

• Difference between a Calculated Column and a Measure
• Types of relationships in Power BI
• How to optimize a Power BI dashboard

Guesstimate

• Number of people with smartphones in India as of 2026.

Supply Chain case study

• What KPIs I could target to improve delivery from 3 days to 1 day, with the interviewer probing deeper into my solutions, hypotheses, edge cases, and corner cases.

Where I got stuck: The supply chain case study consumed most of the round, with the interviewer diving very deep and pushing me on edge cases and assumptions I'd missed or needed to clarify.

Verdict: Cleared

---

Round 3 — Behavioural + Domain

Format: Virtual | Duration: ~45 min | Interviewer: VP of Analytics

He opened with expectations-based questions — "What are your expectations from Lenskart? Which team would you like to work for?" — which caught me slightly off-guard since these usually come at the end of an interview.

Q1 — What is Random Forest?

He moved through my CV and asked me to justify my claims to verify I could back up what I'd written.

Q2 — Describe one project you delivered end-to-end, including business impact and deliverables

Q3 — If I hire you in Supply Chain and give you this role, how will you improve current processes using AI?

My approach: I discussed customer behaviour clustering to bucket customers and reduce friction/delays in deliveries, warehouse-to-pincode optimization, and user browsing pattern analysis. I highlighted that some users check delivery ETA/TAT frequently (indicating urgency) while others focus only on price or ratings — insights that could inform prioritization.

Verdict: Cleared

---

Round 4 — HR & Offer

Format: Virtual | Duration: ~30 min | Interviewer: HR

HR discussed a CTC range of ₹30–35 lakhs with room for negotiation if I could push back. The entire process from initial call to joining took roughly 8–10 days.

Verdict: Cleared

Final Outcome

I was offered the Assistant Manager role in Analytics at Lenskart at a CTC of ₹30–35 lakhs, which I accepted. The company moves fast — the full interview cycle wrapped in less than two weeks, which signalled strong interest.

What I'd Do Differently

• I'd prepare more deeply on supply chain fundamentals and KPI frameworks, since that domain became the focal point of two rounds.
• I'd anchor better on specific, measurable hypotheses in the case study rather than exploring broadly; the interviewer wanted depth and edge-case clarity, not breadth.
• The opening question about team preferences threw me off because it's unconventional. I'd frame an answer in advance that ties my career goals to the company's needs, not the other way around.

Tips for Future Candidates

1. Master window functions — specifically RANK() and DENSE_RANK(). They appear implicitly in case studies, not just standalone questions. Practice until you can write them without thinking.
2. Be fluent in Power BI and DAX — dashboarding is a significant part of the Analytics work here. Expect a 10–12 minute discussion on calculated columns, measures, relationships, and optimization.
3. Justify your entire CV — if you've listed a technology, framework, or project, be ready to explain it deeply and defend it. The VP specifically tested this.
4. Prepare for supply chain reasoning — since this is a supply chain–adjacent Analytics role, anchor your prep on KPIs, bottlenecks, and how data can unlock operational efficiency. Have concrete examples ready.
5. Know the company moves fast — the interview cycle is tight (8–10 days end-to-end). Have your questions, preferences, and negotiation boundaries clear before the final round.