Data Analyst Interview Questions 2026: Complete Preparation Guide

This master guide covers 30+ realistic data analyst interview questions across all evaluation phases: SQL Online Assessments, Python data wrangling, Modern Data Stack & AI query auditing, Product Sense & A/B testing, and Behavioral communication. Every technical question includes explicit table schemas, dirty sample records, expected output tables, production SQL solutions, automated test watch-outs, and live interviewer follow-ups.

The 2026 Interview Architecture: The Two-Stage Technical Funnel

Most job seekers mistakenly treat a technical interview as a single conversation. In reality, modern analytics hiring follows a rigorous multi-stage funnel:

Surviving the Automated Proctored Coding Test (OA)

Before taking any technical screening, internalize the 6 hidden traps that kill SQL OA scores and the proctoring survival protocols.

The 6 Hidden Traps in Automated SQL Graders

1. Non-Deterministic ORDER BY (The Tie-Breaker Trap):
   Automated test runners compare your output against a reference dataset row-by-row. If your query uses ORDER BY salary DESC and three employees earn ₹1,20,000, the database engine returns those three rows in arbitrary order. If your row sequence differs from the reference table, the test case fails.
   The Fix: Always append an unambiguous primary key as a secondary sort key: ORDER BY salary DESC, employee_id ASC.

2. Three-Valued Logic & The NULL Trap with NOT IN:
   In SQL, evaluating x NOT IN (1, 2, NULL) evaluates to UNKNOWN, which filters out 100% of rows. Hidden test cases frequently insert a single orphan row with NULL to catch naive subqueries.
   The Fix: Never use NOT IN for anti-joins. Always use LEFT JOIN ... WHERE right.id IS NULL or NOT EXISTS.

3. Date Gaps in Moving Averages (ROWS vs RANGE):
   Using ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts physical table rows, not calendar days. If a store had zero transactions on Sunday, your "7-day" moving average spans 8 calendar days.
   The Fix: When calendar gaps exist, join against a continuous calendar spine (GENERATE_SERIES) before applying window functions.

4. Integer Division Truncation:
   In PostgreSQL and SQL Server, dividing two integers (COUNT(a) / COUNT(b)) performs integer truncation: 3 / 4 returns 0, not 0.75.
   The Fix: Always multiply the numerator by 100.0 or cast to numeric: COUNT(a) * 100.0 / NULLIF(COUNT(b), 0).

5. Dialect-Specific Syntax Crashes:
   Submitting PostgreSQL shorthand like ::int or ILIKE into a test runner configured for MySQL 8 or SQLite causes fatal syntax errors.
   The Fix: Use ANSI-standard SQL: CAST(x AS INTEGER) and LOWER(name) LIKE '%pattern%'.

6. Statement Timeouts on Cartesian Joins:
   Most platforms enforce a hard statement timeout (e.g. 5,000ms). Joining large tables on non-unique columns without indexing creates a Cartesian explosion (N × M rows) that aborts the execution.

The Proctoring & Anti-Cheat Survival Checklist

Modern assessments (such as Topfolio's proctoring suite, HackerRank Proctored, and Mercer Mettl) monitor telemetry in real-time:

• Hardware: Disconnect all external monitors and HDMI cables before starting. Multi-display scripts trigger immediate session cancellation flags.
• Camera & Gaze Calibration: Position your webcam at eye level. Ensure your face and neck are well-lit without backlighting. Repeated gaze shifts away from the display degrade your session trust score.
• Browser Integrity: Close all background applications, Discord, Slack, and terminal windows. Disable browser extensions (Grammarly, ad-blockers) that inject scripts into the DOM.
• Touchpad Gestures: Do not use multi-finger trackpad swipe gestures. Accidentally triggering a virtual desktop switch registers as a tab_switch violation.

Part 1: SQL Technical Questions (OA & Live Deep-Dive)

Here are 10 realistic interview problems designed to reflect real-world data grime, explicit schemas, edge cases, and follow-ups.

Q1: High-Velocity Customer Segmentation & Order Integrity

Companies Asking This: Amazon (BIE), Swiggy, Flipkart
Difficulty: Easy–Medium | Core Concept: Multi-Table JOIN, Status Filtering, HAVING, Date Arithmetic

Business Scenario

The Marketing Operations team is launching an invite-only VIP loyalty tier. Marketing needs a list of customers who completed more than 3 successfully delivered orders within the trailing 30 days, along with their net delivered spend. Previous queries mistakenly included cancelled orders and suspended accounts, causing financial leakage.

Table Schema (DDL)

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    account_status VARCHAR(20) NOT NULL -- 'ACTIVE', 'SUSPENDED', 'UNDER_REVIEW'
);
 
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),
    order_timestamp TIMESTAMPTZ NOT NULL,
    order_status VARCHAR(20) NOT NULL, -- 'DELIVERED', 'CANCELLED', 'REFUNDED'
    order_total DECIMAL(10, 2) NOT NULL
);

