Data Analyst

Product Design Interview: How to Walk Through Your Case Study as a Data Analyst (And Why Most Candidates Fail This Round)

Seven out of ten data analyst candidates who clear SQL rounds and ace Python questions still get rejected at the product design or case study stage. Not because they lack knowledge, but because they cannot tell a coherent story about their work. The product design interview, specifically the part where you walk through a case study, is where companies like Swiggy, CRED, and Razorpay separate analysts who think from analysts who just compute. If you have an upcoming interview at a product-first company and you are unsure how to structure your case study walkthrough, this guide is what you need to read before that call.

What a Product Design Interview Case Study Actually Tests in 2026

Let us clear up a confusion that trips up a lot of candidates. When a product company asks you to walk through a case study in an interview, they are not asking you to show off your Excel skills or recite a project from your resume. They are testing your product thinking, which is your ability to frame a business problem, define the right metrics, make decisions under ambiguity, and communicate your reasoning clearly to someone who may not look at data all day.

In 2026, this matters more than ever. Indian product companies have matured significantly. Zepto is optimising dark store layouts using demand forecasting. PhonePe is running multi-armed bandit experiments on UPI flows. CRED is building personalised reward engines. The data teams at these companies are deeply embedded in product decisions, not just reporting dashboards. When they hire an analyst, they want someone who can sit in a product review, challenge assumptions, and back their arguments with data. The case study walkthrough is a simulation of exactly that situation. It is a thirty-minute window into how you think, not just what you know.

The shift that has happened in Indian hiring is that product analysts are now expected to come in with a point of view. Gone are the days when you could say “I pulled the data and the PM made the call.” Today, the analyst is expected to co-own the decision. That is the context you need to carry into any case study presentation.

How Product Design Interviews Are Reshaping Data Analyst Hiring in India Right Now

The hiring pattern across Indian product companies has shifted noticeably in the last eighteen months. Roles that were once called “Business Analyst” or “Reporting Analyst” are now titled “Product Analyst” or “Analytics Engineer” and the expectations have climbed accordingly. Paytm, Meesho, and Juspay have all revised their interview processes to include a product design or product thinking round, specifically to filter for analysts who can engage with product decisions rather than just support them from a distance.

At Flipkart, for example, the analytics interview now includes a case study presentation where you are given a product scenario, say, a drop in add-to-cart rate on the mobile app, and you are expected to walk through your entire diagnostic and recommendation live in front of a panel that includes a PM and a senior analyst. At Swiggy, candidates have reported being given a real dataset and asked to walk through what they found, why it matters, and what action they would recommend. The evaluation is not purely on correctness. It is on how you structure the conversation.

Salaries for product analyst roles that require this skill level range from 12 to 20 lakhs per annum at mid-size startups, and 22 to 35 lakhs at companies like Google, Amazon, and the top-tier Indian unicorns. The gap between an analyst who can do this well and one who cannot is increasingly measurable in rupees. Learning to walk through a case study effectively is not a soft skill bonus. It is a core hiring criterion.

Interview Questions This Topic Is Generating at Top Companies

Product-first companies are asking these kinds of questions not to trip you up, but because they genuinely want to know if you can do the job. The job involves explaining complex analysis to non-technical stakeholders, defending your metric choices when a PM pushes back, and identifying the right insight from messy data. Every question below is a variation of a real scenario that has come up in interviews at Indian product companies in the last year.

SQL You Need to Know for Product Design Case Studies

Every product case study eventually involves data. Even if your walkthrough is verbal, the follow-up questions almost always go into the query layer. Companies like PhonePe, Meesho, and Zepto have analytics stacks built on Redshift, BigQuery, or Snowflake, and their interview questions reflect the kind of SQL that analysts write daily. For a product case study scenario, the most common SQL need is cohort analysis, retention calculation, or funnel drop-off measurement. Here is a realistic example: imagine you are analysing the payment completion funnel for a fintech product and you need to calculate weekly retention by user acquisition cohort.

-- Cohort retention analysis for a fintech product
-- Goal: Understand what % of users acquired in a given week
-- complete a payment transaction in subsequent weeks
-- Table: user_events with columns user_id, event_name, event_date, platform

WITH first_payment AS (
    -- Identify each user's first successful payment date
    SELECT
        user_id,
        MIN(DATE_TRUNC('week', event_date)) AS cohort_week
    FROM user_events
    WHERE event_name = 'payment_success'
    GROUP BY user_id
),

