INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table and matched rows from the right (NULLs where no match). FULL OUTER JOIN returns all rows from both tables, with NULLs on either side where there is no match. In practice, LEFT JOIN is the most commonly used in analytics because you want to retain all records from your primary table even if the lookup table has missing entries.

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output. ROW_NUMBER() assigns a unique sequential number to each row. RANK() assigns the same rank to tied values but skips subsequent numbers (1, 2, 2, 4). DENSE_RANK() assigns the same rank to ties but does not skip (1, 2, 2, 3). Use ROW_NUMBER() when you need exactly one result per partition, DENSE_RANK() for leaderboards where ties should not penalise the next rank.

WITH ranked AS (SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM employees) SELECT * FROM ranked WHERE rn = 2; This uses a CTE with DENSE_RANK() partitioned by department. DENSE_RANK handles ties correctly — if two employees share the highest salary, the next distinct salary gets rank 2.

A Common Table Expression (CTE) is a temporary named result set defined with the WITH clause. Use CTEs over subqueries when: (1) the same derived table is referenced multiple times, (2) the query has multiple levels of nesting that hurt readability, or (3) you need recursive queries. CTEs make complex queries more readable and maintainable. Performance is typically identical to subqueries in most databases.

SELECT email, COUNT(*) as cnt FROM customers GROUP BY email HAVING COUNT(*) > 1; GROUP BY the columns that should be unique, then use HAVING to filter groups with more than one occurrence. To see the actual duplicate rows, wrap this in a CTE and join back to the original table.

WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot use aggregate functions in WHERE (e.g., WHERE COUNT(*) > 5 is invalid). Use WHERE to filter raw data, HAVING to filter aggregated results. Example: WHERE salary > 50000 filters individual records; HAVING AVG(salary) > 50000 filters departments by their average.

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; LAG() accesses the previous row’s value. This pattern is asked in nearly every product company interview — Flipkart, Swiggy, and Amazon all use variants of this question.

UNION combines result sets from two queries and removes duplicates (performs a DISTINCT). UNION ALL combines result sets without removing duplicates — it is faster because it skips the deduplication step. Use UNION ALL when you know there are no duplicates or when duplicates are acceptable. In analytics work, UNION ALL is used far more often.

NULLs propagate through calculations (NULL + 5 = NULL). Use IS NULL / IS NOT NULL to filter. Use COALESCE(column, default_value) to replace NULLs with a fallback. Use IFNULL() in MySQL or NVL() in Oracle for the same purpose. In JOINs, NULL = NULL returns FALSE — use IS NOT DISTINCT FROM for NULL-safe comparisons. Always test for NULLs in interview solutions or your answers will be marked incorrect.

WITH ranked AS (SELECT category, product, revenue, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn FROM sales) SELECT * FROM ranked WHERE rn <= 3; ROW_NUMBER() is preferred over RANK() here because if two products tie at rank 3, you still want exactly 3 results per category. Use RANK() or DENSE_RANK() if you want to include all ties.

VLOOKUP searches for a value in the first column of a range and returns a value from a specified column. INDEX-MATCH is more flexible: MATCH finds the row position, INDEX returns the value from any column. INDEX-MATCH can look left (VLOOKUP cannot), handles column insertions without breaking, and is faster on large datasets. In 2026, XLOOKUP has largely replaced both — it searches in any direction and handles errors natively.

Select your data range, go to Insert > Pivot Table, choose a location. Drag fields into Rows, Columns, Values, and Filters areas. Pivot Tables summarise large datasets instantly — for example, dragging “Region” to Rows and “Revenue” to Values shows total revenue by region. Add “Month” to Columns for a cross-tabulation. Right-click values to change aggregation (Sum, Count, Average). Pivot Tables are used in every MIS and reporting analyst interview.

Conditional formatting changes cell appearance (colour, icons, data bars) based on rules. Use case: highlight all sales figures below target in red and above target in green. Apply via Home > Conditional Formatting > New Rule. Advanced use: create heat maps on tables, flag duplicate entries, or visually identify outliers. Interviewers test whether you can use this to make dashboards in Excel without BI tools.

Power Query is an ETL tool built into Excel that lets you connect to external data sources, transform data (filter rows, split columns, merge tables, unpivot), and load clean data into your spreadsheet. Use it when: data arrives in messy formats, you need to combine multiple files, or the same cleaning steps repeat weekly. Power Query records transformations as repeatable steps — refresh the data and all cleaning re-applies automatically.