Sample Input Data

customers table:

orders table (Evaluation Date: 2026-09-12 00:00:00 UTC):

Expected Output Table

Production SQL Solution (PostgreSQL)

SELECT 
    c.customer_id,
    c.full_name,
    COUNT(o.order_id) AS delivered_order_count,
    ROUND(SUM(o.order_total), 2) AS total_delivered_spend
FROM customers c
INNER JOIN orders o 
    ON c.customer_id = o.customer_id
WHERE c.account_status = 'ACTIVE'
  AND o.order_status = 'DELIVERED'
  AND o.order_timestamp >= '2026-09-12 00:00:00Z'::timestamptz - INTERVAL '30 days'
GROUP BY 
    c.customer_id, 
    c.full_name
HAVING 
    COUNT(o.order_id) > 3
ORDER BY 
    total_delivered_spend DESC, 
    c.customer_id ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Omitting c.customer_id ASC in ORDER BY causes non-deterministic sorting if two customers have identical spend. Forgetting account_status = 'ACTIVE' fails hidden fraud test cases.
• 🗣️ Live Interviewer Discussion Point: If orders contains 250M rows, explain the optimal index. Target: composite index on orders(order_status, order_timestamp, customer_id, order_total) to allow an index-only scan.

[!TIP] 👉 Solve this question live in Topfolio's PostgreSQL Sandbox →

Q2: Net Realized GMV by Category with Return & Discount Adjustments

Companies Asking This: Amazon, Flipkart, Walmart
Difficulty: Easy–Medium | Core Concept: Multi-table Joins, Aggregation, HAVING vs WHERE, Net Revenue

Business Scenario

Finance needs a report of all product categories that generated over $10,000 in Net Realized Revenue during the Q1 sales campaign. Net Realized Revenue is defined as: (Quantity * Unit Price) - Discount Amount - Refunded Amount. Unsold catalog products must not appear.

Table Schema (DDL)

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50) NOT NULL
);
 
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    category_id INT REFERENCES categories(category_id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    discount_amount DECIMAL(10, 2) DEFAULT 0.00,
    refund_amount DECIMAL(10, 2) DEFAULT 0.00
);

Production SQL Solution

SELECT 
    c.category_name,
    SUM(oi.quantity * oi.unit_price) AS gross_gmv,
    ROUND(
        SUM((oi.quantity * oi.unit_price) - oi.discount_amount - oi.refund_amount), 
        2
    ) AS net_realized_revenue
FROM categories c
INNER JOIN order_items oi 
    ON c.category_id = oi.category_id
GROUP BY 
    c.category_id, 
    c.category_name
HAVING 
    SUM((oi.quantity * oi.unit_price) - oi.discount_amount - oi.refund_amount) > 10000.00
ORDER BY 
    net_realized_revenue DESC, 
    c.category_name ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Filtering on net_realized_revenue > 10000 in the WHERE clause instead of HAVING causes an execution error because aggregate expressions cannot appear in WHERE.
• 🗣️ Live Interviewer Discussion Point: What if discount_amount or refund_amount contains NULLs instead of 0.00? Explain how COALESCE(discount_amount, 0.00) prevents the entire calculation from evaluating to NULL.

[!TIP] 👉 Practice Aggregations on Topfolio →

Q3: Customer Retention Anti-Join & The Three-Valued Logic NULL Trap

