Top 30 Data Analyst Interview Questions & Answers 2026

Q1. What is the difference between WHERE and HAVING?

Answer: Think of it this way — WHERE acts like a security guard at the entrance who decides which rows even get inside the GROUP BY. HAVING is a second guard at the exit who looks at the grouped totals and decides which groups make it out. So if you want only employees earning above ₹50,000, that is WHERE. If you want only departments where the average salary exceeds ₹50,000, that is HAVING. Mixing them up is one of the most common mistakes I see in interviews at Delhi NCR companies.

Q2. What are window functions? Give an example.

Answer: Here is the key thing window functions do that regular aggregations cannot — they give you a calculation across a group of rows while still keeping every individual row visible in the output. Imagine you want each employee's salary alongside the average salary of their entire department in the same row. A regular GROUP BY would collapse everything into one row per department. A window function like AVG(salary) OVER (PARTITION BY department) keeps every employee row intact and just adds the department average as a new column. ROW_NUMBER(), RANK(), LAG(), LEAD() all work the same way. Interviewers at MNCs in Noida love asking this — it separates candidates who have actually written production SQL from those who have only done tutorials.

Q3. What is the difference between INNER JOIN, LEFT JOIN, and FULL OUTER JOIN?

Answer: I explain this to my students with a customer-orders scenario. You have a Customers table and an Orders table. INNER JOIN gives you only customers who have placed at least one order — anyone without an order is excluded from both sides. LEFT JOIN gives you every customer regardless, and if they have no orders, the order columns come back as NULL. FULL OUTER JOIN gives you every row from both tables — customers with no orders and orders with no matching customer (orphan records). In real analytics work at companies like Wipro or Genpact, you will use LEFT JOIN most often because you typically want all your master records even if transactional data is missing.

Q4. How do you find duplicate records in a table?

Answer: The pattern I teach is: GROUP BY the column you suspect has duplicates, then use HAVING COUNT(*) > 1 to surface only the repeated values. Something like: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1. That tells you which emails appear more than once. If you need to actually delete duplicates and keep one copy, the cleanest approach is a CTE where you assign ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_date DESC) — number 1 is the most recent record you keep, and you delete everything where that number is greater than 1.

Q5. What is a CTE and when do you use it?

Answer: A CTE — written with the WITH keyword before your main SELECT — is essentially a way to name a subquery and refer to it like a temporary table throughout the rest of your query. I find it most valuable when a query has two or three logical steps that would otherwise stack into deeply nested parentheses that nobody can read six months later. For example: first CTE calculates monthly revenue, second CTE calculates monthly target, main query compares the two and flags underperforming months. That is far more readable than one giant query. Most Indian MNCs now expect you to write CTEs rather than nested subqueries in technical rounds.

Q6. Write a query to find the second highest salary.

Answer: There are three ways I cover in class and each one comes up in different interview settings. The simplest is: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) — this works everywhere but only gets you exactly the second highest. The cleaner modern approach uses DENSE_RANK: WITH ranked AS (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) SELECT salary FROM ranked WHERE rnk = 2. DENSE_RANK handles ties better than LIMIT/OFFSET — if two people share the top salary, DENSE_RANK correctly identifies the second distinct value. Always ask the interviewer if you should handle ties, because that changes which approach is right.

Q7. What is the difference between UNION and UNION ALL?

Answer: UNION merges two result sets and silently removes any rows that appear in both. UNION ALL does the same merge but keeps every row including exact duplicates. The practical consequence is speed — UNION has to do a deduplication scan across potentially millions of rows, which is expensive. If you are combining data from two sales regions and you are certain no customer appears in both, always use UNION ALL because you get the same result faster. I have seen candidates confidently use UNION everywhere in take-home assignments, and then the query runs for 10 minutes on a large table. UNION ALL would have finished in seconds.

Q8. What are indexes and how do they improve performance?

Answer: Without an index, every SELECT query scans every single row in the table to find what you need — even if you are looking for one specific customer ID out of ten million records. An index builds a separate sorted structure that the database engine can jump into directly, the same way you use the index at the back of a textbook instead of reading every page. The trade-off is that indexes consume storage and slow down INSERT and UPDATE operations because the index also needs to be updated. In practice: index columns you filter on frequently (WHERE, JOIN conditions, ORDER BY), but do not over-index tables that receive heavy writes like transaction logs.

