Data Analyst Interview Questions and Answers for Freshers and Experienced

A data analyst collects, cleans and analyses data to answer business questions and support decisions. Day to day, that means writing SQL queries, building reports and dashboards, tracking KPIs and explaining trends to managers. For example, an analyst at a food delivery startup might find out why repeat orders fell in Pune last month. The role sits between raw data and business teams, so communication matters as much as technical skill.

A typical process has six steps: define the question, collect the data, clean it, analyse it, visualise the results and communicate a recommendation. The first step matters most, because a vague question leads to a useless analysis. In practice the process loops: cleaning often reveals gaps that change the question, and stakeholders ask follow-ups after seeing the first chart. Interviewers like it when you say the loop is normal, not a failure.

Data cleaning is fixing or removing incorrect, incomplete, duplicate or inconsistent data before analysis. I check for missing values, duplicate rows, wrong data types, inconsistent labels (Bengaluru, Bangalore and BLR in the same column), impossible values like negative quantities, and outliers. Each fix should be documented so others can repeat it. Cleaning often takes more time than the analysis itself, and skipping it is the fastest way to present wrong numbers.

Use the mean for roughly symmetric data without extreme values, the median for skewed data or data with outliers, and the mode for categorical data or the most common value. Order values on an e-commerce site are usually skewed by a few bulk orders, so the median gives a better picture of a typical order. Pair any average with a measure of spread, such as standard deviation or interquartile range, so the reader sees the variation too.

Structured data fits neatly into rows and columns with a fixed schema, like a sales table in a database. Unstructured data has no fixed format, such as customer reviews, emails, images or call recordings. Semi-structured data, like JSON from an app, sits in between. Analysts mostly work with structured data in SQL and Excel, but AI tools now make it easier to pull structure out of text, for example tagging reviews by topic and sentiment.

A KPI (key performance indicator) is a metric that shows how well a business is doing against a specific goal. Good KPIs are tied to a decision, measured consistently and owned by someone. Examples include monthly active users for an app, conversion rate for an e-commerce site, average delivery time for a quick-commerce company and loan default rate for a lender. A metric that nobody acts on is just a number, not a KPI.

An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there is no match. A RIGHT JOIN does the opposite, and a FULL OUTER JOIN returns all rows from both. A CROSS JOIN returns every combination. To find customers who never ordered, LEFT JOIN customers to orders and keep rows where the order ID is NULL.

Filter rows with WHERE, group with GROUP BY, then filter groups with HAVING.

SELECT category,
       SUM(amount) AS total_sales,
       COUNT(order_id) AS orders
FROM orders
WHERE status = 'delivered'
GROUP BY category
HAVING SUM(amount) > 100000
ORDER BY total_sales DESC;

WHERE removes cancelled orders before grouping, and HAVING keeps only large categories after aggregation. You cannot put SUM inside WHERE.

Window functions calculate a value across a set of related rows without collapsing them into one row, unlike GROUP BY. You define the window with OVER, often with PARTITION BY and ORDER BY. For scores of 90, 90 and 80, ROW_NUMBER gives 1, 2, 3; RANK gives 1, 1, 3, skipping a rank after the tie; and DENSE_RANK gives 1, 1, 2 with no gap. Use them for top-N per group, running totals and comparisons with previous rows.

Group by the columns that should be unique and keep groups with more than one row.

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

To remove duplicates while keeping one copy, number the rows with ROW_NUMBER partitioned by email and delete or exclude rows where the number is above 1. Always confirm with the business which record to keep, such as the latest one.

Aggregate revenue by month in a CTE, then use LAG to compare each month with the previous one.

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       (revenue - LAG(revenue) OVER (ORDER BY month))
         / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) AS mom_growth
FROM monthly
ORDER BY month;

DATE_TRUNC is PostgreSQL syntax; MySQL and SQL Server use different date functions. NULLIF avoids division by zero.

UNION combines the results of two queries and removes duplicate rows, while UNION ALL keeps every row, including duplicates. Because UNION has to check for duplicates, it is usually slower on large data. Use UNION ALL when you know the sets do not overlap or when duplicates are meaningful, such as stacking January and February transaction tables. Both queries must return the same number of columns with compatible data types.

A CTE (common table expression) is a named temporary result defined with WITH at the start of a query, which you can then reference like a table. Use CTEs to break a complex query into readable steps, such as first calculating monthly revenue and then computing growth. Recursive CTEs can also walk hierarchies like an employee and manager chain. In interviews, CTEs make your logic easier to follow, which interviewers notice.

VLOOKUP searches the first column of a range and returns a value from a column to its right; it defaults to approximate match, which causes many silent errors. INDEX with MATCH can look left or right and works in older Excel versions. XLOOKUP, available in Microsoft 365 and Excel 2021 onwards, looks in any direction, defaults to exact match and lets you set a value for not-found cases. If the company uses an older Excel version, know INDEX MATCH well.