Companies Asking This: Uber, Swiggy, Zepto
Difficulty: Medium | Core Concept: Set Difference, Anti-Join, SQL Three-Valued Logic

Business Scenario

Find all active customers who placed at least one delivered order in January 2026 but placed zero orders in February 2026.

Why NOT IN Fails (The Dangerous Trap)

-- FATAL INTERVIEW BUG: DO NOT WRITE THIS
SELECT DISTINCT customer_id
FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'
  AND customer_id NOT IN (
      SELECT customer_id FROM orders 
      WHERE order_date BETWEEN '2026-02-01' AND '2026-02-28'
  );

Why this fails: If the subquery contains a single NULL (e.g. an anonymous guest checkout), SQL evaluates customer_id NOT IN (101, 102, NULL) as UNKNOWN. In SQL, WHERE UNKNOWN returns 0 rows, failing every hidden test case!

Optimal Production Solution (LEFT JOIN ... IS NULL or NOT EXISTS)

-- Method 1: LEFT JOIN Anti-Join (Standard & Fast)
SELECT DISTINCT 
    jan.customer_id
FROM orders jan
LEFT JOIN orders feb
    ON jan.customer_id = feb.customer_id
   AND feb.order_date >= '2026-02-01' 
   AND feb.order_date < '2026-03-01'
   AND feb.order_status = 'DELIVERED'
WHERE jan.order_date >= '2026-01-01'
  AND jan.order_date < '2026-02-01'
  AND jan.order_status = 'DELIVERED'
  AND feb.order_id IS NULL
ORDER BY jan.customer_id ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Using BETWEEN '2026-01-01' AND '2026-01-31' on TIMESTAMPTZ excludes transactions that occurred after 00:00:00 on January 31st. Always use half-open intervals: >= '2026-01-01' AND < '2026-02-01'.
• 🗣️ Live Interviewer Discussion Point: Contrast LEFT JOIN ... WHERE right.id IS NULL vs NOT EXISTS. Explain how modern cost-based optimizers treat both as a Hash Anti-Join.

[!TIP] 👉 Solve Anti-Join Challenges in Topfolio Sandbox →

Q4: Department Salary Ranking & Single-Employee Edge Handling

Companies Asking This: Google, JPMorgan, Microsoft
Difficulty: Medium | Core Concept: Window Functions (DENSE_RANK), CTEs, Partitioning, Ties

Business Scenario

Find the employee with the second highest salary in each department. If there is a tie for the second highest salary, return all tied employees. If a department has fewer than 2 distinct salary tiers, exclude the department.

Table Schema (DDL)

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

Optimal Production SQL Solution

WITH ranked_salaries AS (
    SELECT 
        department,
        name,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department 
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT 
    department,
    name,
    salary
FROM ranked_salaries
WHERE salary_rank = 2
ORDER BY 
    department ASC, 
    salary DESC, 
    name ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Using ROW_NUMBER() or RANK() instead of DENSE_RANK(). If two employees tie for 1st place with ₹20,00,000, RANK() assigns the next person rank 3, completely skipping rank 2 and returning zero rows!
• 🗣️ Live Interviewer Discussion Point: How does this query change if the interviewer asks: "What if the department only has 1 employee and we must output NULL for the second salary?" (Requires pivoting to NTH_VALUE or outer joining department dimension).

[!TIP] 👉 Practice DENSE_RANK Partitioning on Topfolio →

Q5: Calendar-Aware Month-over-Month Growth with Gap Handling

Companies Asking This: Razorpay, Walmart, Amazon
Difficulty: Medium–Hard | Core Concept: Calendar Dimensions, GENERATE_SERIES, LAG(), Division by Zero

Business Scenario

Finance needs a Month-over-Month (MoM) revenue growth report for 2026. A naive GROUP BY drops months with zero transactions, causing LAG() to compare non-adjacent months (e.g., comparing March directly to January if February had 0 sales). Build a query that preserves all calendar months, imputes $0.00 for missing months, and calculates accurate growth.

Optimal Production SQL Solution (Calendar Spine via GENERATE_SERIES)

WITH RECURSIVE calendar AS (
    -- Generate complete monthly spine for 2026
    SELECT DATE '2026-01-01' AS month_start
    UNION ALL
    SELECT (month_start + INTERVAL '1 month')::DATE
    FROM calendar
    WHERE month_start < DATE '2026-04-01'
),
monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', transaction_date)::DATE AS month_start,
        SUM(net_amount) AS revenue
    FROM transactions
    WHERE payment_status = 'SUCCESS'
      AND transaction_date >= '2026-01-01'
      AND transaction_date < '2026-05-01'
    GROUP BY DATE_TRUNC('month', transaction_date)::DATE
),
continuous_timeline AS (
    SELECT 
        c.month_start,
        COALESCE(m.revenue, 0.00) AS revenue
    FROM calendar c
    LEFT JOIN monthly_revenue m 
        ON c.month_start = m.month_start
)
SELECT 
    month_start,
    revenue,
    LAG(revenue) OVER (ORDER BY month_start) AS prev_revenue,
    CASE 
        WHEN LAG(revenue) OVER (ORDER BY month_start) IS NULL THEN NULL
        WHEN LAG(revenue) OVER (ORDER BY month_start) = 0.00 AND revenue > 0 THEN NULL -- Base zero boundary
        WHEN LAG(revenue) OVER (ORDER BY month_start) = 0.00 AND revenue = 0 THEN 0.0
        ELSE ROUND(
            ((revenue - LAG(revenue) OVER (ORDER BY month_start)) * 100.0) 
            / LAG(revenue) OVER (ORDER BY month_start), 
            2
        )
    END AS mom_growth_pct
