Data Analyst Interview Questions: SQL, Excel, Python & Power BI

Data Analyst Interview Questions: SQL, Excel, Python and Power BI

Preparing for a data analyst interview requires more than knowing how to write SQL queries or create dashboards. Employers also want to understand how you approach business problems, interpret data, identify errors, and communicate insights clearly.

Whether you are a fresher applying for your first analyst role or an experienced professional looking to switch jobs, preparing for questions across SQL, Excel, Python, and Power BI can help you approach interviews more confidently.

The exact interview process varies by company. Some employers focus on SQL and Excel, while others include Python coding, statistics, dashboard development, business case studies, and questions about previous projects.

This guide covers 20 data analyst interview questions with sample answers, practical examples, and a preparation plan. It also explains how to practise your answers and use mock interviews to identify areas for improvement.

What Do Interviewers Look for in a Data Analyst?

A data analyst helps organisations understand information and make better-informed decisions. Interviewers generally want to assess whether you can work with data accurately and turn your findings into useful insights.

The main skill areas include:

• SQL: Retrieving, joining, filtering, and aggregating data.

• Excel: Cleaning data, building formulas, analysing tables, and creating reports.

• Python: Manipulating datasets, automating repetitive tasks, and performing analysis.

• Power BI: Building data models, writing DAX measures, and creating interactive dashboards.

• Statistics: Understanding distributions, averages, variation, and relationships between variables.

• Business thinking: Translating business questions into analytical tasks.

• Communication: Explaining results, limitations, and recommendations to stakeholders.

You do not necessarily need expert-level knowledge of every tool for every position. Review the job description to determine which skills deserve the most preparation time.

SQL Interview Questions for Data Analysts

SQL is an important skill for many data analyst positions because analysts often need to retrieve and transform information stored in relational databases.

1. What is the difference between INNER JOIN and LEFT JOIN?

Sample answer:

An INNER JOIN returns rows that satisfy the join condition in both tables. A LEFT JOIN returns every row from the left table and matching rows from the right table. If no match exists, the right-side columns contain NULL values.

For example, suppose we have two tables:

Employees

Sales

An INNER JOIN returns Asha and Rahul because they have matching sales records. A LEFT JOIN also returns Neha, with a NULL sales amount.

SELECT
    e.employee_id,
    e.name,
    s.amount
FROM Employees AS e
LEFT JOIN Sales AS s
    ON e.employee_id = s.employee_id;

Interview tip: Be prepared to explain how join conditions, duplicate keys, and NULL values can affect the result.

2. What is the difference between WHERE and HAVING?

Sample answer:

WHERE filters rows before grouping and aggregation. HAVING filters groups after the GROUP BY operation.

Suppose you want to find employees whose total sales exceed 10,000.

SELECT
    employee_id,
    SUM(amount) AS total_sales
FROM Sales
WHERE amount > 0
GROUP BY employee_id
HAVING SUM(amount) > 10000;

Here, WHERE removes rows with zero or negative amounts before aggregation, while HAVING keeps only employee groups whose resulting total exceeds 10,000.

Interview tip: Understand when a condition should apply to individual records and when it should apply to aggregated results.

3. What is the difference between GROUP BY and window functions?

Sample answer:

GROUP BY combines rows into groups and usually returns one row per group. Window functions calculate values across related rows while preserving the individual rows in the result.

For example, to calculate total sales by employee:

SELECT
    employee_id,
    SUM(amount) AS total_sales
FROM Sales
GROUP BY employee_id;

To display each sales record alongside its employee's total sales:

SELECT
    employee_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY employee_id
    ) AS employee_total
FROM Sales;

Window functions are useful for ranking, running totals, moving averages, and comparing individual records with group-level metrics.

4. How would you find duplicate records in SQL?

Sample answer:

First, I would clarify what makes a record a duplicate. It might be a repeated transaction ID, email address, or combination of business fields.

If duplicate transaction IDs are the concern, I could use GROUP BY and HAVING.

SELECT
    transaction_id,
    COUNT(*) AS record_count
FROM Transactions
GROUP BY transaction_id
HAVING COUNT(*) > 1;

This identifies transaction IDs appearing more than once.

However, identifying duplicates is different from deleting them. Before removing records, I would check the business rules and determine whether repeated records represent genuine transactions or data-quality problems.

5. How would you find the second-highest salary in SQL?

Sample answer:

One approach is to use DENSE_RANK() to rank distinct salary values.