Three methods: (1) Data tab > Remove Duplicates — select columns to check, Excel deletes duplicate rows. (2) Use COUNTIF to flag duplicates: =COUNTIF($A$2:$A2, A2) > 1 returns TRUE for duplicates. (3) Use Power Query > Remove Duplicates for a non-destructive, repeatable approach. Method 3 is preferred in professional settings because it preserves the original data.

Array formulas perform calculations on multiple values simultaneously. Example: =SUM(IF(A2:A100=”Sales”, B2:B100)) sums values in column B only where column A is “Sales” — entered with Ctrl+Shift+Enter in older Excel or as a dynamic array in Excel 365. Modern dynamic arrays (FILTER, SORT, UNIQUE, SEQUENCE) have largely replaced traditional CSE array formulas.

SUMIFS sums values meeting multiple criteria: =SUMIFS(revenue_range, region_range, “South”, month_range, “>=2026-01-01”). COUNTIFS counts cells meeting multiple criteria. Both support wildcards (*?) and comparison operators (>=, ). These functions are the Excel equivalent of SQL WHERE with multiple conditions — mastering them is essential for any reporting role.

Use Pivot Tables as the data engine, Pivot Charts for visuals, and Slicers for interactive filtering. Connect multiple Pivot Tables to a single Slicer using Report Connections. Add KPI cards using formulas linked to Pivot Table cells. Use conditional formatting for colour-coded indicators. This approach creates a fully interactive dashboard that refreshes with new data — no VBA required. Most companies prefer this over macros for maintainability.

Use df.isnull().sum() to identify missing values per column. Options: (1) df.dropna() to remove rows with NULLs, (2) df.fillna(value) to impute with mean, median, or a constant, (3) df.interpolate() for time-series data. The right choice depends on the percentage of missing data and whether missingness is random. If less than 5% is missing, dropping is usually safe. If more, imputation is preferred. Always document your approach.

merge() is SQL-style joining on columns: pd.merge(df1, df2, on=’id’, how=’left’). join() joins on index by default. concat() stacks DataFrames vertically (axis=0) or horizontally (axis=1) without matching on keys. Use merge() for database-style operations, concat() for appending datasets with the same structure. In interviews, demonstrate that you know which to use and why.

df.groupby(‘category’)[‘revenue’].sum() groups data by category and sums revenue. You can apply multiple aggregations: df.groupby(‘category’).agg({‘revenue’: ‘sum’, ‘orders’: ‘mean’}). Use .transform() to add aggregated values back to the original DataFrame without collapsing rows. This is the pandas equivalent of SQL GROUP BY and is tested in nearly every Python round.

loc selects data by label (column names and index labels): df.loc[0:5, ‘name’:’salary’]. iloc selects by integer position: df.iloc[0:5, 0:3]. Key difference: loc is inclusive of the end point, iloc is exclusive. Use loc when you know column names, iloc for positional slicing. A common interview trap: df.loc[0:5] returns 6 rows, df.iloc[0:5] returns 5 rows.

Use matplotlib for basic plots: plt.bar(x, y), plt.plot(x, y), plt.scatter(x, y). Use seaborn for statistical visualisations: sns.heatmap(), sns.boxplot(), sns.pairplot(). Always include: title (plt.title), axis labels (plt.xlabel, plt.ylabel), and legend where applicable. For interview presentations, seaborn is preferred because it produces publication-quality visuals with less code.

A lambda is an anonymous one-line function: lambda x: x * 2. In pandas, it is used with apply(): df[‘column’].apply(lambda x: ‘High’ if x > 100 else ‘Low’). Use it for quick transformations that don’t warrant a full function definition. Common in data cleaning — for example, extracting domain from email: df[’email’].apply(lambda x: x.split(‘@’)[1]).

Use pd.read_csv(‘file.csv’, chunksize=10000) to read in chunks for files that exceed memory. Specify dtypes to reduce memory: pd.read_csv(‘file.csv’, dtype={‘id’: ‘int32’}). Use usecols to load only needed columns. For very large files (10GB+), consider Dask or Polars. In interviews, mentioning memory-efficient techniques signals you have worked with real-world data sizes, not just tutorial datasets.