FROM continuous_timeline
ORDER BY month_start ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Unhandled division by zero when previous month's revenue is 0.00. PostgreSQL will crash with division by zero error unless guarded with CASE or NULLIF.
• 🗣️ Live Interviewer Discussion Point: Why is a calendar dimension table standard in enterprise data warehouses (Snowflake, BigQuery) rather than generating recursive CTEs at runtime?

[!TIP] 👉 Solve MoM Growth in Topfolio Practice →

Q6: User Session Streaks (Gaps-and-Islands Matrix)

Companies Asking This: Duolingo, Zepto, Swiggy
Difficulty: Hard | Core Concept: Gaps-and-Islands, ROW_NUMBER() Grouping, Date Difference

Business Scenario

Identify all users who maintained a daily active login streak of at least 3 consecutive calendar days during the last 30 days. Multiple logins on the same day must count as a single active day.

Optimal Production SQL Solution

WITH distinct_user_days AS (
    -- Deduplicate multiple sessions on the same day
    SELECT DISTINCT 
        user_id,
        login_time::DATE AS active_date
    FROM user_sessions
    WHERE login_time >= CURRENT_DATE - INTERVAL '30 days'
),
streak_groups AS (
    SELECT 
        user_id,
        active_date,
        -- The Gaps-and-Islands trick:
        -- Subtracting a continuous ROW_NUMBER from consecutive dates yields a CONSTANT anchor date
        active_date - (ROW_NUMBER() OVER (
            PARTITION BY user_id 
            ORDER BY active_date ASC
        ) * INTERVAL '1 day') AS grp_anchor
    FROM distinct_user_days
),
streaks AS (
    SELECT 
        user_id,
        MIN(active_date) AS streak_start_date,
        MAX(active_date) AS streak_end_date,
        COUNT(*) AS consecutive_days
    FROM streak_groups
    GROUP BY user_id, grp_anchor
    HAVING COUNT(*) >= 3
)
SELECT DISTINCT user_id
FROM streaks
ORDER BY user_id ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Forgetting DISTINCT user_id, login_time::DATE. If a user logged in 4 times on Monday and once on Tuesday, omitting deduplication causes COUNT(*) to equal 5, falsely reporting a 5-day streak!
• 🗣️ Live Interviewer Discussion Point: Explain the mathematical intuition: why does date - ROW_NUMBER() produce a constant date anchor for uninterrupted consecutive days?

[!TIP] 👉 Practice Gaps-and-Islands on Topfolio →

Q7: 7-Day Trailing Moving Average of Daily GMV

Companies Asking This: Flipkart, Amazon, Zomato
Difficulty: Medium–Hard | Core Concept: Window Frame (ROWS BETWEEN vs RANGE BETWEEN), Date Truncation