WITH SalaryRanks AS (
    SELECT
        salary,
        DENSE_RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM Employees
)
SELECT DISTINCT salary
FROM SalaryRanks
WHERE salary_rank = 2;

This returns the second-highest distinct salary. If several employees earn that amount, it returns the salary value once.

If the interviewer wants the employees receiving that salary, I would include their employee IDs or names in the ranking query.

Interview tip: Clarify whether the question asks for the second row after sorting or the second-highest distinct salary.

Excel Interview Questions for Data Analysts

Excel remains useful for quick analysis, data cleaning, reconciliations, and reporting. Interviewers may ask you to explain formulas or solve practical spreadsheet problems.

6. What is the difference between VLOOKUP and XLOOKUP?

Sample answer:

VLOOKUP searches for a value in the first column of a specified table range and returns a value from a column to its right.

XLOOKUP is more flexible. It can search in one range and return a corresponding value from another range, including a range to the left of the lookup range. It also supports an explicit not-found result.

Example using VLOOKUP:

=VLOOKUP(A2,$F$2:$G$100,2,FALSE)

Example using XLOOKUP:

=XLOOKUP(A2,F2:F100,G2:G100,"Not found")

The exact availability of XLOOKUP depends on the Excel version or spreadsheet application being used.

7. How would you identify and remove duplicates in Excel?

Sample answer:

I would first determine which columns define a duplicate. Two rows might share a customer name but represent different transactions, so removing duplicates based on the wrong column could destroy valid data.

I could use conditional formatting to highlight duplicates, the COUNTIF function to flag repeated values, or Excel's Remove Duplicates feature when the removal criteria are clear.

For example:

=COUNTIF($A$2:$A$100,A2)>1

This returns TRUE when the value in A2 appears more than once in the specified range.

Before deleting records, I would retain a backup and verify the resulting row count.

8. What is a PivotTable, and when would you use it?

Sample answer:

A PivotTable summarises and reorganises data without requiring me to write a separate formula for every group.

For example, if I have sales records containing region, product, date, and revenue, I can create a PivotTable to compare revenue by region and product.

I could also group data by month, count transactions, calculate averages, or compare categories.

PivotTables are useful for exploratory analysis and quick reporting. Before creating one, I would check that the source data has clear headers and consistent data types.

9. What is the difference between SUMIF and SUMIFS?

Sample answer:

SUMIF sums values that meet one condition. SUMIFS supports multiple conditions.

For example, to calculate sales for a particular region:

=SUMIF(A2:A100,"East",C2:C100)

To calculate sales for a region and a product:

=SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Laptop")

In the second formula, column A contains the region, column B contains the product, and column C contains the sales amount.

These functions are useful for conditional reporting without creating a separate PivotTable.

10. How would you clean a messy dataset in Excel?

Sample answer:

I would start by examining the data structure and identifying problems such as missing values, inconsistent formats, duplicate records, extra spaces, and invalid entries.

Depending on the problem, I might use:

• TRIM to remove unnecessary spaces.

• CLEAN to remove many non-printing characters.

• IFERROR to handle expected formula errors.

• Text to Columns to split combined fields.

• Data validation to restrict invalid inputs.

• Power Query to repeat cleaning steps reliably.

I would also document important assumptions and validate the cleaned data against the original source.

For example, if a date column contains several formats, I would standardise it and check whether any values were incorrectly interpreted before using the data for reporting.

Python Interview Questions for Data Analysts

Python is particularly useful for data cleaning, exploratory analysis, automation, and working with larger datasets. Interview questions often focus on pandas, data structures, missing values, and basic statistics.

11. What is pandas, and why is it useful?

Sample answer:

Pandas is a Python library for working with structured data. Its main data structures include Series for one-dimensional labelled data and DataFrame for tabular data.

It supports filtering, grouping, joining, reshaping, handling missing values, and importing data from different formats.

For example:

import pandas as pd

df = pd.read_csv("sales.csv")

summary = (
    df.groupby("region", as_index=False)["revenue"]
      .sum()
      .rename(columns={"revenue": "total_revenue"})
)

print(summary)

This code reads a CSV file and calculates total revenue by region.

12. How do you handle missing values in pandas?

Sample answer:

First, I would measure how much data is missing and determine whether the missingness follows a pattern.

df.isna().sum()

Depending on the context, I might remove records, fill missing values, preserve them, or investigate the source of the problem.

