Q1: Find the top 3 products by revenue in each category

Why they ask: This tests window functions — the single most asked SQL concept in data analyst interviews. If you cannot write a window function, you will not pass. Every company, from Mu Sigma to Google, tests this.

SELECT category, product_name, revenue
FROM (
    SELECT category, product_name, revenue,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rn
    FROM sales
) ranked
WHERE rn <= 3;

What separates good answers: Explaining why you chose ROW_NUMBER() over RANK() or DENSE_RANK() — ROW_NUMBER gives exactly 3 results per category even with ties, while RANK could give more. The interviewer wants to see that you understand the difference and can choose deliberately.

Q2: Calculate month-over-month revenue growth percentage

Why they ask: This tests LAG/LEAD functions and percentage calculations — the bread and butter of business reporting. Every analyst builds MoM and YoY reports. If you cannot write this query, you cannot do the job.

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) as prev_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / 
             LAG(revenue) OVER (ORDER BY month), 2) as growth_pct
FROM monthly_revenue;

Follow-up trap: “What happens in the first row where there is no previous month?” Answer: LAG returns NULL, and dividing by NULL gives NULL — not an error. A good candidate adds COALESCE or filters out the first row. The interviewer is checking if you think about edge cases in your data.

Q3: Find customers who made purchases in January but not in February

Why they ask: This tests subqueries and set operations. The interviewer wants to see if you think in sets, not loops. Candidates who come from a programming background often try to write procedural logic — that is a red flag in a SQL interview.

SELECT DISTINCT customer_id
FROM orders
WHERE EXTRACT(MONTH FROM order_date) = 1
  AND customer_id NOT IN (
      SELECT customer_id FROM orders
      WHERE EXTRACT(MONTH FROM order_date) = 2
  );

Bonus points: Mention that NOT IN can behave unexpectedly with NULLs — if the subquery returns any NULL customer_id, the entire NOT IN returns no results. A safer alternative is NOT EXISTS or a LEFT JOIN with a NULL check. This level of SQL awareness impresses interviewers.

Example Case: Revenue Drop Investigation

“You are given 6 months of e-commerce order data. The CEO wants to know why revenue dropped 15% last month. Walk me through your analysis.”

The framework that works:

1. Segment by dimension — break revenue down by product category, geography, customer cohort, and acquisition channel. Identify which segment drove the drop.

2. Check for data quality issues — are there missing records, duplicate orders, or a tracking change that affected the numbers? Sometimes the “drop” is a data problem, not a business problem.

3. Compare against external factors — was there a holiday shift, a competitor sale, or a seasonal pattern that explains the change?

4. Present findings with a recommendation — do not just say “Category X dropped.” Say “Category X dropped 25% driven by a stockout in the top 3 SKUs. Recommendation: fix inventory forecasting for high-velocity items.”

Q: Explain the difference between Type I and Type II errors with a business example

What they want to hear: Use fraud detection as the example. A Type I error (false positive) means flagging a legitimate transaction as fraud — the customer gets blocked and calls support, which costs money and damages trust. A Type II error (false negative) means letting a fraudulent transaction through — the company loses money directly.

The business insight: The cost of each error type is different, and the business decides which one to minimize. A bank might tolerate more false positives (block legitimate users) to reduce fraud losses. An e-commerce platform might tolerate more fraud to avoid blocking real customers during a sale. The analyst needs to understand this tradeoff, not just the statistical definition.

Q: How would you determine if a new checkout flow actually improved conversion?

The answer framework: This is an A/B testing question. You need to cover: defining the metric (conversion rate), calculating the required sample size (based on baseline conversion, minimum detectable effect, and desired statistical power), running the test for a sufficient duration, and interpreting the results using statistical significance (p-value below 0.05) and confidence intervals.

Common trap: Candidates say “just compare the conversion rates.” Without statistical testing, a 2% vs 2.5% difference could be random noise. The interviewer wants to hear you talk about sample size, significance, and confidence intervals — not just “A is higher than B.”

Q: What is the Central Limit Theorem and why does it matter for data analysis?

Plain English answer: The Central Limit Theorem says that if you take enough random samples from any population, the distribution of sample means will be approximately normal — regardless of the original distribution. This matters because it enables us to use normal distribution assumptions (z-tests, confidence intervals) for sample means even when the underlying data is skewed.

Business application: When you calculate average order value from a sample of 1000 orders, CLT guarantees that this average follows a normal distribution. This lets you build confidence intervals and make statements like “we are 95% confident the true average order value is between ₹450 and ₹520.”

Tableau Question Example

“Build a dashboard showing regional sales performance with drill-down capability.” They give you a dataset and 30 minutes. The evaluation is not about making it pretty — it is about choosing the right chart types, building useful filters, and creating a layout that answers the business question without requiring explanation.

What impresses interviewers: Adding calculated fields (like YoY growth), using LOD expressions for complex aggregations, and building a dashboard that tells a story — not just displays numbers. A bar chart with a filter is entry-level. A dashboard with KPI cards, trend lines, and drill-down from region to city to store is what gets you the offer.

Python Question Example: Cohort Retention Analysis

“Clean this messy CSV and calculate customer retention by cohort.” This tests your pandas fluency and whether you understand retention as a business concept.

# Cohort retention analysis
df['cohort'] = df.groupby('customer_id')['order_date'].transform('min').dt.to_period('M')
df['order_month'] = df['order_date'].dt.to_period('M')
cohort_data = df.groupby(['cohort', 'order_month']).agg(
    n_customers=('customer_id', 'nunique')
).reset_index()

What they evaluate: Can you handle messy data (missing values, duplicates, wrong data types) without being told? Do you know pandas well enough to write this without Googling every function? Can you explain what the cohort analysis shows and why it matters for the business?

Q: Our app's daily active users dropped 20% this week. How would you investigate?

The framework: Clarify the metric (how is DAU defined — unique logins, unique sessions, unique actions?). Segment the data (did it drop across all platforms or just iOS? All geographies or just one city? All user cohorts or just new users?). Form hypotheses (app update bug, server outage, seasonal pattern, tracking code change). Propose analysis for each hypothesis. Recommend action based on findings.

Common mistake: Jumping to “let me check the data” without first clarifying what DAU means and segmenting the problem. The interviewer wants to see structured thinking before you touch any data.

Q: The marketing team wants to spend ₹50L on a new campaign. How would you measure its success?

The answer: Define success metrics before the campaign launches — not after. Primary metric: incremental revenue or customer acquisition attributable to the campaign. Secondary metrics: brand awareness, engagement rate, cost per acquisition. Set up a control group (geographic or time-based holdout) to measure incrementality. Calculate ROI: (incremental revenue - campaign cost) / campaign cost. Report results with confidence intervals, not just point estimates.

What impresses: Mentioning attribution challenges (last-click vs multi-touch), suggesting a holdout test design, and framing the answer in terms of business ROI rather than vanity metrics like impressions or clicks.

Q: We are launching in a new city. What data would you need to decide which city to launch in first?

The answer: Market size (population, target demographic density, smartphone penetration), competition (existing players, market share, pricing), demand signals (search trends, waitlist signups, social media mentions), operational feasibility (logistics infrastructure, regulatory environment, talent availability), and financial projections (estimated CAC, LTV, break-even timeline).

Key insight: The interviewer is not looking for a perfect answer. They want to see that you can break a vague business question into specific, measurable data requirements. The candidates who fail this round list generic factors without explaining how they would actually measure or compare them.