Q1. What is the difference between VLOOKUP and INDEX-MATCH?

Answer: VLOOKUP is easy to learn but it has a structural weakness — it can only look to the right of the lookup column, and it references columns by number rather than name. So if someone inserts a column in your spreadsheet, your VLOOKUP silently pulls wrong data with no error. INDEX-MATCH does not have either limitation. INDEX tells Excel where to find the answer column, MATCH tells it which row — and neither cares about column order. On 50,000+ rows, INDEX-MATCH also tends to recalculate faster. In MNC interviews in Delhi NCR, saying "I prefer INDEX-MATCH for production files" immediately signals that you have worked on real spreadsheets that other people also edit.

Q2. How do you handle large datasets (500K+ rows) in Excel?

Answer: The moment a file starts lagging with 100K+ rows, I stop using formulas on raw data and shift everything into Power Query for transformation. Power Query loads data lazily and applies steps without filling cells with formulas, so it handles half a million rows without breaking a sweat. For analysis I use Pivot Tables instead of SUMIFS across large ranges — pivot tables are far more memory-efficient. I also convert raw ranges to structured Tables (Ctrl+T) so references stay dynamic. If the dataset crosses Excel's row limit of roughly 1 million rows, that is a hard signal to move the work into SQL or Python and bring only the summary output back into Excel.

Q3. What is Power Query and why is it useful?

Answer: Power Query is the data preparation layer inside both Excel and Power BI. What makes it genuinely useful in day-to-day analyst work is that every cleaning step you apply — removing blank rows, splitting columns, changing data types, merging two sheets — is recorded as a reusable step. Next month when the source file arrives with new data, you click Refresh and Power Query reruns all those steps automatically in seconds. Before Power Query, analysts were spending hours manually cleaning the same CSV files every week. Companies in Noida's IT sector that I have placed students at use Power Query as a standard part of their monthly reporting workflow.

Q4. How do you build a dynamic dashboard in Excel?

Answer: The foundation of any Excel dashboard that actually stays maintainable is separating raw data, processed data, and visuals into distinct sheets. Raw data goes in as a Table (Ctrl+T) so it expands automatically. Pivot Tables sit on a separate sheet pulling from that table — they are the engine behind every chart. Charts are linked to pivot tables, not to raw ranges. Slicers connect to multiple pivot tables at once so one click filters the entire dashboard. KPI tiles at the top are simple cells referencing specific pivot table cells. This architecture means refreshing the dashboard after new data arrives takes less than 30 seconds — which is what managers actually care about.

Q5. What is the difference between relative and absolute cell references?

Answer: When you copy a formula down a column, Excel adjusts relative references (like A1) to match each new row — which is exactly what you want for row-by-row calculations. But if that formula multiplies by a tax rate sitting in cell B1, you do not want that reference adjusting. Adding dollar signs ($B$1) locks it in place no matter where you paste the formula. Mixed references like $B1 lock only the column but let the row shift, or B$1 locks only the row. In practice: I teach students to press F4 after clicking a cell reference — it cycles through all four reference types so you can pick the right one without typing dollar signs manually.

Q1. What is the difference between a measure and a calculated column?

Answer: This is one of the most commonly confused concepts in Power BI interviews — and getting it right shows the interviewer you genuinely understand the data model. A calculated column runs once during refresh and adds a permanent column to your table, stored in memory. It works in row context — think of it like adding a formula column in Excel. A measure has no permanent storage — it recalculates every time a visual renders based on whatever filters, slicers, and context are active at that moment. The practical rule: if the value belongs to each individual row (like profit margin per product), make it a calculated column. If it is something you want to aggregate and slice differently across visuals (like Total Revenue or Average Rating), it must be a measure.

Q2. What is DAX? Give an example of CALCULATE.

Answer: DAX stands for Data Analysis Expressions — it is the formula language that powers measures, calculated columns, and calculated tables in Power BI. The function that trips up most candidates is CALCULATE, and here is the clearest way I know to explain it: CALCULATE does two things simultaneously — it evaluates an expression AND it lets you modify the filter context around that expression. Example from a real project: Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date])). The outer CALCULATE does not just calculate total sales — it shifts the date filter back by exactly one year before doing so. Understanding filter context is what separates average Power BI users from analysts who can build truly dynamic reports.