For example, filling a missing age with the median may be appropriate in some analyses, but filling a missing transaction amount with zero could produce misleading results.

The correct approach depends on what the missing value represents and how the field will be used.

13. What is the difference between loc and iloc in pandas?

Sample answer:

loc selects data using labels, while iloc selects data using integer positions.

For example:

# Select rows using index labels
df.loc[0:4, ["name", "salary"]]

# Select rows using integer positions
df.iloc[0:5, 0:2]

The slice endpoints behave differently: label-based slicing with loc generally includes the ending label, whereas positional slicing with iloc excludes the ending position.

This distinction is important when selecting records accurately.

14. How would you find the top five products by revenue using pandas?

Sample answer:

I would group the data by product, aggregate revenue, sort the result in descending order, and select the first five rows.

top_products = (
    df.groupby("product", as_index=False)["revenue"]
      .sum()
      .sort_values("revenue", ascending=False)
      .head(5)
)

print(top_products)

This assumes the dataset contains product and revenue columns and that the revenue values have been validated.

If missing product names or invalid revenue values exist, I would investigate them before interpreting the ranking.

15. How would you investigate an unexpected change in a business metric using Python?

Sample answer:

I would first validate the metric and check whether the data pipeline or calculation had changed.

Next, I would compare the metric across relevant time periods and segments, such as region, product, customer type, or acquisition channel.

Using pandas, I could group records by month and category, calculate totals or averages, and identify where the change was concentrated.

I would then examine possible explanations and distinguish observed patterns from proven causes.

Finally, I would communicate the findings, assumptions, and limitations clearly.

This question tests analytical thinking more than knowledge of a particular Python function.

Power BI Interview Questions for Data Analysts

Power BI interviews often cover data modelling, Power Query, DAX, relationships, and dashboard design. Candidates may also be asked to explain how a report supports a business decision.

16. What is the difference between Power Query and DAX?

Sample answer:

Power Query is primarily used to connect to data sources, transform data, and prepare it for analysis before loading it into the model.

DAX, or Data Analysis Expressions, is used to create calculations in the Power BI data model, including measures and calculated columns.

For example, I might use Power Query to standardise column names and remove invalid rows. I would then use DAX to calculate total revenue or year-to-date sales.

The choice depends on when the transformation should occur and whether the calculation needs to respond dynamically to report filters.

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

Sample answer:

A calculated column is evaluated for each row and stored in the model. A measure is evaluated when a visual or query requests a result, taking the current filter context into account.

For example, a calculated column might classify each transaction into a category, while a measure could calculate total revenue for the currently selected region.

A simple DAX measure is:

Total Revenue = SUM(Sales[Revenue])

If a user filters the report to a particular region, this measure typically returns revenue for that region, assuming the model relationships and filter behaviour are configured correctly.

Measures are generally preferred for dynamic aggregations, while calculated columns are useful when a row-level result is needed for grouping, filtering, or relationships.

18. What is a star schema, and why is it useful?

Sample answer:

A star schema organises a data model around a central fact table connected to dimension tables.

A sales model might contain:

• A Sales fact table with transaction-level quantities and revenue.

• A Product dimension with product names and categories.

• A Customer dimension with customer attributes.

• A Date dimension containing dates, months, quarters, and years.

The fact table stores events or measurements, while dimension tables provide descriptive context.

A well-designed star schema can make relationships easier to understand, improve the maintainability of the model, and support more predictable reporting behaviour.

19. How would you improve a slow Power BI report?

Sample answer:

I would investigate the source of the performance problem before making changes.

My approach would include:

1. Checking whether the data source or refresh process is slow.

2. Reviewing the data model and unnecessary columns or rows.

3. Examining relationships and cardinality.

4. Simplifying expensive DAX calculations where possible.

5. Reducing unnecessarily complex visuals.

6. Using available performance-analysis tools to identify slow queries or visuals.

7. Testing the changes against the original report.

I would avoid assuming that every slow report has the same cause. The appropriate fix depends on the model, data source, calculations, and report design.

20. How would you design a useful business dashboard?

Sample answer:

I would start by understanding who will use the dashboard, what decisions they need to make, and which metrics matter most.

For a sales dashboard, relevant measures might include revenue, profit margin, sales growth, target achievement, and performance by region or product.

I would then:

• Define each KPI and its calculation.

• Validate the source data.

• Design a clear visual hierarchy.

• Choose charts suited to the questions being asked.

