100 Data Analytics Interview Questions & Answers — India 2026

WHERE filters rows before grouping; HAVING filters groups after GROUP BY. Aggregate functions (SUM, COUNT, AVG) cannot be used in WHERE — only in HAVING.

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right rows show NULL.

ROW_NUMBER: unique sequential number, no ties. RANK: same number for ties, skips next rank (1,1,3). DENSE_RANK: same number for ties, no skipping (1,1,2). Use ROW_NUMBER for "top N per group" to get exactly N rows.

Use GROUP BY on all columns and filter with HAVING COUNT(*) > 1. Alternatively use ROW_NUMBER() OVER (PARTITION BY all_columns) and select rows where row_num > 1.

SELECT email, COUNT(*) AS cnt
FROM   customers
GROUP  BY email
HAVING COUNT(*) > 1;

A CTE (Common Table Expression) is a named temporary result set defined with WITH ... AS. Use CTEs for: multi-step logic that is easier to read, reusing the same subquery multiple times, and recursive queries. Subqueries are fine for simple one-time lookups.

Use DENSE_RANK or a subquery.

-- Method 1: DENSE_RANK
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t WHERE rnk = 2;

-- Method 2: subquery
SELECT MAX(salary) FROM employees
WHERE  salary < (SELECT MAX(salary) FROM employees);

DELETE: removes rows based on WHERE, can be rolled back (DML). TRUNCATE: removes all rows fast, cannot roll back (DDL). DROP: removes the entire table and structure permanently.

Use AVG() as a window function with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.

