Data Analyst interview questions and answers

WHERE filters individual rows before they are grouped; HAVING filters the groups after aggregation, so it can use SUM, COUNT and the like. For this question:

SELECT city, SUM(amount) AS total_sales FROM orders GROUP BY city HAVING SUM(amount) > 100000;

If you also wanted only delivered orders, that condition goes in WHERE (WHERE status = 'Delivered'), because it applies to each row, not to a city's total. Most databases won't even accept it in HAVING unless status is in the GROUP BY, and filtering rows early is faster. Interviewers often follow up by asking you to sort the result with ORDER BY total_sales DESC.

COUNT(*) counts every row, whatever it contains. COUNT(column) counts only the rows where that column is not NULL. COUNT(DISTINCT column) counts the different non-NULL values. So in an orders table, COUNT(*) is the number of orders, COUNT(coupon_code) is the orders that used a coupon, and COUNT(DISTINCT customer_id) is the number of customers who ordered.

NULLs catch people elsewhere too: AVG ignores them, so the average is over the non-NULL rows only, and NULL = NULL is not true, so you must write IS NULL. Use COALESCE(column, 0) when a missing value should really count as zero.

One clear way uses NOT EXISTS: select the distinct customers with an order dated in January, where no order exists for the same customer in February. In SQL: SELECT DISTINCT o.customer_id FROM orders o WHERE o.order_date >= '2026-01-01' AND o.order_date < '2026-02-01' AND NOT EXISTS (SELECT 1 FROM orders f WHERE f.customer_id = o.customer_id AND f.order_date >= '2026-02-01' AND f.order_date < '2026-03-01').

Two things go wrong often. NOT IN returns no rows at all if the subquery has a NULL customer_id. And filtering dates with BETWEEN on a datetime column misses orders later on the last day; a half-open range avoids that.

Almost certainly a fan-out: the join isn't one-to-one. If an order can have several payment rows (a failed attempt then a success, or a split payment), each order row is repeated once per payment, and summing order amounts counts it several times.

Check the grain first: compare COUNT(*) with COUNT(DISTINCT order_id) after the join. Then fix it by aggregating the many side before joining (sum payments per order in a subquery or CTE, then join), or by filtering to the one row you need, such as successful payments only. Make it a habit to check row counts before and after every join; interviewers like hearing that.

loc selects by label: row index labels and column names. iloc selects by integer position, like a list. So df.loc[df['city'] == 'Pune', ['name', 'amount']] gives the name and amount of Pune's rows, while df.iloc[0:5, 0:2] gives the first five rows and first two columns.

One detail interviewers check: slicing with loc includes the end label (df.loc['a':'c'] includes 'c'), while iloc excludes the end position, as Python slices do. Also, after filtering, the index keeps its old labels, so loc[0] may not be the first row any more; use iloc[0] or reset_index() when you mean position.

Group by region and aggregate the amount: df.groupby('region')['amount'].agg(['sum', 'mean']). For clearer column names, use named aggregation: df.groupby('region').agg(total=('amount', 'sum'), average=('amount', 'mean'), orders=('order_id', 'count')).

The result has region as its index; add .reset_index() if you want it back as a normal column for exporting or merging. Sort it with .sort_values('total', ascending=False) to show the top regions first. Mention that groupby drops rows where the group key is NaN by default, so check for missing regions first, or pass dropna=False.

The how argument works like SQL joins: 'inner' keeps only matching keys, 'left' keeps every row of the left table, 'outer' keeps everything. For a customer report including people who never ordered, use pd.merge(customers, orders, on='customer_id', how='left').

Then check it. Pass validate='one_to_many' so pandas raises an error if customer_id is unexpectedly duplicated in customers. Pass indicator=True to get a _merge column showing 'left_only', 'right_only' or 'both', which quickly finds orders whose customer is missing. And compare row counts before and after. Key columns with different types, such as text in one table and numbers in the other, are a common cause of a silent empty match.