• Add useful filters and drill-downs.

• Check readability and accessibility.

• Test the dashboard with representative users.

A dashboard should help users understand what is happening and investigate why. Adding more charts does not automatically make a dashboard more useful.

How to Prepare for a Data Analyst Interview

Learning individual answers is useful, but you also need a repeatable preparation routine.

Step 1: Review the job description

Identify the tools and responsibilities mentioned. Prioritise SQL if the role emphasises databases, or Power BI if dashboard development is a central responsibility.

Step 2: Practise SQL regularly

Write queries involving joins, aggregations, CTEs, window functions, and filtering. Test your queries against sample data and explain the results.

Step 3: Work through practical Excel exercises

Practise lookup formulas, conditional aggregation, PivotTables, data cleaning, and reconciliation tasks.

Step 4: Build a small Python analysis

Choose a dataset, clean it using pandas, investigate a question, and summarise the results. Be prepared to explain your code and analytical decisions.

Step 5: Create a Power BI dashboard

Build a small model with a fact table and relevant dimensions. Create a few meaningful measures and explain how the visuals support a business question.

Step 6: Prepare project explanations

Choose two or three projects and prepare to discuss the problem, your approach, the tools used, the challenges you faced, and the results you can substantiate.

Step 7: Complete a mock interview

Practise answering questions aloud with a partner. Request specific feedback on technical accuracy, reasoning, communication, and clarity.

Repeat the process after addressing your main improvement areas.

A Seven-Day Data Analyst Interview Preparation Plan

If your interview is approaching, this schedule can help you organise your revision.

Adjust this plan according to the job requirements and your existing proficiency. If SQL is your strongest skill but Power BI is unfamiliar, spend more time on the weaker area rather than dividing your time equally.

Practise Data Analyst Interviews With MatchMyInterview

Preparing independently helps you learn technical concepts, but explaining them to another person can reveal gaps that are difficult to spot on your own.

A mock interview gives you an opportunity to practise technical questions, explain your reasoning, respond to follow-ups, and receive feedback before a real hiring process.

For example, you could ask a peer to give you a SQL problem, evaluate your approach, and challenge you to explain edge cases. You could then switch roles and practise interviewing them.

MatchMyInterview is designed around peer mock interviews, structured evaluations, and professional portfolios supported by evidence from completed peer interactions.

Members can build professional profiles, connect with peers, conduct interviews, and exchange written feedback. This helps make practice more structured and gives both participants an opportunity to learn.

Peer evaluations provide feedback on particular interactions. They are not formal professional certifications or guarantees of hiring outcomes.

Frequently Asked Questions

What are the most important data analyst interview topics?

SQL, Excel, data cleaning, statistics, and business problem-solving are common areas. Python and Power BI may also be important depending on the role. Always check the job description.

Are data analyst interviews difficult for freshers?

They can be challenging, particularly when candidates have limited experience applying concepts to practical problems. Freshers can prepare by practising SQL, Excel, basic statistics, project explanations, and realistic interview questions.

Is SQL more important than Python for data analysts?

It depends on the job. SQL is particularly useful when working with relational databases, while Python is useful for data manipulation, automation, and more complex analysis. Some positions require both.

What Python libraries should a data analyst know?

Pandas and NumPy are useful for data manipulation and numerical operations. Matplotlib and other visualisation libraries can help with exploratory analysis. The most relevant libraries depend on the role.

What is commonly tested in Power BI interviews?

Interviewers may assess Power Query, data modelling, DAX, relationships, report performance, and dashboard design. They may also ask you to explain how your reports support business decisions.

How can I practise data analyst interviews online?

You can solve technical questions, work on projects, and practise answering questions aloud. You can also use MatchMyInterview to connect with peers, conduct mock interviews, and exchange structured feedback.

Final Thoughts

A successful data analyst interview requires more than memorising technical definitions. You need to demonstrate that you can work with data, select appropriate tools, validate your results, and communicate findings clearly.

Build a strong foundation in SQL and Excel, practise Python and Power BI where relevant, and prepare to explain your projects in detail.

Most importantly, combine individual study with realistic mock interviews. Use feedback to identify gaps, revise your approach, and practise again.

Ready to put your data analyst skills to the test?

Visit MatchMyInterview to explore peer mock interviews, exchange structured feedback, and build a professional portfolio that brings together your skills and peer-interaction evidence.

Prepare with purpose, practise with others, and keep improving.