Analytics — SQL + Case study, offer accepted

Background

I have 3 years of work experience as a Business Analyst at a FAANG company. 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.com from February 15th and purchased a premium subscription. Lenskart's HR reached out directly on March 3rd — just two weeks after I began the process. The recruiter discussed the role fit, mentioned they needed an immediate joiner, and outlined the interview structure: three rounds for the Assistant Manager role (Technical, Technical + Behavioural, and HM). I was already prepping for 4–5 weeks before the first round.

Timeline

Rounds

Round 1 — Technical (SQL)

Format: Virtual
Duration: ~1 hour 10 minutes
Date: March 8, 2026 (Sunday)
Interviewer: Manager

Q1 — Join types and NULL/duplicate handling

I was given two single-column tables and asked to show the output for each join type: Left, Inner, Right, and Full Outer, paying close attention to how duplicates and nulls were handled.

Q2 — Highest and lowest scoring subject per student

Given a students table with StudentID, StudentName, Subject, and Score, I needed to return each student's highest-scoring and lowest-scoring subject.

Expected Output:

Q3 — 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)

I needed to find the percentage of male customers with no transaction history.

Q4 — Multi-table aggregation and ranking

Three tables were provided:

Three sub-questions:

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

Verdict: Cleared

---

Round 2 — Technical + Case Study

Format: Virtual
Duration: ~45 minutes
Date: March 11, 2026
Interviewer: General Manager (4 levels above the Assistant Manager role)

Q1 — Symmetric difference (non-matching IDs)

Return only the rows present in one table but not the other.

Follow-ups: (a) solve in a single query, (b) solve using joins only.

Q2 — Running (cumulative) total

Given daily transaction data, compute a running total column.

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

Q3 — Maximum consecutive attendance period

Find the maximum consecutive present period and its length per employee.

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

Q4 — Cricket analytics: cumulative and rolling run rate

Given ball count and runs scored per ball in a 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 the STAR method (Situation, Task, Action, Result/Impact).
• Power BI questions:
  • 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: He asked what KPIs I could target to improve delivery from 3 days to 1 day and kept probing deeper into my solutions, hypotheses, edge cases, and how I'd solve corner cases.

Where I got stuck: The supply chain case study consumed most of the round time, with the interviewer diving very deep into the topic 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 minutes
Date: March 13, 2026
Interviewer: VP of Analytics

The interviewer started 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 are usually end-of-interview questions.

He then moved through my CV and asked me to justify my claims — specifically, "What is Random Forest?" — to verify I could back up what I'd written.

I was asked to describe one project I'd delivered end-to-end, including business impact and deliverables.

The round concluded with a supply chain-focused scenario: "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 minutes
Date: March 16, 2026
Interviewer: HR

The HR discussed a CTC range of ₹30–35 lakhs with room for negotiation if I could push back.

I received the offer letter on March 17, accepted on March 18, and joined on March 20 after completing my notice period. The entire process from initial call to joining took 8–10 days.

Verdict: Cleared

Final Outcome

I was offered the Assistant Manager role in Analytics at Lenskart with a CTC of ₹30–35 lakhs. I accepted and joined on March 20, 2026. The company moves fast — the full interview cycle was completed in less than two weeks, indicating strong interest.

What I'd Do Differently

• I'd prepare more deeply on supply chain fundamentals and KPI frameworks before the interview, since that domain became the focal point of two rounds.
• I would've anchored 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 will 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 strategies.

3. Justify your entire CV: If you've listed a technology, framework, or project, be ready to explain it deeply and defend your understanding. The VP specifically tested this.

4. Prepare for supply chain reasoning: Since this was 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: Lenskart's interview cycle is tight (8–10 days end-to-end). Have your questions, preferences, and negotiation boundaries clear before the final round.