Q3. What is the difference between DirectQuery and Import mode?

Answer: With Import mode, Power BI pulls a snapshot of your data into its own compressed in-memory engine when you refresh. Reports run extremely fast because all queries hit local memory, not a remote database. The downside is the data is only as current as your last refresh — typically scheduled daily or hourly. DirectQuery skips the local copy entirely and sends live queries to your source database every time a visual renders. This means data is always current, but every slicer click triggers a database query, so performance depends on how fast your source can respond. For most business dashboards I recommend Import mode. Use DirectQuery only when stakeholders genuinely need live data — like a real-time operations screen — and your source database can handle the query load.

Q4. How do you optimise a slow Power BI report?

Answer: The first thing I do with any slow report is open Performance Analyser (under the View tab) and record while I interact with visuals. It tells me exactly which visual is taking the longest and whether the bottleneck is DAX evaluation, visual rendering, or data load. The most common culprits I find: too many visuals on one page competing for resources, calculated columns using iterators like SUMX across millions of rows, bidirectional relationships that create ambiguous filter paths, and high-cardinality columns (like full names or transaction IDs) being used in relationships. The fastest single improvement is usually moving to Import mode if the report runs in DirectQuery, and removing columns from the model that no visual actually uses.

Q5. What is Row-Level Security (RLS) and how do you implement it?

Answer: RLS is how you make one report work for multiple users where each person only sees data relevant to their role — without maintaining separate reports per user. You set it up in Power BI Desktop: go to Modelling → Manage Roles, create a role (say, "North Region"), and write a DAX filter like [Region] = "North". Then in Power BI Service, you go to the dataset settings and assign actual users or Azure AD groups to that role. When those users open the report, the DAX filter runs automatically in the background — they never see a filter but they can only ever see North region data. The USERNAME() function makes this dynamic: [Email] = USERNAME() means each person automatically sees only their own rows based on their login.

Q1. What is the difference between loc and iloc in Pandas?

Answer: The confusion between these two trips up a lot of candidates because the difference is subtle but important in practice. loc uses labels — so df.loc[5, "Salary"] means "row with index label 5, column named Salary." iloc uses pure integer positions — so df.iloc[5, 3] means "sixth row from the top, fourth column from the left," regardless of what labels exist. Where this really matters: when your DataFrame has a DateTime index or a custom string index, df.loc["2024-01-01"] makes intuitive sense. df.iloc["2024-01-01"] would throw an error because iloc only understands 0, 1, 2, 3... integers. In interview tasks, candidates who use iloc on a filtered DataFrame and get unexpected rows usually have this confused.

Q2. How do you handle missing values in a DataFrame?

Answer: My first step is always df.isnull().sum() to understand the scale — a column with 2% missing is handled very differently from one with 40% missing. For small amounts of missing numeric data, mean or median imputation with df["col"].fillna(df["col"].median()) usually works without distorting the distribution. For categorical columns, I typically fill with the most frequent value or a label like "Unknown" depending on the business context. df.dropna() is a last resort because dropping rows means losing all the other valid data in those rows. For time-series data, forward-fill (ffill) or backward-fill (bfill) often makes more sense than statistical imputation. The key point to make in interviews: always explain WHY you chose the method, not just what code you wrote.

Q3. What is groupby and how do you use it?

Answer: groupby is the Pandas equivalent of SQL's GROUP BY and it follows the same split-apply-combine logic. You split the data by one or more columns, apply a function to each group, and Pandas combines the results back into a DataFrame. Simple example: df.groupby("City")["Revenue"].sum() gives total revenue per city. For multiple aggregations at once, .agg() is cleaner: df.groupby("City").agg(Total_Revenue=("Revenue","sum"), Avg_Order=("Revenue","mean"), Order_Count=("OrderID","count")). The named aggregation syntax (column=("source","function")) was introduced in Pandas 0.25 and makes the output column names immediately readable without renaming afterwards.

Q4. How do you merge two DataFrames in Pandas?

