Top 40 Data Analytics Interview Questions (SQL, Excel, Power BI) 2026

Structured Query Language. It is how you talk to databases. Analysts use it to pull data, clean it, join tables, and prepare data for analysis. Without SQL, you are dependent on someone else to give you data. That dependency makes you slower and less valuable.

SELECT returns all rows. SELECT DISTINCT returns only unique rows, removing duplicates. It is useful for finding unique values in a column, like all the different product categories. But on multiple columns, it looks for unique combinations, not unique values per column.

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. You cannot use aggregate functions like SUM in WHERE. That is what HAVING is for. WHERE is for row-level filtering. HAVING is for group-level filtering.

There are a few ways. A subquery with LIMIT and OFFSET. SELECT DISTINCT order_value FROM sales ORDER BY order_value DESC LIMIT 1 OFFSET 1. Or use a window function like DENSE_RANK. The interviewer wants to see you can think beyond basic SELECT-FROM-WHERE.

A JOIN combines rows from two or more tables based on a related column. INNER JOIN returns only matching rows. LEFT JOIN returns all rows from the left table and matching rows from the right, with NULLs where there is no match. RIGHT JOIN is the reverse. FULL OUTER JOIN returns everything. I use LEFT JOIN the most. Most real-world queries involve some kind of JOIN.

A query nested inside another query. Used in WHERE, FROM, or SELECT clauses. You might use one to filter based on a calculation, like finding customers whose average order value exceeds the overall average. They are powerful but can be slow if written poorly. Sometimes a JOIN or a CTE is cleaner.

Functions that perform a calculation on a set of rows and return a single value. COUNT, SUM, AVG, MIN, MAX. They are almost always paired with GROUP BY. The GROUP BY defines the groups. The aggregate function calculates the summary for each group.

It groups rows sharing the same values in specified columns. It is how you summarize data. Sales by region. Average order value by customer segment. Count of users by signup month. Most analytical queries use it.

COUNT(*) counts all rows, including rows with NULLs. COUNT(column) counts only rows where that specific column is not NULL. If your data has missing values, these will give different results. The distinction matters.

You cannot use equals to check for NULL. NULL is the absence of a value, not a value itself. Use IS NULL or IS NOT NULL. COALESCE replaces NULLs with a default value. These functions are essential for data cleaning.

A temporary named result set you can reference within a larger query. It makes complex queries readable. Instead of nesting five subqueries, you define each step as a CTE and combine them. It breaks a big problem into smaller, understandable pieces.

Functions that perform calculations across a set of rows related to the current row. Unlike GROUP BY, they do not collapse rows. ROW_NUMBER, RANK, LAG, LEAD, SUM with OVER. Example. Calculating a running total of sales by month. Or ranking customers by order value within each region.

A database structure that speeds up data retrieval. Like a book index. Without it, the database scans every row. With it, the database knows where to look. Indexes make queries faster but slow down inserts and updates. It is a tradeoff.

Check the execution plan. It shows where time is spent. Avoid SELECT *. Pull only needed columns. Filter early with WHERE. Avoid functions on indexed columns in WHERE. Consider if an index is missing. Query optimization is a practical skill learned by doing.

GROUP BY the columns that should be unique. Use HAVING COUNT(*) greater than one. This returns groups appearing more than once. This task comes up constantly during data cleaning. Every analyst should write this without thinking.

It searches for a value in the first column of a range and returns a value from a specified column. Limitations. It only looks right, never left. It is slow on large data. It breaks if columns are inserted because it uses column index numbers. XLOOKUP fixes most of these problems. Mention XLOOKUP and the interviewer knows you are current.

INDEX-MATCH is more flexible. It can look left or right. It is not dependent on column position. It is faster on large datasets. Slightly harder to learn, which is why VLOOKUP persists despite being worse. Knowing both signals real experience.

A tool for summarizing large datasets interactively. Drag fields into rows, columns, values, and filters. It creates summaries without formulas. Sales by region. Count of orders by month. It is fast and flexible. The first thing I use when exploring new data in Excel.

Data tab, Remove Duplicates button. Select the columns to check. Always make a copy of your data first. Removing duplicates is destructive. Understand what Excel considers a duplicate. It checks the selected columns, not necessarily the entire row.

It changes cell formatting based on rules. Highlight cells above a threshold. Color scales. Data bars. I use it to flag outliers, highlight values needing attention, and make tables scannable at a glance.