First make a month column from the date: df['month'] = df['order_date'].dt.to_period('M'), after making sure order_date is a real datetime. Then use pivot_table: df.pivot_table(index='month', columns='region', values='amount', aggfunc='sum', fill_value=0). Add margins=True if you want row and column totals.

Use pivot_table rather than pivot here: pivot only reshapes and raises an error when a month and region combination appears more than once, while pivot_table aggregates duplicates. A month with no sales in a region would be NaN without fill_value, so filling with 0 makes the table correct for totals and charts.

A KPI is a metric the business has chosen to track because it shows progress towards a goal. Every KPI is a metric, but most metrics aren't KPIs. For a food delivery app: orders per day, average delivery time, and repeat-order rate.

Take repeat-order rate: the share of customers who ordered in a month and ordered again within the next 30 days, so customers with a repeat order divided by all ordering customers. A good answer defines it exactly, because "repeat customer" can be counted in several ways and teams often disagree on the numbers for that reason. Mention also who would act on the KPI and how often it's reviewed.

A lagging metric records an outcome after it has happened, such as monthly revenue or customer churn. It's what the business finally cares about, but by the time it moves it's too late to change it. A leading metric moves earlier and predicts the outcome, so you can act on it: for a subscription app, the share of new users who complete their profile in the first week, or how often active users open the app.

Good dashboards pair the two: the lagging result alongside the leading signals the team can influence. Say that a leading metric must be shown to predict the outcome in your data, not just assumed to.

First check the data is right: is yesterday fully loaded, did tracking or a pipeline break, did a definition change? Many drops are data problems. Then check whether it's real by comparing with the same weekday last week, since weekdays and festivals swing orders a lot.

If it's real, break it down. Look at the funnel (visits, product views, add to cart, checkout, payment success) to find which step fell, then split by platform, app version, region, payment method and traffic source. A drop only on Android after a release, or only in UPI payments, points straight to the cause. Finally check outside events like a competitor sale or an outage, and report what you found with the evidence.

A cohort is a group of users who share a starting point, usually the month they signed up or first purchased. Cohort analysis follows each group over time, so you compare like with like instead of mixing new and old users in one average.

For a retention table: give each user a cohort month (their first activity), then for each later activity compute the months since that start. Count distinct active users per cohort and month offset, and divide by the cohort's size. Each row is a cohort, each column is month 0, 1, 2 and so on. Reading down a column shows whether newer cohorts retain better, which tells you if product changes are working.

Click inside the data, then Insert, PivotTable, and choose where to place it. Rows lists the categories down the side, such as regions. Columns spreads categories across the top, such as months. Values holds the numbers being summarised, summed by default for numbers, and you can change it to count, average, maximum or percent of total. Filters limits the whole pivot, for example to one product line.

A useful habit to mention: first convert the data to an Excel Table (Ctrl + T), so the pivot's source grows automatically when new rows are added and a Refresh picks them up.

A slicer is a set of buttons for filtering a pivot table or pivot chart. It does the same job as the Filters area, but it shows clearly which items are selected and is easier for a manager to click. The big advantage is that one slicer can control several pivot tables at once: right-click the slicer and use Report Connections to link every pivot built on the same data source.

For dates, a Timeline is the equivalent, letting users pick months, quarters or years with a slider. Slicers and timelines make a simple Excel dashboard without any formulas or code.

Add a calculated field: PivotTable Analyze, Fields, Items and Sets, Calculated Field, with the formula = Profit / Sales, formatted as a percentage. A calculated field works on the summed values, so each region shows its total profit divided by its total sales, which is the correct margin.

Averaging a row-level margin column gives every order equal weight, so a ₹100 order with a 50% margin counts as much as a ₹1 lakh order with 5%, and the result is wrong. The same applies to any ratio: sum the parts, then divide. In the Data Model you would write this as a DAX measure instead.