A Series is a one-dimensional labelled array (like a single column). A DataFrame is a two-dimensional labelled table (like a spreadsheet or SQL table). A DataFrame is a collection of Series sharing the same index. Selecting a single column from a DataFrame returns a Series: df[‘name’] returns a Series. Selecting multiple columns returns a DataFrame: df[[‘name’, ‘age’]] returns a DataFrame.

Mean is the arithmetic average — sensitive to outliers. Median is the middle value when sorted — robust to outliers. Mode is the most frequent value — useful for categorical data. Use median for income or salary data (skewed distributions). Use mean when data is normally distributed. Use mode for categorical analysis (most popular product, most common complaint category).

Standard deviation measures how spread out data points are from the mean. A low SD means data clusters near the mean; a high SD means data is widely dispersed. In analysis, SD helps identify outliers (values beyond 2-3 SDs), assess consistency (low SD in delivery times = reliable service), and compare distributions. It is the foundation of confidence intervals and hypothesis testing.

A p-value is the probability of observing your result (or more extreme) if the null hypothesis is true. If p-value < 0.05 (standard threshold), reject the null hypothesis — the result is statistically significant. A p-value of 0.03 means there is a 3% chance the observed effect is due to random variation. Important: a low p-value does not prove causation, and statistical significance does not always mean practical significance.

Correlation means two variables move together. Causation means one variable directly causes the other. Example: ice cream sales and drowning deaths are positively correlated — but ice cream does not cause drowning. The hidden variable (confound) is summer heat. In data analysis, always ask: is there a plausible causal mechanism, or are both driven by a third factor? Use controlled experiments (A/B tests) to establish causation.

A normal (Gaussian) distribution is bell-shaped and symmetric around the mean. About 68% of data falls within 1 SD, 95% within 2 SDs, and 99.7% within 3 SDs (the 68-95-99.7 rule). It is important because many statistical tests (t-tests, ANOVA, linear regression) assume normal distribution. In practice, check normality using histograms, Q-Q plots, or the Shapiro-Wilk test before applying parametric tests.

(1) Define null hypothesis (H0: no effect) and alternative hypothesis (H1: there is an effect). (2) Choose significance level (alpha, typically 0.05). (3) Select the appropriate test (t-test, chi-square, ANOVA). (4) Calculate the test statistic and p-value. (5) If p-value < alpha, reject H0. Example: H0 = "new website design has no effect on conversion rate." Run an A/B test, compute p-value, and decide.

Linear regression models the relationship between a dependent variable and one or more independent variables as a straight line: y = mx + b. Use it to predict continuous outcomes (sales forecast, salary prediction) or understand the strength and direction of relationships (does marketing spend predict revenue?). Key metrics: R-squared (how much variance is explained), coefficients (direction and magnitude of each variable’s effect), and p-values (which variables are significant).

Type I error (false positive): rejecting a true null hypothesis — concluding an effect exists when it does not. Type II error (false negative): failing to reject a false null hypothesis — missing a real effect. Example: a drug trial declares a drug effective when it is not (Type I) or declares it ineffective when it actually works (Type II). Reducing one type of error typically increases the other. In business analytics, the cost of each error type should guide your significance threshold.

Define success metrics before launch: primary metric (adoption rate or conversion lift), secondary metrics (engagement, retention), and guardrail metrics (page load time, error rate). Compare pre/post launch data or run an A/B test. Track metrics over time — initial spikes may not sustain. Report results with confidence intervals. A strong answer names specific metrics rather than vague goals.

Revenue: GMV, AOV (Average Order Value), revenue per user. Acquisition: CAC (Customer Acquisition Cost), traffic sources, conversion rate. Retention: repeat purchase rate, churn rate, DAU/MAU. Operations: delivery time, return rate, inventory turnover. The key insight interviewers look for: KPIs should be actionable, not just reportable. “We track 50 KPIs” is a weaker answer than “we focus on 5 KPIs tied to business levers.”

(1) Define hypothesis (“changing CTA button colour from blue to green increases clicks”). (2) Determine sample size using a power calculator. (3) Randomly split users into control (A) and treatment (B). (4) Run the test for a statistically significant duration (at least 1-2 weeks to capture weekly patterns). (5) Analyse: compare conversion rates, calculate p-value. (6) If significant, implement the winner. Common mistake: stopping the test too early because initial results look promising.