Business Scenario

Calculate a 7-calendar-day trailing moving average of daily revenue for the e-commerce marketplace.

Optimal SQL Solution

SELECT 
    sale_date,
    daily_revenue,
    ROUND(
        AVG(daily_revenue) OVER (
            ORDER BY sale_date ASC
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ), 
        2
    ) AS moving_avg_7d
FROM daily_sales_summary
ORDER BY sale_date ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: ROWS BETWEEN 6 PRECEDING computes the average across the 7 most recent records. If the business is closed on weekends (gaps in rows), this calculates a 9-calendar-day average. Clarify whether the prompt assumes continuous data or requires a date spine.
• 🗣️ Live Interviewer Discussion Point: What is the difference between ROWS BETWEEN (physical rows) and RANGE BETWEEN INTERVAL '6 days' PRECEDING (logical value ranges)?

[!TIP] 👉 Practice Sliding Windows on Topfolio →

Q8: Pareto 80/20 Revenue Contribution Share

Companies Asking This: Amazon (BIE), Swiggy, Uber
Difficulty: Medium–Hard | Core Concept: Unpartitioned Window Functions OVER (), Running Totals, Cumulative %

Business Scenario

Identify the top tier of marketplace products that generate the cumulative top 80% of total revenue (Pareto Analysis).

Optimal SQL Solution

WITH product_totals AS (
    SELECT 
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.unit_price) AS product_revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    GROUP BY p.product_id, p.product_name
),
cumulative_shares AS (
    SELECT 
        product_id,
        product_name,
        product_revenue,
        SUM(product_revenue) OVER (ORDER BY product_revenue DESC) AS running_revenue,
        SUM(product_revenue) OVER () AS total_platform_revenue
    FROM product_totals
)
SELECT 
    product_id,
    product_name,
    product_revenue,
    ROUND((running_revenue * 100.0) / total_platform_revenue, 2) AS cumulative_pct
FROM cumulative_shares
WHERE (running_revenue - product_revenue) / total_platform_revenue < 0.80
ORDER BY product_revenue DESC, product_id ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: The boundary condition WHERE (running_revenue - product_revenue) / total < 0.80 ensures that the product that pushes the total over 80% is included in the output.
• 🗣️ Live Interviewer Discussion Point: Explain how SUM(x) OVER () without an ORDER BY or PARTITION BY calculates a platform grand total in a single execution pass without a separate self-join.

[!TIP] 👉 Solve Pareto 80/20 on Topfolio Practice →

Q9: Rolling 30-Day Churn Identification

Companies Asking This: Spotify, Netflix, Zepto
Difficulty: Hard | Core Concept: Self-Joins, Date Inactivity Windows, Event Timelines

Business Scenario

A subscription platform defines a "churned user" as anyone who was active in the prior month (30–60 days ago) but had zero activity in the trailing 30 days. Write a query to identify all churned user IDs as of the current evaluation date.

Optimal SQL Solution