SELECT order_date,
       daily_revenue,
       AVG(daily_revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7d_avg
FROM   daily_sales;

LAG(col, n) returns the value n rows before the current row. LEAD(col, n) returns the value n rows after. Both use OVER(ORDER BY ...). Common use: month-over-month comparison — LAG(revenue, 1) gives previous month's revenue.

Clustered index defines the physical order of data in the table — only one per table, usually on the primary key. Non-clustered index creates a separate data structure pointing to rows — multiple allowed per table. Clustered indexes make range queries faster; non-clustered help with lookups on specific columns.

Self-join or EXISTS or INTERSECT.

SELECT DISTINCT customer_id
FROM   orders
WHERE  order_month = '2026-01'
  AND  customer_id IN (
         SELECT customer_id FROM orders
         WHERE  order_month = '2026-02'
       );

NULL represents unknown or missing data — it is not zero or empty string. Any comparison with NULL returns NULL (not TRUE/FALSE). Use IS NULL or IS NOT NULL. COALESCE(col, default) returns the first non-NULL value.

Use CASE WHEN ... THEN ... inside a GROUP BY. Or use PIVOT syntax in SQL Server.

SELECT customer_id,
  MAX(CASE WHEN month = 'Jan' THEN revenue END) AS jan_rev,
  MAX(CASE WHEN month = 'Feb' THEN revenue END) AS feb_rev
FROM   monthly_revenue
GROUP  BY customer_id;

UNION removes duplicate rows from the combined result. UNION ALL keeps all rows including duplicates. UNION ALL is faster because it skips the deduplication step — use it when you know there are no duplicates or want to keep them.

Check execution plan first. Common fixes: add indexes on JOIN and WHERE columns, avoid SELECT *, replace correlated subqueries with JOINs or CTEs, use LIMIT when exploring, avoid functions on indexed columns in WHERE.

pd.read_csv() + df.info(), df.describe(), df.isnull().sum(). Always run these three before any analysis.

loc uses label-based indexing (row/column names). iloc uses integer-position-based indexing (0, 1, 2...). loc is inclusive of both end labels; iloc is exclusive of the end index like Python slices.

pd.merge(df1, df2, on="key", how="left/inner/outer"). Equivalent to SQL JOINs. how="left" keeps all rows from df1; how="inner" keeps only matching rows.

result = pd.merge(customers, orders,
                   on='customer_id',
                   how='left')

df.isnull().sum() to count. dropna() to remove rows. fillna(value) to replace with constant, mean, median, or ffill/bfill.

groupby() groups rows by one or more columns. agg() applies one or more aggregation functions per group.

df.groupby('category').agg(
    total_revenue=('revenue', 'sum'),
    avg_order=('revenue', 'mean'),
    order_count=('order_id', 'count')
).reset_index()

Use apply() with a lambda or named function. For element-wise operations on a single column, use map(). For operations involving multiple columns, use apply(axis=1).

df['discount_label'] = df['discount_pct'].apply(
    lambda x: 'High' if x >= 20 else ('Medium' if x >= 10 else 'Low')
)

pd.pivot_table(df, values, index, columns, aggfunc). fill_value=0 handles NaN cells.

pivot = df.pivot_table(
    values='revenue', index='category',
    columns='month', aggfunc='sum', fill_value=0
)

df.drop_duplicates() removes rows that are identical across all columns. Use subset=['col1','col2'] to deduplicate on specific columns only. keep='first' or keep='last' controls which duplicate to retain.

map() operates on a single Series element-by-element. apply() operates on rows or columns of a DataFrame. applymap() (now map() in newer pandas) operates on every element of a DataFrame.

pd.to_datetime(df['col']). Then .dt.year, .dt.month, .dt.day, .dt.dayofweek, .dt.to_period('M').

df['date'] = pd.to_datetime(df['order_date'])
df['year']  = df['date'].dt.year
df['month'] = df['date'].dt.month
df['week']  = df['date'].dt.isocalendar().week

Mean: arithmetic average — sensitive to outliers. Median: middle value — robust to outliers, better for skewed distributions. Mode: most frequent value — used for categorical data. Use median for income, delivery times, property prices.

Standard deviation measures the average spread of values from the mean. High SD means data is widely spread; low SD means data is clustered near the mean. Example: two products with same mean rating but different SDs have very different customer experience consistency.

A bell-shaped symmetric distribution where 68% of values fall within 1 SD, 95% within 2 SD, and 99.7% within 3 SD. It matters because most statistical tests (t-tests, z-tests) assume normally distributed data or sampling distributions (via the Central Limit Theorem).

Regardless of the underlying distribution, the distribution of sample means approaches a normal distribution as sample size increases (typically n > 30). This is why t-tests work on non-normal data when n is large enough.

The probability of observing a result at least as extreme as the data, assuming the null hypothesis is true. p < 0.05 means the result is statistically significant at the 5% significance level — unlikely to have occurred by chance alone. It does NOT mean the effect is large or practically important.

Type I (false positive, α): rejecting H₀ when it is true — concluding an effect exists when it does not. Type II (false negative, β): failing to reject H₀ when it is false — missing a real effect. Power = 1 − β = probability of correctly detecting a real effect.

When testing independence between two categorical variables. Example: is payment method (UPI/card/COD) associated with return rate (yes/no)? Both variables are categorical — chi-square tests whether their association is stronger than chance.

Correlation: two variables move together statistically. Causation: one variable directly causes the other to change. Correlation does not imply causation — a third confounding variable often explains both. Establishing causation requires controlled experiments (A/B tests) or causal inference methods.

A range of values within which the true population parameter falls with a specified probability (e.g., 95%). A 95% CI of [₹480, ₹520] means that if the experiment were repeated 100 times, 95 of the computed intervals would contain the true mean. Wider CI = less precision = need more data.

Population: the complete group you want to study. Sample: a subset of the population you actually measure. Statistics describes the sample; inference extrapolates to the population. The larger and more representative the sample, the better your inference.

A controlled experiment where users are randomly split into a control group (A, current experience) and a treatment group (B, new change). Use A/B tests to validate that a change (new CTA, different price, redesigned page) actually improves a metric rather than assuming it does.

Use a power calculator: input current baseline rate, minimum detectable effect (MDE), significance level (α, typically 0.05), and power (typically 0.80). The result is the minimum users needed per group before making a decision. Never start without this — stopping early leads to false positives.

Checking results before the test reaches the required sample size and stopping early if you see significance. This dramatically inflates Type I error — you will incorrectly declare winners. The fix: pre-commit to a sample size and test duration before starting.

If they affect the same users or metrics, use mutual exclusion (separate user buckets for each test). If they target unrelated parts of the product, they can run in parallel. Track interaction effects when multiple tests affect the same funnel.

Report both statistical significance AND practical significance (effect size). A 0.001% lift in conversion on a ₹1 crore/month platform may be statistically significant but not worth the engineering cost to ship. Always include business context when reporting test results.

Missing values, duplicates, incorrect data types, inconsistent formatting (city names "Mumbai" vs "mumbai" vs "MUMBAI"), outliers, impossible values (age = -5, order_date in 1900), referential integrity violations (order with no matching customer).

First investigate: is the missingness random or systematic (e.g., only missing for a specific region or time period)? If systematic, the missingness itself is informative — flag it as a binary column. If random, consider imputation (mean/median for numeric, mode or "Unknown" for categorical) or dropping the column if it is not critical.

Scaling numeric features to a common range. Min-max normalisation scales to [0,1]. Z-score standardisation centres at mean=0 with SD=1. Use before ML models that are sensitive to scale (KNN, SVM, gradient descent). Not needed for tree-based models (Random Forest, XGBoost) or SQL aggregations.

Convert all date columns to a single format (ISO 8601: YYYY-MM-DD) immediately after loading. In Python: pd.to_datetime(col, infer_datetime_format=True, errors="coerce"). Log rows where conversion failed (NaT) for manual review.

Wide format: each row is one entity; multiple observations are columns (Jan_sales, Feb_sales, Mar_sales). Long format: each row is one observation; a "variable" column identifies what was measured. Long/tidy format is generally better for analysis and visualisation in Python, R, and SQL.

Segment first (by platform, geography, user type, acquisition channel). Check for external events (app store review spike, social media). Check funnel: are users not opening the app, crashing at login, or bouncing from a specific screen? Check recent releases or server incidents. Timeline the drop — is it gradual or a step-change?

Revenue can grow while profit falls if: cost of goods sold grew faster (input cost increase or product mix shift), operating expenses rose (more marketing spend per rupee of revenue), discounts increased (higher GMV but lower net revenue), or one-time costs hit this period. Decompose: gross margin, operating expense ratio, CAC trend, return rate.

Define metrics before launch: adoption rate (% of eligible users who used the feature), engagement depth (how often, how long), impact on the north star metric (retention, revenue, NPS), and absence of harm to adjacent metrics. Set a review timeline — 2-4 weeks post-launch for early signal, 3 months for full evaluation.

Use the ICE framework: Impact (how much will this move a key metric?), Confidence (how certain are we the analysis will yield actionable insight?), Ease (how long will it take?). Score each request and discuss with the requester — sometimes the "urgent" request is not the highest-impact one.

First listen — they may know something about the business context that changes the interpretation. Walk through your methodology step by step. If the disagreement is about the data, trace the source together. If it is about the interpretation, present both interpretations and the evidence for each. Escalate to shared data only if the disagreement cannot be resolved — never change the analysis just to match their preference.

DAX (Data Analysis Expressions) is the formula language for Power BI, Analysis Services, and Excel Power Pivot. It creates calculated columns (computed when data loads) and measures (computed at query time during report rendering). Measures are preferred for performance because they are computed on demand, not stored.

Calculated column: stored in the data model, computed row-by-row when data loads, uses row context, takes memory. Measure: not stored, computed at query/filter time, uses filter context, more performant. Rule: use measures for aggregations shown in visuals; use calculated columns for filtering or grouping.

CALCULATE modifies the filter context for an expression. CALCULATE([Total Sales], Region = "North") returns total sales for only the North region regardless of what filters are active in the visual.

Sales_Excl_Returns =
CALCULATE(
    [Total Revenue],
    FILTER(Orders, Orders[status] <> "Returned")
)

Bar/column: comparing categories. Line chart: trends over time. Scatter: relationship between two continuous variables. Pie/donut: part-to-whole (use sparingly — max 4-5 segments). Heatmap: correlation matrix or calendar intensity. Card/KPI: single key number. Waterfall: contribution to change.

RLS restricts data access by user. Define roles with DAX filters (e.g., [Region] = USERNAME()) in Power BI Desktop, then assign users to roles in Power BI Service. This means a sales manager in Mumbai only sees Mumbai data even though the underlying dataset contains all regions.

VLOOKUP looks up a value in the leftmost column and returns a value from a specified column to the right. INDEX-MATCH is more flexible: it can look up in any column (left or right), is faster on large datasets, and does not break when you insert columns. Best practice in India: use INDEX-MATCH or XLOOKUP (Excel 365) instead of VLOOKUP.

Power Query is Excel's ETL tool — connects to data sources, transforms data, and loads it into Excel. Power Pivot extends Excel with an in-memory data model allowing relationships between tables and DAX measures. Together they enable large-scale data analysis without slow formulas on huge worksheets.

Use PivotTables as data sources, slicers for interactive filtering, PivotCharts for visuals, and named ranges or tables to make the data source auto-expanding. Connect multiple PivotTables to one slicer using "Report Connections" for a synchronised dashboard.

Automatically applies formatting (colour, icons, data bars) based on cell values. Useful for: heat maps on large tables, flagging KPIs above/below threshold, highlighting duplicates, visualising trends in tabular data without creating separate charts.

SUMIF sums values matching a single condition. SUMIFS sums values matching multiple conditions simultaneously. Example: SUMIFS(revenue, region, "North", product, "Electronics") returns revenue for Northern electronics only.

Use Present → Past → Future: what you do now + one achievement, how you got here in one sentence, and why this role. Keep it under 90 seconds.

Keep it forward-looking: "I've grown well in X but I'm looking for a role where I can Y [do more strategic analysis / work closer to the business / mentor others]. This role offers that."

Use STAR. Be specific about a real mistake. Focus 70% on what you did to fix it and the process change you put in place. "I incorrectly joined two tables using a date key that had duplicates — the report was showing double revenue. I caught it during stakeholder review, corrected it within 2 hours, and added a data validation step to every new pipeline thereafter."

Use the "so what" approach: start with the business implication, not the method. "Our return rate for the East region is 2× the national average — that's ₹40L/month in reverse logistics cost." Then offer to go deeper if they want the analysis behind it.

Show growth in the data path: "I want to be leading analysis for a key product or business unit, moving from reporting to shaping decisions. I'd like to develop people management experience alongside that — ideally a small analytics team."

Triage by impact and urgency: "I acknowledge all requests within the hour, estimate effort, flag if something will take more than a day, and check with the requester on deadline flexibility. I build a shared tracker for recurring stakeholders so they have visibility into the queue."

STAR format. Example: "The marketing team was about to increase Facebook ad spend by 40%. I analysed our last 6 months of campaign data and found our cost-per-acquisition on Facebook had grown 3× over 12 months while Google Search CAC stayed flat. The team redirected 30% of the budget to Search, resulting in 18% more signups at the same cost."

"I present the data clearly and professionally. I make sure to separate the finding from the recommendation — sometimes the finding is unwelcome but the recommendation is actionable. I do not change the analysis to match expectations, but I am careful about how I frame things so the stakeholder can act on it rather than becoming defensive."

Reference specific, credible sources: "I follow Towards Data Science, Analytics Vidhya's weekly newsletter, LinkedIn posts from practitioners I respect, and I try to work on a personal data project every quarter using a new technique."

Prepare one strong quantified STAR story. "I built a fraud detection rule engine using SQL window functions that flagged 87% of fraudulent transactions before they completed — preventing an estimated ₹45L in monthly losses. It was adopted across 3 regional business units."

Immediately flag the issue to stakeholders (do not wait for them to notice). Roll back to last known good version of the report. Investigate the root cause in the pipeline (upstream data, schema change, job failure). Fix, rerun, and validate before redistributing. Document the incident and add a data freshness check.

Sample first to validate your logic before running on full data. In SQL: add indexes, use partitioned tables, filter early in the query. In Python: use chunk reading (pd.read_csv(chunksize=)), Dask, or push the computation to SQL. Profile the slow step before optimising.

Define churn (e.g., no activity in 90 days). Build features: recency, frequency, monetary value, usage trend. Label historical data (churned/not). Train binary classifier (logistic regression baseline, then gradient boosting). Evaluate with AUC-ROC and precision-recall. Deploy model score into CRM for retention campaigns. Validate lift over control group via A/B test.

Grouping users by a shared characteristic at acquisition (e.g., month of first purchase) and tracking their behaviour over time. Used for: measuring retention decay, comparing quality of different acquisition cohorts, evaluating whether product changes improved retention for newer vs older users.

Simple CLV = ARPU / Churn Rate. More precise: CLV = (Avg order value × Purchase frequency × Gross margin) / Churn Rate. Use CLV to set maximum CAC (CLV should be at least 3× CAC) and to identify which customer segments to invest in for retention.

Recency (how recently did the customer buy?), Frequency (how often?), Monetary (how much total spend?). Score each dimension 1-5 (or use NTILE in SQL). Combine scores to classify: Champions (555), Loyal (454), At Risk (245), Lost (111). Enables targeted campaigns per segment.

Down vs what baseline (last month, last year, plan)? Which products, regions, or channels? When did the decline start — sudden or gradual? Has anything changed (pricing, competition, marketing spend, product availability)? What decisions will the analysis inform? This scoping prevents doing the wrong analysis.

Cross-check a sample of numbers against the source system manually. Verify totals match at different aggregation levels (daily rolls up to monthly). Test edge cases (date boundaries, null values, filters). Ask a business user who knows the data to sanity-check key figures before publishing.

A transactional (OLTP) database is optimised for fast reads/writes of individual records (row store). A data warehouse (OLAP) is optimised for analytical queries across large historical datasets (column store, star/snowflake schema, aggregated tables). Examples of warehouses: Snowflake, BigQuery, Redshift. Analysts query warehouses, not production databases.

Use their language: start with the number (₹X revenue impact), show the logic in an Excel or a simple table they can drill into, provide the supporting data in an appendix. Offer to walk through it live. A dashboard will not win trust — a well-structured spreadsheet with clear methodology notes will.

Extract (pull data), Transform (clean, restructure), Load (write to target). The core data engineering workflow.

Fact table at centre connected to dimension tables. Optimised for analytical queries in data warehouses.

Policies and processes ensuring data quality, security, privacy, and consistency across an organisation.

Structured: rows/columns in a database. Unstructured: text, images, video, audio — no predefined format.

Data lake: raw, unstructured storage (S3, ADLS) — cheap, flexible. Warehouse: cleaned, structured for querying (Snowflake, BigQuery) — expensive, fast for analytics.

Data Build Tool — transforms data in a warehouse using SQL models, version control, and testing. Modern ELT standard.

Measuring conversion between sequential steps (e.g., Visit → Sign-up → Add to cart → Purchase). Shows where users drop off.

Unsupervised algorithm that partitions data into K clusters based on feature similarity. Used for customer segmentation.

Harmonic mean of precision and recall. F1 = 2×(P×R)/(P+R). Used for classification models when both false positives and false negatives matter.

Area Under the ROC Curve. Measures a binary classifier's ability to distinguish classes across all thresholds. AUC=1 perfect, AUC=0.5 random.

Creating new input variables from raw data to improve model performance — e.g., extracting "day of week" from a date column.

Model memorises training data and fails to generalise. Fix: cross-validation, regularisation, more training data, simpler model.

80% of outcomes come from 20% of causes. Example: 80% of revenue from 20% of customers. Use to prioritise where to focus.

Mutually Exclusive, Collectively Exhaustive. Framework for structuring analysis: categories should not overlap and together should cover all possibilities.

Key Performance Indicator — a quantifiable metric tied to a specific business objective. KPIs should be actionable, measurable, and time-bound.

Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions do not interfere), Durability (committed data persists). Properties of reliable transactions.