weekly_activity AS (
    -- Find all weeks where each user had a payment success
    SELECT
        ue.user_id,
        fp.cohort_week,
        DATE_TRUNC('week', ue.event_date) AS activity_week,
        DATEDIFF('week', fp.cohort_week, DATE_TRUNC('week', ue.event_date)) AS week_number
    FROM user_events ue
    JOIN first_payment fp ON ue.user_id = fp.user_id
    WHERE ue.event_name = 'payment_success'
),

cohort_size AS (
    -- Count users per cohort week
    SELECT
        cohort_week,
        COUNT(DISTINCT user_id) AS total_users
    FROM first_payment
    GROUP BY cohort_week
),

retention_counts AS (
    -- Count retained users per cohort per week
    SELECT
        wa.cohort_week,
        wa.week_number,
        COUNT(DISTINCT wa.user_id) AS retained_users
    FROM weekly_activity wa
    GROUP BY wa.cohort_week, wa.week_number
)

SELECT
    rc.cohort_week,
    rc.week_number,
    cs.total_users AS cohort_size,
    rc.retained_users,
    ROUND(100.0 * rc.retained_users / cs.total_users, 2) AS retention_rate_pct
FROM retention_counts rc
JOIN cohort_size cs ON rc.cohort_week = cs.cohort_week
ORDER BY rc.cohort_week, rc.week_number;
  

The output of this query is a cohort retention table that tells you, for each weekly acquisition cohort, what percentage of users came back to complete a payment in week one, week two, week three, and so on. In an interview, do not just explain the query. Tell the interviewer what a flattening retention curve means for product strategy: if week-four retention stabilises at 40 percent, that is your loyal core and your product decisions should optimise for growing that segment, not just acquiring new users.

Python for Product Case Studies: What Analysts Actually Do

In a product design interview, Python comes up in two ways. Either you are given a dataset and asked to analyse it live or in a take-home, or you are asked how you would approach a specific analytical problem. The most relevant Python skill for walking through a product case study is funnel visualisation and drop-off analysis. Imagine you are a data analyst at Zepto and you want to show the product team where users are dropping out of the checkout flow. Here is how you would do it cleanly in Python using pandas and matplotlib, the two libraries that come up most in Indian product company interviews.

# Funnel drop-off analysis for an e-commerce checkout flow
# Scenario: Zepto checkout funnel — identify where users abandon
# Dataset: checkout_events.csv with columns user_id, step_name, event_timestamp

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

# Load the event-level data
df = pd.read_csv('checkout_events.csv', parse_dates=['event_timestamp'])

# Define the funnel steps in order
funnel_steps = [
    'cart_viewed',
    'address_confirmed',
    'payment_method_selected',
    'otp_verified',
    'order_placed'
]

# Count unique users at each funnel step
funnel_counts = {}
for step in funnel_steps:
    unique_users = df[df['step_name'] == step]['user_id'].nunique()
    funnel_counts[step] = unique_users

funnel_df = pd.DataFrame(list(funnel_counts.items()), columns=['step', 'users'])
funnel_df['drop_off_pct'] = round(
    (1 - funnel_df['users'] / funnel_df['users'].shift(1)) * 100, 1
)
funnel_df['conversion_from_top'] = round(
    funnel_df['users'] / funnel_df['users'].iloc[0] * 100, 1
)

print(funnel_df)

# Visualise the funnel
fig, ax = plt.subplots(figsize=(10, 6))
bars = ax.barh(funnel_df['step'], funnel_df['users'], color='steelblue')
ax.set_xlabel('Unique Users')
ax.set_title('Zepto Checkout Funnel — User Drop-off Analysis')

for i, (bar, row) in enumerate(zip(bars, funnel_df.itertuples())):
    label = f"{row.users:,} users"
    if i > 0:
        label += f" | Drop: {row.drop_off_pct}%"
    ax.text(bar.get_width() + 500, bar.get_y() + bar.get_height() / 2,
            label, va='center', fontsize=9)

plt.tight_layout()
plt.savefig('checkout_funnel.png', dpi=150)
plt.show()
  

This chart gives you an immediate visual of where the biggest user drop is happening. If you see a 45 percent drop between OTP verification and order placement, that is a signal that the payment confirmation step has a friction problem, possibly a timeout or