A pivot table summarises large data by grouping and aggregating it, for example total sales by region and month, without writing formulas. You drag fields into rows, columns, values and filters, and Excel recalculates instantly. Use it for quick exploration, checking totals and answering ad hoc questions from managers. Remember to refresh it after the source data changes, and convert the source to an Excel table so new rows are included automatically.

Choose the chart based on the question you are answering. Use a line chart for trends over time, a bar chart for comparing categories, a scatter plot for relationships between two numbers, a histogram for distributions and a table when exact values matter. Avoid pie charts with more than a few slices and 3D effects that distort size. A good test: can someone get the main point within five seconds?

A calculated column is computed row by row when the data refreshes and is stored in the model, like a profit column equal to revenue minus cost. A measure is calculated at query time based on the current filters in a visual, like total profit for whichever region the user selects. Measures are usually preferred for aggregations because they respond to filters and do not increase model size. Both are written in DAX.

Both are strong BI tools, and the right choice usually depends on what the company already uses. Power BI fits naturally into organisations on Microsoft 365 and is often cheaper to roll out widely. Tableau is known for flexible, polished visual exploration. The core skills transfer: data modelling, choosing the right chart and designing for the audience. In an interview, say which one you know best and show you could pick up the other quickly.

Start by asking what decisions they make and which three to five KPIs support those decisions. Put the most important numbers at the top with comparisons to target and last period, then add trend charts and only the breakdowns they act on. Keep filters simple, label everything clearly and note the data refresh time. I would rather ship a dashboard with five trusted numbers than one with fifty that nobody reads.

First, confirm the data is correct: check for pipeline delays, tracking changes or a different date range. Then compare against seasonality, for example the previous festive season. Break sales into orders multiplied by average order value, and segment by region, channel, product category and new versus repeat customers to find where the drop sits. Check internal changes like price increases or stock-outs, and external ones like competitor sales. Finish with the likely cause and a recommended action.

I would group metrics by funnel stage. For growth: new users and orders per day. For conversion: app open to order rate and checkout drop-off. For operations: average delivery time, on-time percentage and cancellation rate. For economics: average order value and cost per delivery. For retention: repeat order rate within 30 days. Then pick one north star metric, such as weekly orders per active user, and explain why it reflects real customer value.

Cohort analysis groups users by a shared starting point, usually their sign-up or first purchase month, and tracks their behaviour over time. A retention table shows what percentage of each cohort is still active after one, two or three months. It reveals whether newer users stick better than older ones, which overall active-user totals hide. For example, a cohort acquired through heavy cashback might churn faster once the offer ends.

Stay calm and treat it as a data question, not a personal one. Ask which number they expected and where their figure comes from. Most disagreements come from different definitions, filters or date ranges, such as gross versus net sales. Walk through your query logic together, reconcile the two numbers and document the agreed definition. If you did make an error, own it quickly and fix the source so it does not repeat.

Excel has a limit of just over one million rows per sheet, so larger data needs other tools. Push the heavy work to the database with SQL, using filters and aggregations so you only export a summary. Alternatively, use Power Query or the Power BI data model, or Python with pandas for files that fit in memory. The principle is to bring the computation to the data instead of moving huge raw files around.

I use AI assistants to speed up drafting, not to replace thinking. Typical uses are writing a first draft of a query from the table schema, explaining an unfamiliar query, suggesting Excel formulas, generating Python for a chart and summarising findings for an email. Good prompt engineering helps: share the schema, the business definition and an example output. Then I review and test everything before it reaches a report.

I check AI output the way I would check a new teammate's work. Confirm the tables, columns and join keys exist and match the schema, run the query on a small sample where the answer is known, and compare totals with a trusted report. Look for silent errors such as duplicate rows after a join or wrong date filters. Large language models can hallucinate column names and confident explanations, so never present an AI insight you cannot trace back to data.

Major BI tools now include AI assistants that can create visuals or answer questions from natural language, summarise dashboards in text and suggest insights or anomalies. Examples include Copilot in Power BI and Tableau's AI features such as Tableau Pulse. Availability depends on licence, region and admin settings, and these features change quickly, so check current documentation. The analyst's job remains defining metrics correctly, because AI summaries are only as good as the underlying data model.

The main risk is sharing confidential or personal data, such as customer names, phone numbers or financial details, with a tool the company has not approved. Some tools may store or process inputs outside your control. Follow company policy, use approved enterprise tools, share schemas or masked samples instead of raw data, and aggregate before asking for help. In India, the Digital Personal Data Protection Act, 2023 adds legal obligations around personal data, so check with your compliance team.