WITH prior_month_active AS (
    SELECT DISTINCT user_id
    FROM user_events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '60 days'
      AND event_timestamp < CURRENT_DATE - INTERVAL '30 days'
),
current_month_active AS (
    SELECT DISTINCT user_id
    FROM user_events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT 
    p.user_id
FROM prior_month_active p
LEFT JOIN current_month_active c 
    ON p.user_id = c.user_id
WHERE c.user_id IS NULL
ORDER BY p.user_id ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Excluding users who permanently deleted their accounts. In production schemas, join with users WHERE account_status != 'DELETED'.
• 🗣️ Live Interviewer Discussion Point: How do you distinguish seasonal dormancy from structural churn?

[!TIP] 👉 Practice Inactive User Anti-Joins on Topfolio →

Q10: Multi-Month User Cohort Retention Matrix

Companies Asking This: Meta, Uber, Swiggy, Zepto
Difficulty: Hard | Core Concept: Cohort Analysis, Date Normalization, Two-Table Retention Aggregation

Business Scenario

Construct a classic monthly cohort retention matrix showing the percentage of users who returned to perform an action in Month 0, Month 1, Month 2, and Month 3 after their initial signup month.

Optimal SQL Solution

WITH user_cohorts AS (
    -- Step 1: Assign each user to their signup cohort month
    SELECT 
        user_id,
        DATE_TRUNC('month', MIN(signup_date))::DATE AS cohort_month
    FROM users
    GROUP BY user_id
),
user_activities AS (
    -- Step 2: Extract distinct activity months per user
    SELECT DISTINCT 
        user_id,
        DATE_TRUNC('month', activity_timestamp)::DATE AS activity_month
    FROM user_activity_logs
),
cohort_sizes AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT user_id) AS total_cohort_users
    FROM user_cohorts
    GROUP BY cohort_month
),
retention_counts AS (
    SELECT 
        uc.cohort_month,
        -- Calculate integer month offset: (Year diff * 12) + Month diff
        (EXTRACT(YEAR FROM ua.activity_month) - EXTRACT(YEAR FROM uc.cohort_month)) * 12 +
        (EXTRACT(MONTH FROM ua.activity_month) - EXTRACT(MONTH FROM uc.cohort_month)) AS month_number,
        COUNT(DISTINCT ua.user_id) AS active_users
    FROM user_cohorts uc
    JOIN user_activities ua 
        ON uc.user_id = ua.user_id
    WHERE ua.activity_month >= uc.cohort_month
    GROUP BY uc.cohort_month, month_number
)
SELECT 
    rc.cohort_month,
    cs.total_cohort_users,
    rc.month_number,
    rc.active_users,
    ROUND((rc.active_users * 100.0) / cs.total_cohort_users, 2) AS retention_pct
FROM retention_counts rc
JOIN cohort_sizes cs 
    ON rc.cohort_month = cs.cohort_month
ORDER BY 
    rc.cohort_month ASC, 
    rc.month_number ASC;

Traps & Discussion Points

• 🤖 OA Test-Runner Watch-Out: Calculating month offsets using AGE() in PostgreSQL works, but breaks in Snowflake or BigQuery. The formula (Year2 - Year1) * 12 + (Month2 - Month1) is portable across all database engines.
• 🗣️ Live Interviewer Discussion Point: How do you handle un-matured cohorts? A cohort formed 15 days ago cannot be evaluated for Month 1 retention without biasing the metric.

[!TIP] 👉 Solve Cohort Retention on Topfolio Practice →

Part 2: Python & Pandas Machine Coding Questions

Modern data analyst rounds (e.g. Swiggy, Zepto, Flipkart machine coding) test data wrangling on dirty DataFrames.

Q11: Time-Aware Moving Average on Irregular Data

# Naive transform fails on missing days. Use time-aware rolling window:
df['order_date'] = pd.to_datetime(df['order_date'])
df = df.sort_values('order_date')
 
# Groupby product with a 7-day rolling time window
df['moving_avg_7d'] = (
    df.set_index('order_date')
    .groupby('product_id')['revenue']
    .rolling('7D')
    .mean()
    .reset_index(level=0, drop=True)
)

What they're testing: Knowledge that rolling(7) counts rows, whereas rolling('7D') requires a DatetimeIndex and correctly calculates a 7-day calendar window.

Q12: Outlier Trimming via Interquartile Range (IQR)

# Statistical Best Practice: Salaries are skewed, NOT normal. Use IQR:
q1 = df['salary'].quantile(0.25)
q3 = df['salary'].quantile(0.75)
iqr = q3 - q1
 
lower_bound = q1 - 1.5 * iqr
upper_bound = q3 + 1.5 * iqr
 
df_clean = df[(df['salary'] >= lower_bound) & (df['salary'] <= upper_bound)]

What they're testing: Why mean ± 2*std is invalid for skewed data (salaries, order values) and how Tukey's IQR method handles asymmetric distributions.

Q13: Merging Dirty Datasets with Audit Flags

# Audit merge discrepancies using the indicator flag
merged = pd.merge(orders, payments, on='order_id', how='outer', indicator=True)
 
missing_payments = merged[merged['_merge'] == 'left_only']
orphan_payments = merged[merged['_merge'] == 'right_only']

What they're testing: Data reconciliation and debugging orphan records during ETL pipelines.

Q14: Multi-Index Pivot Tables with Cohort Imputation