Decompose revenue: Revenue = Users x Conversion Rate x AOV. Check which component dropped. Segment by: channel (organic vs paid), geography, device, customer type (new vs returning). Check for external factors: seasonality, competitor launches, economic events. Look at the funnel: did traffic drop (acquisition problem) or did conversion drop (product/pricing problem)? Present findings as a structured tree, not a random list of guesses.

Lead with the business impact (“Revenue dropped 15% because returning customer orders fell 22%”), not methodology. Use visuals — one clear chart beats a table of numbers. Structure as: Situation, Finding, Recommendation, Next Step. Avoid jargon (say “the result is reliable” not “p-value was 0.02”). Anticipate questions and have backup slides with methodology details for stakeholders who ask.

Data storytelling combines data, visuals, and narrative to drive decisions. Raw numbers do not persuade — a story does. Structure: Context (what was the situation), Insight (what did the data reveal), Action (what should we do). Example: instead of “Churn rate is 8.3%”, say “We are losing 1 in 12 customers every month. Customers who do not engage in the first 7 days are 3x more likely to churn. An onboarding email sequence could reduce this by 30%.” The story creates urgency and points to action.

Define churn precisely (no login for 30 days? cancelled subscription?). Calculate churn rate by cohort (signup month). Segment churned vs retained users by: usage frequency, feature adoption, support tickets, plan type, acquisition channel. Build a logistic regression or decision tree to identify top churn predictors. Recommend interventions targeted at high-risk segments. Track whether interventions reduce churn in subsequent cohorts.

Check for: missing values, duplicates, data type mismatches, outliers beyond 3 SDs, referential integrity (do all foreign keys have matching primary keys?). Validate against known benchmarks (does the total revenue match the finance team’s number?). Document assumptions and data lineage. Automate checks where possible — a data quality issue that ships to production is far more expensive than one caught during analysis.

Use the STAR method: Situation (the data challenge), Task (what you needed to deliver), Action (how you handled missing data — imputation, proxy variables, acknowledging limitations), Result (the outcome and what you learned). Example: “I was analysing customer feedback but 30% of responses had missing satisfaction scores. I imputed using the median score per product category and flagged the imputed values separately so stakeholders understood the data quality.”

Assess each request on: business impact (will this decision move revenue/retention?), urgency (is there a deadline?), and effort (quick win or multi-week project?). Communicate timelines proactively — “I can deliver X by Tuesday and Y by Friday.” Use an impact-effort matrix to visually prioritise. Never silently miss a deadline — flag delays early with a revised estimate.

Structure your answer: (1) The initial assumption or planned decision. (2) Your analysis that challenged it. (3) How you communicated the findings. (4) The revised decision and its outcome. Interviewers want to see that you can influence decisions with data, not just produce reports. Quantify the impact: “The analysis saved the company ₹12L in ad spend by reallocating budget from underperforming channels.”

Listen first — understand their perspective and the business context they have that you might lack. Then show your work: share the methodology, data sources, and assumptions transparently. If they challenge the data, investigate together rather than defending. Often disagreements arise from different definitions (their “active user” is not yours). Align on definitions before debating conclusions.

This is a new question category in 2026. Strong answer: “I use AI tools to accelerate code writing (generating pandas boilerplate, SQL query drafts), brainstorm analysis approaches, and draft presentation narratives. I always validate AI-generated code against the actual data — AI tools hallucinate and produce syntactically correct but logically wrong queries. I use them as a productivity multiplier, not a replacement for analytical thinking.”

Mention specific sources: following data professionals on LinkedIn, reading Towards Data Science, practising SQL on LeetCode/HackerRank weekly, taking micro-courses on Kaggle. Demonstrate continuous learning with examples: “Last month I completed a Kaggle competition on time-series forecasting and learned Prophet.” Interviewers want to see initiative, not just “I read articles sometimes.”

STAR method again. Focus on your learning process: “I needed to use Power BI for a client dashboard with a one-week deadline. I completed the Microsoft Learn path in two days, built a prototype on day three, iterated based on feedback, and delivered on time. My approach was to learn just enough to ship, then deepen my skills after delivery.” This shows adaptability and pragmatism.

Avoid generic answers like “I love data.” Connect your answer to: (1) a specific problem you solved using data that excited you, (2) the impact data-driven decisions can have on businesses and people, and (3) why this company specifically. Example: “I analysed my college fest attendance data and identified that Instagram promotions drove 3x more footfall than posters — that moment of finding a non-obvious insight and acting on it is what drives me.”