Data Analyst interview questions and answers

WHERE filters individual rows before grouping, and HAVING filters groups after GROUP BY and aggregation. So to find cities with more than 100 orders placed in March, I would put the date condition in WHERE, since it applies to each order row, then GROUP BY city, and put COUNT(*) greater than 100 in HAVING, since the count only exists after grouping. You cannot use an aggregate like SUM in WHERE. Putting row filters in WHERE is also faster, because fewer rows reach the grouping step.

An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns every row from the left table, and where there is no match the right table’s columns come back as NULL. RIGHT JOIN is the mirror, and FULL OUTER JOIN keeps unmatched rows from both sides. I use LEFT JOIN a lot to find gaps, for example customers LEFT JOIN orders WHERE order_id IS NULL gives customers who never ordered. One trap: if the right table has several rows per key, a join multiplies rows, so I check row counts before and after every join.

I would first sum revenue per product and category in a CTE. Then I would add DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) as rnk, and in the outer query keep rows where rnk is 3 or less. The choice of function matters when there are ties. ROW_NUMBER gives unique numbers, so tied products get split arbitrarily. RANK gives ties the same rank but skips the next numbers, like 1, 1, 3. DENSE_RANK gives 1, 1, 2 with no gaps. I would ask the business whether tied products should both appear, and pick the function accordingly.

First I find out why values are missing. If a delivery date is blank because the order was cancelled, that is meaningful and I would not fill it. If data is missing at random and it is a small share, I may drop those rows. Otherwise I fill with the median for skewed numbers, or a clear Unknown category for text, and I note it in the report. For outliers, I use a box plot or the IQR rule to spot them, then check the source. A salary of 10 crore might be a typo in paise. Real extremes I keep, but I may report the median instead of the mean.

First I would check the data itself: did the pipeline load fully, did any tracking or definitions change. If the drop is real, I compare with the same weekday last week, since orders are seasonal by day. Then I segment: by platform, app version, city, payment method and new versus returning users. If the drop is only on Android after a release, it is probably a bug. I would also walk the funnel from visits to add-to-cart to payment, to see where people fell off. Finally I check outside factors like a holiday, a competitor sale or a payment gateway outage.

VLOOKUP only looks to the right of the lookup column, breaks if someone inserts a column because the index is a fixed number, and defaults to approximate match unless I set the last argument to FALSE, which causes silent wrong results. INDEX-MATCH fixes these: it can look left and does not break when columns move. XLOOKUP, where available, is simplest: exact match by default, looks in any direction and has a built-in value for not found. I use XLOOKUP when the team’s Excel version supports it, and INDEX-MATCH otherwise. For big joins I would move to Power Query or SQL.

The mean is pulled by extreme values, and the median is not. So for skewed data like salaries, order values, house prices or delivery times, I report the median, and often the mean too, because the gap between them is informative. For example, if average order value is 1,800 rupees but the median is 900, a few bulk buyers are lifting the average and a typical customer spends much less. For roughly symmetric data, like heights or test scores, the mean is fine and easier to use in further calculations. I also show percentiles, such as the 90th, for things like delivery times.

I would define one primary metric up front, say checkout conversion rate, plus guardrails like average order value. Users are randomly split, and assignment is sticky so each user sees one version. I calculate the sample size needed from the current conversion rate and the smallest lift worth detecting, then run for full weeks to cover weekday patterns. I do not stop early when results look good, because peeking inflates false positives. At the end I use a two-proportion test. A p-value below 0.05 means a difference this large would be unlikely if the button had no effect. I report the confidence interval too.

I start by asking the sales head which decisions the dashboard should support, for example which regions need attention this week. Then I agree on metric definitions, like whether revenue is booked or collected. The top row shows four or five KPI cards: revenue against target, orders, average deal size and conversion rate, each with change versus last period. Below that, a line chart for the trend, a bar chart for regions sorted by gap to target, and a table for drill-down. I avoid pie charts with many slices. In Power BI I add slicers for region and month, and I schedule a daily refresh.

Correlation means two things move together. Causation means one actually drives the other. For example, I found that customers who used our app’s wishlist spent more per year. The marketing team wanted to push everyone to use wishlists. But more engaged shoppers probably both use wishlists and spend more, so engagement is a confounder. To test cause, the best route is an experiment: randomly prompt some users to try the wishlist and compare spending with a control group. If an experiment is not possible, I compare similar users, matched on past spending and visits, before and after they start using it.