Service Level Agreement for data — commitments on data freshness (e.g., "dashboard refreshes by 7 AM"), accuracy, and availability.

Looker: BI platform with LookML modelling layer owned by Google. Looker Studio (formerly Data Studio): free Google BI tool connecting to Google products. Looker Studio is common in startups; Looker in enterprises.

Analysing data ordered in time to extract trends, seasonality, and cycles. Methods: moving averages, decomposition, ARIMA (for forecasting). Example: predicting next month's sales from 3 years of data.

Week 1: understand the business (read docs, shadowing, learn the org chart). Week 2: map the data ecosystem (tables, pipelines, dashboards). Week 3: do one quick analysis that solves a real current problem. Week 4: present findings and ask for feedback. Relationship-building is as important as technical output in the first month.

DELETE removes specific rows based on a WHERE clause and can be rolled back (DML). TRUNCATE removes all rows from a table quickly without logging individual row deletions — it cannot be rolled back in most databases (DDL). DROP removes the entire table structure and all its data permanently (DDL). In an interview: DELETE for conditional removal, TRUNCATE to empty a table keeping the structure, DROP to eliminate the table entirely.

DELETE FROM orders WHERE status = 'cancelled';  -- specific rows
TRUNCATE TABLE temp_staging;                    -- all rows, keep structure
DROP TABLE old_archive;                         -- remove table entirely