Answer: pd.merge() is the most flexible way and I use it almost exclusively over .join() because you have full control over the join key and join type. The how parameter mirrors SQL joins exactly: "inner" keeps only matching rows, "left" keeps all rows from the left DataFrame (unmatched right columns become NaN), "right" does the opposite, "outer" keeps everything from both sides. The part candidates often miss: always check df.shape before and after a merge. If your row count explodes — say from 10,000 rows to 80,000 — it usually means the join key had duplicates on both sides, causing a many-to-many multiplication. Catching that immediately shows an interviewer you understand data integrity, not just syntax.

Q5. How do you detect and remove outliers in Python?

Answer: My go-to is the IQR method because it does not assume the data follows a normal distribution. Calculate the 25th percentile (Q1) and 75th percentile (Q3) using df["col"].quantile(). The IQR is Q3 minus Q1, and any value below Q1 − 1.5×IQR or above Q3 + 1.5×IQR is flagged as an outlier. I always visualise with a seaborn boxplot before removing anything — sometimes what looks like an outlier is actually the most interesting data point (a record-breaking sales day, a fraudulent transaction). The decision to remove outliers depends on whether they represent data errors (remove them) or real extreme events (keep them but note them in your analysis). This distinction is what interviewers at analytics-heavy companies specifically probe for.

Q1. Tell me about a time you found an insight from data that changed a decision.

Answer: Structure your answer using Situation → What you did → What the data revealed → What changed as a result. The magic is in the specificity — numbers matter. A strong answer sounds like: "In my final project, I was analysing e-commerce sales data for a mock retail brand. I noticed that returns in the footwear category were running at 34% — nearly three times the apparel category. When I drilled into the reason codes, 61% of footwear returns cited sizing mismatch. I built a dashboard flagging SKUs with above-average return rates and the team used it to update the size guide on product pages. In the scenario I modelled, that change could reduce returns by roughly 20%." That level of detail — category, percentage, root cause, action — is what interviewers remember.

Q2. How do you explain complex data findings to a non-technical stakeholder?

Answer: The most important shift is moving from describing the analysis to describing the business implication. A finance manager does not need to know you used a rolling 12-month average — they need to know "our Q3 costs are trending 14% above the same point last year and if that continues we will exceed the annual budget by ₹18 lakhs by December." Lead with that number, then be ready to explain how you got there if they ask. Visuals matter enormously — a single well-designed bar chart with clear labels lands in 30 seconds; a table of numbers gets skimmed and ignored. I also recommend narrating the so-what explicitly: "This means we should act on X before the end of this quarter."

Q3. What would you do if the data you received had quality issues?

Answer: The first thing I do is document exactly what I found — missing entries per column, duplicate record count, values that fall outside logical ranges (like a customer age of 300 or a negative invoice amount), inconsistent formatting. Then I go to the data owner, not to complain, but to understand the root cause. Is this a one-time export error? A recurring upstream system issue? A process gap? The answer determines whether I clean it manually this time or whether the fix needs to happen at the source. What I never do is silently clean data and present analysis without flagging the original quality issues — because if the business makes a decision on my numbers, they deserve to know how reliable the underlying data actually was.

Q4. Why do you want to work as a data analyst?

Answer: Avoid the generic "I love working with numbers" answer — every candidate says that. What resonates with interviewers is a specific moment or realisation. Something like: "I worked in operations for two years and every week we were making decisions based on gut feel. When I started pulling actual data — how long processes were taking, where delays clustered, which steps had the highest error rates — I realised the answers were always already in the data, just invisible until someone looked. I want to be the person who makes that visible for a team." That kind of answer shows curiosity, problem orientation, and that you have thought about the actual value of the role — not just that you like Excel.

Q5. Where do you see yourself in 3 years as a data analyst?

Answer: Be specific about skills, not just titles. "Senior analyst" is a title — "independently owning end-to-end reporting for a business function, mentoring one or two junior team members, and contributing to how the team structures its data pipelines" describes actual capability. Tie it to the company: if you are joining a company that is early in its analytics journey, say you want to help build that foundation. If it is a mature analytics team, say you want to go deeper on advanced modelling or stakeholder management. Interviewers notice when candidates have actually thought about how the role fits into their growth versus just practising a generic answer.