I run a fixed set of checks. I compare totals with a trusted source, such as finance’s monthly revenue figure, and investigate any gap above a small tolerance. I check row counts after every join to catch duplicates, and look for NULLs and impossible values like negative quantities. I sanity-check trends against last week and last month, since a sudden jump usually means a data issue. I write down the definitions and filters I used at the top of the report. For anything going to senior leaders, I ask a colleague to review the SQL, and I do the same for them.

Our regional manager believed a discount campaign had driven a spike in sales and wanted to repeat it every month. When I analysed it, sales had risen equally in regions without the discount, because the spike matched the start of the wedding season. I did not send a blunt email. I met him first, showed the comparison chart of discount and non-discount regions, and asked what else could explain it. He saw the pattern himself. We agreed to run the next discount in only half the regions as a test. It showed a small lift, and he now asks for test designs before campaigns.

Two days after sending a monthly retention report, I noticed my query counted test accounts created by the QA team, which inflated active users in one city. I fixed the query the same morning, measured the impact, which changed that city’s retention by about four points, and emailed everyone on the original list with the corrected numbers and a one-line explanation. I called the city manager directly because he had quoted the number in a meeting. Then I added a filter for internal accounts to our shared base table so no one else would hit it.

During my internship at a mid-size retail chain, the store operations team asked why one store had high inventory write-offs. I pulled six months of sales and stock data into Excel and then SQL, and grouped write-offs by product category. Almost half came from dairy products, and they peaked on Mondays. Delivery was scheduled on Saturdays, but Sunday sales were lower than the order assumed. I recommended splitting the dairy delivery into Saturday and Monday. The manager tried it for a month and dairy write-offs in that store fell by roughly a third.

I had built a churn analysis using logistic regression to find which customers were likely to cancel their subscription. The operations manager did not need coefficients. I opened with the answer: customers who raise two or more support tickets in their first month are about three times as likely to cancel. Then one chart showing that, and one recommendation: call these customers in week two. I kept the model details in an appendix for anyone curious. She approved a callback pilot the same week, and I tracked cancellations for the called group against a similar group that was not called.

Every Monday, two people in our team spent about five hours copying data from three systems into an Excel file for the weekly business review. I wrote SQL queries for each source, combined them in a Python script with pandas, and loaded the output into a Power BI report that refreshed automatically each morning. I added simple checks that emailed me if row counts dropped sharply. I documented the steps and trained a teammate to maintain it. The Monday work dropped to a quick review of about thirty minutes, and copy-paste errors disappeared.

Our head of sales needed a region-wise view of lost deals for a board meeting the next afternoon. The CRM data had duplicate accounts and free-text loss reasons. I focused on what the decision needed: loss value by region and the top reasons. I removed duplicates by matching on GST number, and grouped the free-text reasons into six categories with keyword rules, reviewing a sample by hand. I sent the summary by the morning with a clear note that about eight per cent of reasons were unclassified. After the meeting I cleaned the data properly and proposed a dropdown for loss reasons in the CRM.

As a fresher with an internship and certifications in SQL and Power BI, I have seen entry-level analyst roles in Gurugram offering roughly 4.5 to 6.5 lakh per annum depending on the company and the tools used. I would be comfortable within that range, ideally around 5.5 lakh. At this stage, the quality of learning matters a lot to me: working with real data at scale and having senior analysts review my work. So I am open to discussing the full package, including any training or certification support you offer.

I have worked on retail sales data for two years, and I want to move into lending because the decisions are sharper there: approval rules, risk, collections, and every choice has a measurable cost. I have been learning the basics of credit, such as bureau scores, delinquency buckets and vintage analysis, and I built a small project on a public loan dataset to practise. Your team uses data directly in approval decisions, not only in monthly reports, and that is the kind of analyst work I want to do.

Yes. I understand the role needs overlap with the UK in the afternoon and a few hours with the US in the evening. In my current job I already work from 12 to 9 pm on two days a week for a client in London, and it works well for me. I would like to know whether there is a fixed shift or flexible hours, and whether transport is provided for late finishes, since that affects how I plan my commute. I would prefer that very late calls stay occasional and are planned in advance where possible.