# Resample transactions by month and customer segment with zero-fill
pivot_table = pd.pivot_table(
    df,
    values='net_amount',
    index=pd.Grouper(key='transaction_date', freq='ME'),
    columns='customer_tier',
    aggfunc='sum',
    fill_value=0.0
).round(2)

What they're testing: pd.Grouper for time-series bucketing and handling sparse category matrices.

Q15: Vectorized Conversion Funnel with Safe Zero-Division

# Calculate stage-by-stage drop-off safely:
funnel = (
    df.groupby('cohort_month')[['visited', 'added_to_cart', 'purchased']]
    .sum()
)
 
funnel['cart_conv_pct'] = (
    funnel['added_to_cart'].div(funnel['visited'].replace(0, np.nan)) * 100
).round(2)
 
funnel['checkout_conv_pct'] = (
    funnel['purchased'].div(funnel['added_to_cart'].replace(0, np.nan)) * 100
).round(2)

What they're testing: Vectorized DataFrame division (.div()) and preventing ZeroDivisionError via np.nan.

Part 3: Modern Data Stack & AI Query Auditing (2026 Benchmark)

In 2026, companies evaluate how analysts work alongside LLMs and cloud warehouses (Snowflake, BigQuery).

Q16: Auditing AI-Generated SQL for Fan-Out Bugs

Interviewer Prompt: "A junior analyst used ChatGPT to write a query calculating total revenue per customer. Why does this AI query produce inflated revenue, and how do you fix it?"

-- BROKEN AI QUERY: Notice the JOIN fan-out!
SELECT 
    c.customer_id, 
    SUM(oi.price) as total_spent, 
    COUNT(DISTINCT r.review_id) as total_reviews
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN product_reviews r ON c.customer_id = r.customer_id
GROUP BY c.customer_id;

The Bug: Joining order_items and product_reviews at the customer level creates a Cartesian product between items and reviews. If a customer bought 3 items and wrote 2 reviews, the query creates 3 × 2 = 6 rows, doubling the computed revenue!
The Fix: Aggregate order_items and reviews in separate CTEs before joining to customers.

Q17: Snowflake QUALIFY Clause vs Verbose Subqueries

In Snowflake, you do not need a 10-line CTE to filter on a window function:

-- Clean, modern Snowflake SQL:
SELECT department, employee_name, salary
FROM employees
QUALIFY DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) = 2;

What they're testing: Dialect efficiency and modern warehouse syntax awareness.

Q18: Unnesting Semi-Structured Arrays in Google BigQuery

Modern event data (GA4, Segment) is stored in repeated records:

SELECT 
    event_timestamp,
    event_name,
    param.value.string_value AS page_location
FROM `analytics_project.events_2026`,
UNNEST(event_params) AS param
WHERE event_name = 'page_view'
  AND param.key = 'page_location';

What they're testing: Ability to query JSON, structs, and arrays without ETL flattening.

Q19: Analytics Engineering & dbt Idempotency

Interviewer: "Why must incremental dbt models have an explicit unique_key?"
Answer: Without a unique_key, retrying a failed pipeline run appends duplicate records to the target table. An idempotent pipeline can run multiple times with the same input without changing the final result.

Q20: Text-to-SQL Governance & Dashboard Validation

Interviewer: "How do you prevent hallucinations in automated business intelligence pipelines?"
Answer: Implement strict dbt data tests (not_null, relationships, accepted_values), establish semantic layer metrics (cube / dbt metrics), and validate query execution plans against pre-computed gold summary tables.

Part 4: Product Sense, A/B Testing & Root-Cause Analytics

Q21: The MECE Root-Cause Framework: DAU Dropped 15% Yesterday

When a key metric dips, follow this structured 4-step framework:

1. Telemetry & Pipeline Verification: Check if data pipelines finished on time. Did an ingestion worker fail? Did tracking SDKs stop emitting events?
2. Metric Decomposition: DAU = New Users + Resurrected Users + Retained Users - Churned Users
   Identify which component drove the dip.
3. Segmentation Matrix:
   • Platform: iOS vs Android vs Web (did a mobile release introduce a crash?).
   • Geography: City/Country level (regional ISP outage, holiday, or regulatory block).
   • Channel: Paid Ads vs Organic vs Direct (did an ad campaign budget cap out?).