First measure them: df.isna().sum() per column, and look at which rows are missing, because the reason matters. A missing discount may simply mean no discount (fill with 0), while a missing age is unknown.

The main options: drop rows (dropna) if few rows are affected and they're missing at random; fill with a sensible value such as the median for a skewed number, the mode or "Unknown" for a category, or the previous value for a time series (ffill); or leave them and add a flag column, so the analysis can show them separately. Never fill blindly with the mean before checking, and always note what you did in your report.

Use pd.to_datetime, and be explicit about the format, because 05/03/2026 is 5 March in India but 3 May in the US. Pass format='%d/%m/%Y', or dayfirst=True if the format varies. Add errors='coerce' so values that can't be parsed become NaT instead of stopping the code, then count and inspect those rows rather than ignoring them.

After converting, check the result: the minimum and maximum dates should make sense, with no orders in the future or from 1900. Once it is a real datetime you can use .dt.month, .dt.day_name() or resample for monthly totals.

Standardise first, then remove duplicates. Clean the text: df['name'] = df['name'].str.strip().str.lower(), collapse repeated spaces with str.replace(r'\s+', ' ', regex=True), and normalise phone numbers by removing spaces, dashes and the +91 prefix so they compare equally.

Then decide what makes two rows the same customer. A cleaned phone number or email is usually a better key than a name. Use df.duplicated(subset=['phone']) to review them first, then drop_duplicates keeping the most recent record, after sorting by the update date. Report how many rows were merged; removing duplicates changes counts, and stakeholders should know why.

Start by looking: a box plot or histogram, and describe() to see the extremes. A common rule is the IQR method: anything below Q1 - 1.5 × IQR or above Q3 + 1.5 × IQR, where IQR = Q3 - Q1. A z-score above 3 also works for roughly normal data, but order amounts are usually skewed, so the IQR rule is safer.

Then investigate before acting. A ₹0 order may be a test or a fully discounted order, and a huge one may be a genuine bulk purchase. Remove clear errors, keep real values, and if a few big orders dominate, report the median as well or show them separately. Never delete outliers just to make a chart look neat.

The mean is the sum divided by the count; the median is the middle value when the data is sorted. The mean is pulled by extreme values, the median isn't. If nine employees earn ₹30,000 a month and one earns ₹5 lakh, the mean is ₹77,000, which describes nobody, while the median is ₹30,000.

So use the median for skewed data: salaries, house prices, order values, delivery times. Use the mean when data is roughly symmetric or when you need totals, since mean times count gives the total. The mode, the most frequent value, is useful for categories, like the most common product size.

Standard deviation measures how spread out values are around the mean: roughly the typical distance of a value from the average. A small one means values are close together, a large one means they vary a lot. It's the square root of the variance, so it's in the same units as the data.

Example: two stores both average ₹2 lakh in daily sales, but one has a standard deviation of ₹10,000 and the other ₹80,000. The second is far less predictable, which matters for stocking and staffing. For roughly normal data, about 68% of values lie within one standard deviation of the mean and about 95% within two.

The 90th percentile is the value that 90% of deliveries are faster than, so it tells us what the slowest tenth of customers experience. The average can look healthy while many customers wait too long, because fast deliveries hide the slow ones, and a few very late orders can also distort it.

For example, an average of 28 minutes with a 90th percentile of 55 minutes means one order in ten takes nearly an hour, and those are the customers who complain and leave. Tracking the median for the typical case and the 90th percentile for the bad cases gives a truer picture, and gives the team a clear target to bring down.

Not from that alone. A correlation of 0.8 means a strong positive linear relationship: months with more ad spend tend to have more sales. It doesn't show which causes which, or whether something else drives both. Here the usual suspect is season: companies spend more on ads during Diwali and big sale events, when people buy more anyway.

Also check the scatter plot, since Pearson correlation only measures straight-line relationships and a few outliers can inflate it. To estimate the real effect, you'd need an experiment, such as pausing ads in some cities or running a holdout group, or at least a model controlling for season. Say what the data supports and what it doesn't.