WHERE filters rows BEFORE grouping — it operates on individual row values. HAVING filters groups AFTER the GROUP BY clause — it operates on aggregated values. You cannot use aggregate functions (SUM, COUNT, AVG) in a WHERE clause. Example: WHERE revenue > 1000 filters individual rows; HAVING SUM(revenue) > 1000 filters groups whose total revenue exceeds ₹1,000.

Window functions perform calculations across a set of related rows ("a window") without collapsing them into a single output row like GROUP BY does. They use OVER(PARTITION BY ... ORDER BY ...) syntax. Key window functions: ROW_NUMBER() assigns unique sequential numbers; RANK() handles ties with gaps; DENSE_RANK() handles ties without gaps; LAG/LEAD access previous/next row values; SUM/AVG OVER calculates running totals. They are essential for ranking, trend analysis, and cohort calculations.

Three approaches: (1) df.dropna() — remove rows or columns with missing values; use when the fraction of missing data is small and not systematic. (2) df.fillna(value) — replace NaN with a constant, mean, median, or forward/backward fill; use when removing would cause bias or too much data loss. (3) Flag and model — create a binary "was_missing" indicator column before imputing; used in ML when missingness itself is informative. Always report how much data was missing before deciding which approach to use.

Four methods: (1) IQR method — values below Q1 − 1.5×IQR or above Q3 + 1.5×IQR are outliers; robust and widely used. (2) Z-score — values more than 3 standard deviations from the mean are outliers; assumes normal distribution. (3) Box plot — visual inspection. (4) Domain knowledge — a ₹10,000 order is not an outlier in luxury goods but is in grocery. Always decide: remove (if data error), cap/floor (winsorisation), or keep and note them (if genuine extreme values).

Step 1 — Define the hypothesis: "Changing the CTA button from grey to orange will increase checkout conversion rate." Step 2 — Define the primary metric: checkout conversion rate (CVR). Step 3 — Calculate sample size: use a power calculator with current CVR, minimum detectable effect (e.g., 5% relative lift), α=0.05, power=0.80. Step 4 — Randomise users into control (A) and treatment (B). Step 5 — Run the test for at least 2 full weeks (capture weekly seasonality). Step 6 — Analyse with a two-proportion z-test. Step 7 — If p < 0.05 and lift is practically meaningful, ship the variant.

INNER JOIN returns only rows where the join condition is met in BOTH tables — rows without a match are excluded from both sides. LEFT JOIN returns ALL rows from the left table plus matching rows from the right table — if there is no match, the right table columns are NULL. Use INNER JOIN when you only want records that exist in both tables. Use LEFT JOIN when you want all records from one table and optionally matched data from another — for example, all customers even if they have no orders.