Data Analyst Basics
1. What does a data analyst do? basic
A data analyst collects, cleans and studies data to answer business questions. The job usually includes pulling data with SQL or Excel, finding patterns, building reports and dashboards, and explaining the findings in plain language so managers can decide what to do.
2. What are the main steps of a data analysis project? basic
The usual steps are: define the question, collect the data, clean it, explore it, analyse it, visualise the results, and share the findings with a clear recommendation. Many projects loop back to earlier steps when the data raises new questions.
3. What is the difference between a data analyst, a data scientist and a data engineer? basic
A data analyst explains what happened and why using existing data. A data scientist builds models to predict or classify, often with machine learning. A data engineer builds the pipelines and databases that deliver clean data to both of them.
4. What is the difference between data and information? basic
Data is raw facts, like a list of order values. Information is data that has been processed so it has meaning, like the average order value by month. Analysts turn data into information, and then into decisions.
5. What is the difference between structured, semi-structured and unstructured data? basic
Structured data fits into rows and columns, like a SQL table. Semi-structured data has tags or keys but no fixed table, like JSON or XML. Unstructured data has no set format, like emails, images, PDFs and free text.
6. What is the difference between quantitative and qualitative data? basic
Quantitative data is numeric and can be measured, like sales or age. Qualitative data describes qualities or categories, like colour, feedback comments or customer type. Good analysis often uses both.
7. What are KPIs and how do you choose them? basic
A KPI (key performance indicator) is a measurable number that shows progress toward a business goal. Choose KPIs that are tied to a goal, easy to measure, hard to game, and understood by the people who act on them. A few good KPIs beat dozens of weak ones.
8. What is the difference between a metric and a KPI? basic
A metric is any number you track, such as page views. A KPI is a metric that is directly linked to a key goal, such as the conversion rate for a sales target. Every KPI is a metric, but not every metric is a KPI.
9. What is a dashboard and who uses it? basic
A dashboard is a single screen that shows key numbers and charts so people can monitor performance quickly. Managers and teams use it to spot problems early, track targets and decide where to look deeper.
10. What is the difference between a report and a dashboard? basic
A report is usually a static document or table for a set period, often with written explanation. A dashboard is interactive or live, shows the current state, and lets users filter and drill down. Reports explain, dashboards monitor.
11. What are the most common tools used by data analysts? basic
Excel for quick analysis, SQL for pulling data from databases, a BI tool such as Power BI or Tableau for dashboards, and often Python or R for deeper analysis. Soft tools matter too, like documenting your work and presenting clearly.
12. Why is data cleaning important? basic
Dirty data leads to wrong answers. Duplicates, missing values, typos and mixed formats can change totals and averages. Analysts often spend most of a project cleaning data, because a clean data set is the base for every result.
13. What is data wrangling or data munging? basic
It is the process of transforming raw data into a usable format. It includes cleaning, reshaping, merging tables, changing data types and creating new columns, so the data is ready for analysis.
14. What is the difference between descriptive, diagnostic, predictive and prescriptive analytics? basic
Descriptive explains what happened. Diagnostic explains why it happened. Predictive estimates what is likely to happen next. Prescriptive suggests what action to take. Most analyst work is descriptive and diagnostic.
15. What is a data warehouse? basic
A data warehouse is a central database that stores cleaned, historical data from many sources, organised for reporting and analysis. Analysts query it instead of the busy systems that run the daily business.
16. What is a database and what is a table? basic
A database is an organised collection of data. A table is a set of rows and columns inside a database, where each row is a record and each column is a field such as name or date.
17. What is a primary key and a foreign key? basic
A primary key is a column (or set of columns) that uniquely identifies each row in a table. A foreign key is a column that points to the primary key of another table, which creates the link between the two tables.
Data Cleaning and Preparation
18. How do you handle missing values? basic
First find out why they are missing. Then choose: remove the rows if few and random, fill with a sensible value such as the mean, median or a default, or flag them so they are not mistaken for real zeros. Always document what you did, because it can change the results.
19. When would you use the median instead of the mean to fill missing values? intermediate
Use the median when the data has outliers or is skewed, such as salaries or order values, because a few extreme values pull the mean away from the typical value. For symmetrical data without outliers, the mean is fine.
20. How do you find and remove duplicates? basic
Decide which columns define a duplicate, such as order ID or customer plus date. In SQL use GROUP BY with HAVING COUNT(*) > 1 to find them, or ROW_NUMBER() to keep only one. In Excel use Remove Duplicates or conditional formatting.
21. What is an outlier and how do you deal with it? basic
An outlier is a value far from the rest of the data. First check whether it is an error, such as an extra zero. If it is real, keep it but consider reporting with and without it, or use the median. Remove it only when you can explain why.
22. What methods can you use to detect outliers? intermediate
Common methods are the IQR rule (values below Q1 minus 1.5 x IQR or above Q3 plus 1.5 x IQR), the z-score (values more than 3 standard deviations from the mean), and visual checks such as box plots and scatter plots.
23. How do you check data quality? basic
Check for missing values, duplicates, wrong data types, values outside a valid range, inconsistent spelling or formats, and whether totals match the source system. Simple summary counts and min/max checks catch most problems.
24. What is data validation and where can you apply it? intermediate
Data validation is checking that data is correct and follows rules, such as a date in the right format or a quantity above zero. You can apply it when data is entered (drop-downs, constraints), when it is loaded (ETL checks) and before reporting (reconciliation checks).
25. What is data normalisation in the database sense? basic
It means organising tables to reduce repeated data, for example storing customer details once in a customer table and linking orders to it with an ID. This avoids update mistakes and saves space.
26. What is the difference between normalisation in databases and normalisation in analysis? intermediate
In databases, normalisation means splitting data into related tables to remove repetition. In analysis, normalisation (or scaling) means changing numeric values to a common range, such as 0 to 1, so columns can be compared fairly.
27. How do you standardise text values such as city names? basic
Trim extra spaces, make the case consistent, fix spelling variants with a mapping table, and replace known abbreviations. In Excel you can use TRIM, UPPER and lookups. In SQL use TRIM, UPPER and a mapping table or CASE.
28. How do you deal with dates stored as text? basic
Convert them to real date values using the correct format, then check the result for errors. In Excel use DATEVALUE or Text to Columns. In SQL use CAST or TO_DATE depending on the database. Always check for mixed formats such as day-month versus month-day.
29. What is the difference between wide and long data, and why does it matter? intermediate
Wide data has one row per item and many columns for values, such as one column per month. Long data has one row per observation, such as one row per item per month. Most analysis and charting tools work better with long data, so you often unpivot wide data.
30. What is data profiling? intermediate
Data profiling is a quick study of a data set to understand its structure and quality: column types, counts of distinct and missing values, min, max, and common values. It is a smart first step before any analysis.
31. How do you merge data from several sources? intermediate
Find a common key such as customer ID, check that it is clean and unique in each source, then join the tables. Check the row count before and after the join to catch accidental duplicates or lost rows, and reconcile totals with the sources.
Statistics for Analysts
32. What are mean, median and mode? basic
The mean is the average, the median is the middle value when the data is sorted, and the mode is the most common value. The median is better when there are outliers, and the mode is useful for categories.
33. What is standard deviation and what does it tell you? basic
Standard deviation measures how spread out values are around the mean. A small value means the data is close to the average. A large value means it is widely spread. It helps you judge how typical the average really is.
34. What is the difference between variance and standard deviation? basic
Variance is the average of the squared differences from the mean. Standard deviation is the square root of the variance, so it is in the same units as the data and easier to explain.
35. What is a normal distribution? basic
A normal distribution is a bell-shaped curve that is symmetrical around the mean. About 68% of values fall within 1 standard deviation, 95% within 2, and 99.7% within 3. Many natural measures follow it approximately.
36. What is skewness? intermediate
Skewness shows whether a distribution leans to one side. Right (positive) skew has a long tail of high values, like incomes. Left (negative) skew has a long tail of low values. Skewed data usually needs the median instead of the mean.
37. What is correlation and what does it not tell you? basic
Correlation measures how strongly two variables move together, from -1 to +1. It does not prove that one causes the other. Two things can move together because of a third factor or by chance.
38. What is the difference between correlation and causation? basic
Correlation means two things move together. Causation means one thing makes the other happen. To show causation you need a controlled test, such as an A/B test, or strong evidence that rules out other explanations.
39. What is a p-value? intermediate
A p-value is the probability of seeing a result at least as extreme as yours if the null hypothesis were true. A small p-value (often below 0.05) suggests the result is unlikely to be pure chance. It does not measure the size or importance of the effect.
40. What is a null hypothesis and an alternative hypothesis? intermediate
The null hypothesis says there is no effect or no difference, for example that a new page has the same conversion rate as the old page. The alternative says there is an effect. A test checks whether the data gives enough evidence to reject the null.
41. What are Type I and Type II errors? intermediate
A Type I error is a false positive: you reject the null hypothesis when it is actually true. A Type II error is a false negative: you fail to reject it when it is false. Lowering one usually raises the other.
42. What is a confidence interval? intermediate
A confidence interval is a range that is likely to contain the true value. A 95% interval means that if you repeated the study many times, about 95% of the intervals would contain the true value.
43. What is sampling and why is it used? basic
Sampling means studying a part of a population instead of all of it, to save time and cost. A good sample is chosen randomly or in a way that represents the population, so the results can be applied to everyone.
44. What are common sampling methods? intermediate
Simple random sampling gives everyone an equal chance. Stratified sampling splits the population into groups and samples each. Systematic sampling picks every nth item. Cluster sampling picks whole groups. Convenience sampling is easy but can be biased.
45. What is sampling bias? intermediate
Sampling bias happens when the sample does not represent the population, so the results are skewed. For example, surveying only online customers about store service. Random selection and checking who is missing reduce the problem.
46. What is the central limit theorem? intermediate
It says that the average of many random samples will be close to normally distributed, even if the original data is not, as long as the samples are large enough. It is why many statistical tests and confidence intervals work.
47. What is regression analysis? intermediate
Regression finds the relationship between a dependent variable and one or more independent variables. Linear regression fits a straight line, for example predicting sales from ad spend. It helps explain and predict, but does not prove causation.
48. What does R-squared mean? intermediate
R-squared shows how much of the variation in the outcome is explained by the model, from 0 to 1. An R-squared of 0.8 means 80% of the variation is explained. A high value does not always mean a good or reliable model.
49. What is multicollinearity? advanced
Multicollinearity is when two or more predictors in a regression are strongly correlated with each other. It makes the coefficients unstable and hard to interpret. You can detect it with correlation tables or variance inflation factor (VIF), and fix it by removing or combining variables.
A/B Testing and Business Analysis
50. What is an A/B test? intermediate
An A/B test compares two versions, such as the old and new web page, by showing each to a random group of users and measuring a target metric. Random assignment makes it possible to say that a difference was caused by the change.
51. How do you design an A/B test? intermediate
Define a clear hypothesis and one main metric, decide the sample size needed, split users randomly, run the test for a full business cycle without peeking early, and then check statistical significance and the size of the effect before deciding.
52. Why should you not stop an A/B test as soon as it looks significant? advanced
Checking repeatedly and stopping at the first significant result inflates false positives. Decide the sample size or duration in advance, and run the test for at least one full week so that day-of-week patterns are included.
53. What is statistical significance and practical significance? intermediate
Statistical significance means the result is unlikely to be random. Practical significance means the effect is large enough to matter to the business. With big samples, tiny differences can be statistically significant but not worth acting on.
54. What is a conversion rate and how do you calculate it? intermediate
Conversion rate is the share of visitors or leads that complete a desired action. Divide the number of conversions by the number of visitors and multiply by 100. For example, 50 purchases from 2,000 visitors is 2.5%.
55. What is churn and how do you measure it? intermediate
Churn is the share of customers who stop using a product in a period. Divide the customers lost in the period by the customers at the start of the period. Retention is the opposite: the share who stay.
56. What is customer lifetime value (CLV)? intermediate
CLV is the total profit you expect from a customer over the whole relationship. A simple version is average order value x purchases per year x years as a customer x profit margin. It helps decide how much to spend to acquire a customer.
57. What is cohort analysis? intermediate
Cohort analysis groups users by a shared start point, such as signup month, and tracks their behaviour over time. It shows whether retention or spend is improving for newer groups, which averages can hide.
58. What is funnel analysis? intermediate
Funnel analysis follows users through a series of steps, such as visit, add to cart, checkout and purchase, and measures how many drop off at each step. It shows where to focus improvements.
59. How would you investigate a sudden drop in sales? advanced
Confirm the drop is real by checking data quality and tracking issues. Then break it down by time, region, product, channel and customer type to find where it is concentrated. Check for outside causes such as holidays, price changes, outages or campaigns, then form and test explanations.
60. How would you decide which metric to track for a new product feature? advanced
Start from the goal of the feature, pick one main metric that reflects it (such as feature adoption or task completion), add a few supporting metrics, and add a guardrail metric, such as support tickets, to make sure nothing important gets worse.
61. What is seasonality and how do you handle it in analysis? intermediate
Seasonality is a pattern that repeats at regular times, like higher retail sales in December. Compare the same period year on year, or use moving averages and seasonal adjustment, so you do not mistake normal seasonal swings for real changes.
62. What is a moving average and why is it used? intermediate
A moving average takes the average of the last few periods, such as the last 7 days. It smooths short-term noise so the trend is easier to see.
63. What is the difference between YoY, MoM and QoQ growth? intermediate
They compare a period with an earlier one: year over year, month over month and quarter over quarter. Growth percent is (current - previous) / previous. YoY avoids seasonality, while MoM shows the latest momentum.
Data Visualization and Reporting
64. How do you choose the right chart? basic
Use a line chart for trends over time, bars to compare categories, a scatter plot for relationships between two numbers, a histogram for distribution, and a table when exact numbers matter. Use pie charts only for a few parts of a whole.
65. What are the best practices for a good dashboard? basic
Keep it simple, show the most important numbers first, use consistent colours and formats, label clearly, limit the number of charts, add filters only where they help, and design for the question the viewer wants to answer.
66. Why can pie charts be a poor choice? basic
People find it hard to compare angles and areas, especially with many slices or similar values. A sorted bar chart is usually easier to read.
67. What is the difference between a histogram and a bar chart? intermediate
A histogram shows the distribution of one numeric variable by grouping values into ranges (bins), and the bars touch. A bar chart compares separate categories, and the bars have gaps.
68. What is a box plot used for? basic
A box plot shows the median, the quartiles and the spread of the data, and marks outliers. It is useful for comparing distributions across groups.
69. What makes a visualisation misleading? intermediate
Common problems include a truncated axis that exaggerates a difference, 3D effects, too many colours, mixed scales, cherry-picked time ranges and missing labels. Keep axes honest and let the data speak.
70. How do you present findings to a non-technical audience? basic
Start with the answer or recommendation, then show one or two supporting visuals. Use plain words instead of jargon, explain what the number means for the business, and be ready with details for questions.
71. What is data storytelling? intermediate
Data storytelling combines data, visuals and a narrative to explain what is happening, why it matters and what to do next. A good story has a clear message, a logical order and a call to action.
72. What is the difference between a measure and a dimension? intermediate
A measure is a number you can aggregate, such as revenue or quantity. A dimension is a category used to group or filter measures, such as region, product or month.
73. What is drill-down and drill-through in reporting? intermediate
Drill-down moves from a summary to more detail within the same view, such as year to quarter to month. Drill-through jumps to a different page or report filtered to the selected item.
74. How do you keep a recurring report accurate? intermediate
Automate the data load, add reconciliation checks against the source, keep one clear definition for each metric, document the logic, and review changes with the report users before release.
SQL, Python and Excel for Analysts
75. Which SQL skills do analysts need most? basic
Joins, GROUP BY with aggregate functions, WHERE and HAVING, CASE WHEN, subqueries and CTEs, and window functions. These cover most daily work such as summaries, cohorts and rankings.
76. How do you analyse data in Excel? basic
Clean the data, turn it into a table, use formulas like SUMIFS, COUNTIFS and XLOOKUP for calculations, build PivotTables for summaries, and add charts or slicers for presentation.
77. When would you use Excel and when would you use SQL? intermediate
Use Excel for small data sets, quick checks and flexible presentation. Use SQL for large data in a database, repeatable queries and joins across many tables. Many analysts pull data with SQL and finish the analysis in Excel or a BI tool.
78. What is a PivotTable and when is it better than formulas? intermediate
A PivotTable summarises data by dragging fields into rows, columns and values. It is faster than writing many SUMIFS formulas for exploring data, and it updates after a refresh. Formulas are better when you need a fixed layout.
79. What Python libraries do data analysts use? basic
Pandas for data tables, NumPy for numeric calculations, Matplotlib and Seaborn for charts, and sometimes SciPy or statsmodels for statistics.
80. What is a pandas DataFrame? intermediate
A DataFrame is a two-dimensional table in Python with labelled rows and columns, like a spreadsheet. A Series is a single column of a DataFrame.
81. How do you handle large files that are too big for Excel? intermediate
Load the data into a database and query it with SQL, use Power Query or Power BI, or process it in chunks with Python. Excel has a limit of just over one million rows per sheet.
82. What is the difference between ETL and ELT? intermediate
ETL extracts data, transforms it, then loads it into the warehouse. ELT loads the raw data first and transforms it inside the warehouse. ELT is common with modern cloud warehouses that can handle heavy processing.
83. What is the difference between OLTP and OLAP? intermediate
OLTP systems handle many small, fast transactions, such as taking orders. OLAP systems are built for analysing large amounts of historical data with complex queries, such as a data warehouse.
84. What is version control and why do analysts use it? intermediate
Version control, such as Git, records changes to code and files over time. It lets you track what changed, go back to an earlier version and work with others without overwriting each other's work.
85. What is a data lake and how is it different from a data warehouse? advanced
A data lake stores large amounts of raw data in any format, cheaply, for later use. A data warehouse stores cleaned and structured data for reporting. Lakes are flexible, warehouses are reliable and fast for analysis.
Scenario and Interview Questions
86. Tell me about a project where your analysis changed a decision. intermediate
Pick a real example. Describe the business problem, the data you used, what you found, and the decision that followed with a measurable result. Interviewers want to hear that your work had impact, not only that you built a report.
87. How do you prioritise when several people ask you for reports at the same time? intermediate
Ask about the business impact and deadline of each request, agree priorities with your manager or the requesters, do quick wins first, and tell people honestly when their request will be done. Clear communication prevents most conflicts.
88. What would you do if two data sources give different numbers? intermediate
Compare definitions and filters first, such as date ranges and what counts as a sale. Check for timing differences, duplicates and missing records. Trace both numbers back to the raw data, find the cause, and agree with stakeholders on which source is the official one.
89. How would you check that a new dashboard is correct before releasing it? advanced
Compare totals with the source system for several periods, test with a few records by hand, check filters and edge cases such as blanks and zero values, and ask a business user to confirm that the numbers match what they know.
90. How do you explain a complex analysis to a senior manager in two minutes? intermediate
Lead with the conclusion and its impact, give the two or three facts that support it, state any limits or assumptions briefly, and finish with the action you recommend. Keep the detail for follow-up questions.
91. How would you measure the success of a marketing campaign? advanced
Agree the goal first, such as sales, leads or sign-ups. Compare results with a baseline or control group, calculate cost per acquisition and return on spend, and check whether the effect lasted or only pulled demand forward.
92. What would you do if a stakeholder asks you to change an analysis to get a result they want? advanced
Listen to their reasons, explain what the data actually shows, and offer to look at it from other valid angles. Do not change the method just to get a desired answer. Be honest about the limits and escalate if needed.
93. How do you stay up to date with data analytics tools? intermediate
Follow a few trusted blogs and communities, practise new features on small projects, take short courses, and try tools on real work problems. Learning by doing sticks better than reading alone.
94. What is the first thing you do when you get a new data set? intermediate
Look at its size and structure, check the column names and types, count missing and duplicate values, and view a sample of rows and summary statistics. Then confirm with the data owner what each column means.
95. How would you forecast next quarter's sales using simple methods? advanced
Look at the trend and seasonality in past data, then use a moving average, year-on-year growth applied to last year's same quarter, or a regression on known drivers. Compare the forecast with past actuals to check accuracy and show a range, not only a single number.
96. How do you handle a data set with millions of rows and slow queries? advanced
Filter and aggregate as early as possible, select only needed columns, use indexes and partitions where available, avoid unnecessary joins, and pre-aggregate common results into summary tables.