Start with the questions the dashboard must answer and who will use it. Then connect to the Excel file, check and clean the data (types, blanks, duplicates) in Power Query or Tableau's data source page, and set up relationships if there are several tables, such as sales, products and regions.

Next build the calculations, like total sales, profit margin and growth, and the visuals: KPI cards at the top, a trend over time, a breakdown by region or product, and filters for date and region. Check the totals against the source file. Finally publish it to Power BI Service or Tableau Cloud/Server, share it with the right people and schedule a data refresh.

Dimensions are fields you group or slice by, usually text or dates: region, product category, customer name. Measures are numbers you aggregate: sales, quantity, profit. Tableau puts a measure on the view as an aggregate like SUM(Sales), split by whatever dimensions are in the view.

Discrete and continuous are a separate idea, shown by colour: blue pills are discrete and create separate headers, while green pills are continuous and create an axis. A date can be either: discrete months give one column per month name, while a continuous month draws a time axis for a line chart. Mixing these up is a common reason a chart looks wrong.

Treat it as a definitions problem first, not a blame problem. Agree on a small, specific case, such as one month for one region, and compare both numbers line by line. The usual causes: different revenue definitions (gross or net of returns, with or without GST), different date fields (order date versus invoice date), timing of the data refresh, filters such as excluding cancelled orders, or a join that duplicated rows.

Once you find the difference, agree on one definition with finance, write it down, and show it on the dashboard as a tooltip or note. Then add a check against the finance total so a gap is spotted before a manager sees it.

Keep it to about a minute and point it at analytics. Present, past, future works well: "I'm a [degree] graduate from [college], and I enjoy finding answers in data. I've learnt SQL, Excel and Power BI, and in my project I analysed [dataset, e.g. two years of sales for a retail chain] and found [one specific insight, e.g. that weekend discounts lost money in two regions]. I also [internship or certification, with what you did]. I'm looking for a data analyst role where I can help teams make decisions with clear numbers."

Name one real finding; it's more memorable than a list of tools.

Give a genuine reason and show you know what the job involves day to day: cleaning data, answering questions from business teams and explaining results. For example: "I like solving problems with evidence. In college I [specific moment, e.g. analysed our fest's ticket sales and showed which promotion actually worked], and I enjoyed both the analysis and seeing the team act on it. Analytics lets me do that every day, across different parts of a business."

Avoid saying it's a well-paid or trending field; it suggests you might leave as soon as something else trends.

Use STAR and make the business impact clear. For example: "Our sales team wanted to extend a discount scheme to all cities (situation). I was asked to check whether it was working (task). I compared cities with and without the scheme over the same months, adjusting for festival season, and found it raised orders but cut profit per order by more than the extra orders added (action). We kept it in only two cities and saved about [amount] a quarter (result)."

Say what you personally did, and include one number, even approximate. If the decision went another way, say what you learnt about presenting it.

Show that you prioritise openly rather than silently working late. A good structure: understand each request (what decision it supports and the real deadline), estimate the effort, then agree the order with the people involved or your own manager if they conflict. For example: "I'd ask each person what the numbers are for and when they're needed. Often one is for a meeting tomorrow and another can wait till Friday. If two are truly urgent, I'd ask my manager to decide the order, and I'd tell everyone when to expect their numbers."

Mention that a quick first answer, clearly marked as rough, sometimes solves the real need.

Lead with the answer and what it means for them, then give only the evidence they need. For example: "I'd say: 'Repeat orders are falling because first-time customers in Tier-2 cities are getting late deliveries; fixing delivery there is likely to help most.' Then I'd show one simple chart comparing on-time rates and repeat rates, and keep the details ready in case they ask."

Avoid jargon like p-values or correlation coefficients unless asked, use their business terms, and finish with a clear recommendation and its expected impact. Interviewers like candidates who say they check the manager understood, by asking what they'd do next.