SUMIF, COUNTIF, AVERAGEIF for conditional calculations. TEXT functions like LEFT, RIGHT, CONCATENATE for cleaning data. DATE functions like EOMONTH for date math. IFERROR to handle errors cleanly. Name them and briefly explain when you use each.

Select data, insert chart. An effective chart has a clear title, labeled axes, a readable scale, and minimal decoration. Remove gridlines if they do not help. Use color intentionally. The insight should be obvious within seconds.

Relative references change when you copy a formula. Absolute references, with dollar signs, stay fixed. A1 is relative. $A$1 is absolute. Not knowing this leads to broken formulas and wrong results.

Remove unnecessary formatting. Use tables instead of raw ranges. Avoid volatile functions like INDIRECT. Consider Power Query to load and transform data without bringing it all into the sheet. If it is millions of rows, Excel is simply the wrong tool. Use SQL or Python.

A data connection and transformation tool built into Excel and Power BI. It imports data from various sources, cleans it, reshapes it, and loads it. The steps are recorded and repeatable. Refresh the query and the data updates without redoing the work.

Power BI is for building interactive dashboards and reports. Excel is a spreadsheet tool that also does analysis and charts. Power BI handles larger data volumes. It connects to more data sources natively. It is designed for sharing. Excel is designed for individual analysis. They complement each other.

Power Query for data import and transformation. Power Pivot for data modeling and DAX. Power View for visualization. Power BI Service for sharing online. Understanding the components helps you know which tool for which task.

Data Analysis Expressions. A formula language for Power BI. Used for calculated columns, measures, and tables. Example. Total Sales equals SUM of Sales Amount. Or something more complex like Year-over-Year Growth using CALCULATE with DATEADD. DAX is the hardest part of Power BI to learn well.

A calculated column is computed row by row during data refresh and stored. It does not respond to filters. A measure is computed at query time and responds dynamically to filters and slicers. Use calculated columns for static categorizations. Use measures for aggregations that need to be interactive.

A data model with a central fact table surrounded by dimension tables. Fact table contains transactions or events. Dimension tables contain descriptive attributes like customer details or product info. It makes models faster, simpler, and easier to understand. It is the standard approach.

Relationships are created in Model view. One-to-many is most common. The dimension table is on the one side, the fact table on the many side. Filter direction matters. Usually filters flow from dimension to fact. Understanding relationships is fundamental to accurate reports.

Bar and column charts for comparisons. Line charts for trends. Card visuals for key metrics. Tables and matrices for detail. Slicers for interactivity. The choice depends on the question the visual is answering. A good dashboard has a purposeful mix.

Reduce data volume. Import only needed columns. Filter rows at the source. Optimize DAX. Avoid complex calculated columns when measures work. Use a star schema. Check the performance analyzer to identify slow visuals.

Desktop is the free authoring tool installed locally. Service is the cloud platform for sharing, collaborating, and consuming reports. You build in Desktop. You publish to Service. Service enables scheduled data refreshes and dashboard creation.

Validate against source data. Check totals against known benchmarks. Test filters and slicers. Document assumptions. Have someone else review before it reaches stakeholders. There is no substitute for thorough, manual checking.

First, understand the business context. What question are we trying to answer? Then explore the data. Columns, types, missing values. Clean as needed. Do exploratory analysis. Trends, distributions, relationships. Form hypotheses. Test them. Summarize findings clearly, tied back to the original business question.

Start with the conclusion. Not the methodology. "We found that customers who contact support in week one are three times more likely to cancel." Then explain the evidence simply. Use analogies. Use visuals. Avoid jargon. Check for understanding. Be patient.

Have a specific example ready. What was the problem. What data did you analyze. What did you find. What decision was made. What was the result. Even if from a project rather than a job, the structure matters. Problem, analysis, recommendation, outcome.

First, verify it truly does not exist. Check all sources. If it does not, look for a proxy metric that correlates with what you need. Communicate the limitation clearly. If data exists but is unreliable, document the issues and proceed with caveats. Never present bad data as good.

Forty questions. You will not be asked all of them. But if you understand the concepts behind them, you can handle whatever comes up. The interviewer wants to see that you have done the work. That you have written queries that broke and you fixed them. That you have built dashboards someone actually used. That you can take a messy question and turn it into a clear answer.