4. Impact Quantification: Calculate standard deviation over the last 90 days to determine if the 15% drop is outside the 3σ expected variance.

Q22: A/B Testing: Sample Ratio Mismatch (SRM)

Scenario: An A/B test was configured 50/50. After 7 days, telemetry shows 52,000 visitors in Control and 48,000 in Variant.
Question: "Can we evaluate the p-value and declare a winner?"
Answer: No. Running a Chi-Square goodness-of-fit test reveals p < 0.001. This indicates a severe Sample Ratio Mismatch (SRM) caused by:

• Variant redirect latency (users dropping off before the tracking tag fires).
• Bot filtering disproportionately purging one variant.
• Broken user hashing.
  Evaluating an experiment with SRM leads to false positive conclusions; the test must be stopped and fixed.

Q23: Marketplace Network Effects & Switchback Testing (Swiggy / Uber)

Interviewer: "Why does standard user-level A/B randomization fail in ride-sharing or food delivery?"
Answer: Spillover / Interference. If Variant riders receive a ₹50 discount, they request more rides, consuming the local driver supply. This starves Control riders of drivers, artificially lowering Control conversion.
The Solution: Switchback Experiments (randomizing entire cities across 2-hour time windows) or Cluster-based Geo-Randomization.

Q24: Defining "Power Users" (Distribution-Led Analysis)

Avoid arbitrary rules like "orders 5 times a week."

1. Plot the cumulative distribution function (CDF) of user actions.
2. Identify the inflection point (typically the 80th or 90th percentile).
3. Validate against long-term metrics: Do users in this percentile demonstrate 3× higher 90-day LTV?

Q25: Quick-Commerce Dark Store SLA & FinTech Auth Rate Diagnostics

• Quick Commerce (Zepto/Instamart): Decompose 10-minute delivery into: Picker Assignment Time + Picking/Packing Time + Rider Dispatch Wait + Transit Time.
• FinTech (Razorpay/Stripe): Decompose authorization failure rates into: Customer Drop (OTP timeout) + Bank Gateway Downtime + Issuer Fraud Block + Network Latency.

Part 5: Behavioral & Stakeholder Communication Questions

Tier-1 analytics rounds evaluate how you navigate conflict and ambiguity using the STAR Method (Situation, Task, Action, Result).

Q26: When Data Contradicts Executive Intuition

Approach: Never say "You're wrong." Say: "The data reveals an unexpected pattern. Let's walk through the cohort segmentation together." Validate assumptions, confirm data integrity, and present findings as an optimization opportunity rather than a refutation.

Q27: Handling Ambiguous Requirements

Approach: "What specific business decision will this analysis unlock?" Spending 30 minutes aligning on hypotheses and table mockups prevents 3 days of building the wrong report.

Q28: Post-Mortem of a Data Pipeline Outage

Structure: 1) How was the error detected? 2) What was the immediate containment? 3) Who was communicated to? 4) What automated assertions (dbt tests) were introduced to prevent recurrence?

Q29: Prioritizing Ad-Hoc Requests vs Infrastructure

Approach: Classify requests by Urgency vs Business Impact. Blockers on revenue-generating product launches take priority over weekly routine reports. Maintain a transparent analytics sprint backlog.

Q30: Explaining Statistical Significance to Business Stakeholders

Approach: Avoid saying "The p-value is 0.02." Say: "We have a 98% certainty that this checkout change drove a 4% lift in completed orders, translating to an estimated ₹12 Lakhs in monthly incremental GMV."

Company-Specific Technical Interview Breakdowns

Explore our verified hiring guides featuring company-specific interview loops, live problem scenarios, and compensation benchmarks:

2-Week Structured Interview Preparation Roadmap

Related Interview Guides & Resources

• SQL Interview Questions Guide (30 Core Patterns)
• SQL Cheat Sheet: Queries, Joins & Window Functions
• Data Analyst Salary Guide (Verified Indian Compensation)
• How to Become a Data Analyst in 2026: Complete Roadmap
• Business Analyst Interview Questions: 20 Top Scenarios
• Python for Data Analysis Complete Tutorial