Data Analyst interview questions and answers

Take a customers table and an orders table joined on customer_id. An INNER JOIN returns only customers who have at least one order, because it keeps rows that match on both sides. A LEFT JOIN from customers to orders returns every customer; for those with no orders, the order columns are NULL. This is what I use when I need "all customers with their order count, including zero". A FULL OUTER JOIN returns all rows from both tables: matched pairs, customers with no orders, and orders with no valid customer. That last one is useful for data quality checks, for example finding orphan orders. I also watch out for one-to-many joins, because joining customers to orders repeats the customer row for each order, which can inflate totals if I sum customer-level values.

First I understand the data: what one row represents, where it came from and what each column means. Then I profile it: row counts, missing values per column, distinct values and min and max. I check for duplicates on the key that should be unique and find out why they exist before deleting anything. I fix data types, such as dates stored as text or numbers with commas, and standardise formats like city names written in different ways. I look for outliers and impossible values, like negative ages or future order dates, and decide with the business whether they are errors. For missing values I choose based on context: drop, fill with median, or flag them. Finally I document every step, ideally in a script or notebook, so the cleaning is repeatable and someone else can check it.

I use the median when the data is skewed or has outliers, because the mean gets pulled toward extreme values while the median is just the middle value. A good example is delivery time. Most orders arrive in 30 minutes, but a few take 5 hours because of a system issue. The mean might show 50 minutes, which no customer actually experienced, while the median shows about 30 minutes, the typical experience. Salaries and house prices are other classic examples. That said, the mean is still useful when the total matters, for example total revenue equals mean order value times the number of orders. In reports I often show both, along with a percentile like p90, because the gap between mean and median itself tells you the data is skewed.

I start from the question I want the chart to answer, not from the data. If the question is "how has this changed over time", I use a line chart. If it is "which category is biggest", I use a bar chart, usually sorted, and horizontal if labels are long. To show the relationship between two numbers, like ad spend and sales, I use a scatter plot. For distribution I use a histogram or box plot. I only use a pie chart for parts of a whole with two or three slices; otherwise bars are easier to compare. I avoid 3D and too many colours, use one highlight colour for the key point, label axes clearly, and put the takeaway in the title, for example "Sales fell 12% in March" instead of just "Sales".

I start with the answer, not the method. For example: "Repeat customers dropped 8% last month, mostly in Pune, and it lines up with the late deliveries there. I recommend we fix the courier issue first." Then I show one simple chart that supports that sentence. I avoid words like p-value or left join; instead I say "this is very unlikely to be random". I connect the finding to something they care about, like revenue lost or customers affected. I keep the detailed method ready in an appendix if they ask. I am also honest about limits, such as "this is based on two weeks of data". Finally I end with a clear next step, so the meeting leads to a decision and not just a nice chart.

At my last internship the marketing lead asked why online orders fell 8% in March. I first agreed on the metric: completed orders, excluding test accounts. I pulled orders, sessions and campaign data with SQL and checked row counts against the order system so I knew the data was complete. Then I broke the drop down by channel, device and region. Almost all of the fall came from mobile users in one city, starting the day a new app version was released. Checkout conversion on that version was half the normal rate. I shared a one-page summary with a chart and the size of the loss, about 1,200 orders a week. The app team found a payment bug, fixed it within days, and orders returned to normal. The key lesson for me was to segment early instead of looking only at the total.

I check in layers. First, row counts: I count rows in each table and after each join, and confirm the join keys are unique where I expect them to be, because a duplicated key silently inflates totals. Second, I reconcile a headline number such as last month revenue with a trusted source like the finance report or an existing dashboard. Third, I pick three or four real customers or orders and trace them by hand through the query to see that each step does what I think. I also scan for NULLs, negative amounts, future dates and test accounts. Finally, I ask if the result makes sense: if conversion suddenly doubled, I assume a bug before a miracle. When something still looks uncertain, I say so clearly in the summary instead of hiding it.

VLOOKUP searches the first column of a range and returns a value from a column to the right, picked by a column number. Two things make it risky: the last argument defaults to approximate match, which can return wrong values on unsorted data, and the hard-coded column number breaks if someone inserts a column. XLOOKUP takes a separate lookup range and return range, so it can look left or right, it does exact match by default, and it has an if-not-found argument so I can show "Missing" instead of #N/A. It can also search from the bottom to get the latest match. I use XLOOKUP whenever the file will be opened in modern Excel or Google Sheets. If colleagues use an old version, I use INDEX with MATCH, which has the same flexibility.

A metric is any number we measure, such as page views, average delivery time or number of support tickets. A KPI, or key performance indicator, is one of the few metrics the business has chosen to judge progress toward a specific goal. A KPI should have a clear definition, a target and an owner. For example, an online grocery company might track dozens of metrics, but if the goal this year is customer loyalty, one KPI could be the share of customers who order again within 30 days, with a target of 45% owned by the retention team. Delivery time is still a useful metric that helps explain the KPI, but it is not a KPI unless leadership has tied it to a goal. Keeping KPIs few makes dashboards focused.

First I decide what a duplicate means here. Exact copies of a row are easy, but the same customer may also appear with a different spelling or email case, so I check by a business key such as phone number or normalised email. Then I measure the size: how many rows are affected and whether they cluster around one source or date, which hints at the cause, like a double import. Next I agree a keep rule, for example keep the most recently updated record, and apply it in a repeatable way, such as ROW_NUMBER partitioned by the key and ordered by updated_at descending, keeping row 1. I never just delete rows by hand in a spreadsheet. Finally I note the change in my analysis and tell the data owner, so the problem is fixed at the source.

First I check whether the drop is real. Did the pipeline load fully, did a tracking event change, is the dashboard using a partial day? I compare with the source system if possible. If it is real, I check context: a holiday, a competitor sale, a payment gateway outage or a weather event. Then I segment: by platform (Android, iOS, web), app version, region, traffic channel, and new versus returning customers. Usually the drop is concentrated in one segment, for example only Android users on the latest version. Next I walk the funnel, from visits to product views to cart to payment, to find which step fell. I check the release and campaign calendar for changes. Once I find a likely cause, I share a short update with the evidence, my confidence level and who should act, and keep monitoring.

I start with a hypothesis: the new button will increase checkout completion. The primary metric is checkout conversion per user who reaches the cart, and guardrails include average order value, payment errors and page load time. I randomise by user, not by session, so a person always sees the same version. Using the baseline conversion, the smallest lift worth detecting, 5% significance and 80% power, I calculate the sample size and the number of days needed, rounding up to full weeks to cover weekday and weekend behaviour. I agree on this plan before launch and do not stop early when results look good. When the test ends, I first check that the traffic split matches the plan, then look at the effect size with a confidence interval, the guardrails, and key segments before making a recommendation.

Window functions calculate a value across a set of related rows while still keeping each row, unlike GROUP BY which collapses rows. The OVER clause defines the window, with PARTITION BY to split groups and ORDER BY to set order. I use ROW_NUMBER to get the latest record per customer, for example to remove duplicates or find each user's first purchase. I use RANK or DENSE_RANK for top N products per category, choosing based on how ties should be handled. For trends, SUM OVER with ORDER BY gives running totals, and AVG over the last 7 rows gives a 7-day moving average. LAG and LEAD let me compare each month with the previous month to compute month-over-month growth without a self join. They make queries shorter and usually faster than equivalent subqueries.

I start with what value the app gives users and what the business needs. For food delivery, a good north star is completed orders per week, because it reflects real value for customers, restaurants and riders. Under it I track input metrics the teams can move: app opens, menu views, add-to-cart rate, checkout conversion, and repeat order rate for retention. Then I add quality and guardrail metrics: average delivery time, late delivery percentage, cancellation rate, and customer ratings, so growth does not come at the cost of experience. Each metric gets a precise definition, for example whether cancelled orders count and which timezone a day uses. I also check that each metric is actionable, meaning a team knows what levers change it, and I keep the list short so people actually look at it.

I choose based on data size, complexity and who will use the result. Excel is great for small datasets, quick what-if checks, and when a business user needs to play with the numbers themselves. It becomes risky with large data and manual steps that are hard to repeat. SQL is my default for pulling and aggregating data from the warehouse, because the computation happens where the data lives and it handles millions of rows easily. Python with pandas is best when I need complex cleaning, statistical tests, merging many sources, or automating a report that runs every week. It is also more reproducible because the whole process is in code. In practice I often combine them: SQL to extract and aggregate, Python for analysis, and a BI tool or Excel for the final view.

Cohort analysis groups users by when they started, for example their sign-up month, and then follows each group over the months since they joined. The output is usually a triangle table: rows are cohorts, columns are month 0, month 1, month 2 and so on, and each cell is the share still active or their revenue. I use it when totals can mislead. If a company is growing fast, total active users can rise even while each new group of users leaves sooner, because new sign-ups hide the losses. Cohorts show whether behaviour itself is improving. They are also great for checking the effect of a change: if users who joined after the new onboarding flow keep a higher month 1 retention than earlier cohorts, that is a strong signal. I read down a column to compare cohorts at the same age.

I start by asking each manager a short question: what decision will this help, and by when? Often "urgent" means a meeting tomorrow, not in an hour. I also estimate effort, because a five-minute number can be sent right away while a new analysis takes days. Then I order by impact and deadline: a request feeding a pricing decision today beats a nice-to-have chart for next week. I reply to everyone with an honest timeline, so nobody is left guessing. If two high-impact requests truly conflict, I ask my own manager to help decide instead of choosing politically. Afterwards I look for patterns. If the same type of question keeps coming, I build a small dashboard or saved query so managers can serve themselves, which frees time for deeper work.

I first define the funnel steps clearly, for example product page, cart, checkout start, payment and order confirmed, and count distinct users reaching each step per day. Then I compute step-to-step conversion over time to see which step changed, rather than looking only at the overall rate. Before trusting the drop, I check for tracking problems, such as a renamed event or a tag missing after a release, because those are common. Next I segment the weak step by device, browser, app version, country, payment method and traffic source. A drop concentrated in one segment, like Android users on a new version paying by card, points to a cause. Finally I add qualitative evidence: error logs, payment failure codes, support tickets or session recordings. I report the step, the segment, the estimated orders lost and a suggested fix.

I never remove outliers automatically. First I find them, using a box plot or histogram and a simple rule like 1.5 times the IQR. Then I investigate: is a 500,000 AED order a typing error, a test transaction, or a real corporate customer? Errors get fixed or removed, and I note how many. Real extreme values usually stay, because they can matter a lot to the business, for example a few big clients driving much of revenue. If they distort a summary, I switch to robust measures such as the median, or cap values at a high percentile for modelling. For important conclusions I run a quick sensitivity check: show the result with and without the outliers. If the conclusion flips, that is something the stakeholder must know rather than a detail I hide.

I design it as a star schema. The fact table holds one row per order line with keys, quantity, amount and discount. Around it sit dimension tables: Product, Customer, Store and a dedicated Date table marked as the date table, so time intelligence like year to date works. Relationships go one-to-many from dimension to fact with single-direction filtering, which keeps filter behaviour predictable and fast. I avoid one wide flat table and avoid bidirectional relationships unless there is a clear need. All aggregations, such as Revenue, Margin % and Orders, are DAX measures rather than calculated columns, so they respond correctly to slicers. I remove unused columns and high-cardinality text to keep the model small. Finally I document each measure definition in a description field so business users and other analysts use the same numbers.

First I verify the test is valid: traffic split matches the plan, no tracking bugs, and enough runtime. Then I go back to the goal. Conversion and order value are both inputs; the business usually cares about revenue or margin per user. If revenue per user is flat with a confidence interval around zero, the change is not clearly winning. I segment to see what happened: maybe more small first-time buyers converted, which lowers average order value but could be valuable if they come back. So I would check repeat purchase or retention for the new converters, and margin, since small orders may cost more to deliver. My recommendation might be: do not roll out yet, but if new-customer acquisition is the strategic goal, run a longer test or a follow-up that measures 30-day value. I present this trade-off clearly instead of just reporting the significant metric.

Most of the time the gap comes from different definitions, not wrong maths. I sit with both teams and write down exactly how each number is calculated: gross or net of discounts, before or after refunds and taxes, order date or payment date, which timezone, and whether test or internal orders are removed. Then I build a bridge: start from the Marketing number and add or subtract each difference until I reach the Finance number. If a gap remains, I compare the raw sources and check for late-arriving data or failed pipeline loads. Once I understand the gap, I help the teams agree on named metrics, for example "gross bookings" for Marketing and "net recognised revenue" for Finance, each with a clear owner. Finally I put the definitions in a shared semantic layer or certified dataset so dashboards stop disagreeing.

A simple before versus after is risky because seasonality, marketing campaigns or general growth can explain the change. So I try to build a believable counterfactual, meaning what would have happened without the feature. If some group was not affected, like a region, platform or an older app version, I use difference-in-differences: compare the change in the affected group with the change in the unaffected group, after checking their trends were parallel before launch. If everyone got it, I can use an interrupted time series: model the historical trend and seasonality, forecast the post-launch period, and compare actuals against the forecast. Comparing users who used the feature with those who did not is tempting but has selection bias, since engaged users adopt features first; matching on past behaviour helps somewhat. I present a range, not a single number, and clearly state the assumptions.

I would first log and categorise a few weeks of requests. Usually 60 to 70% are repeat questions like "sales by region last week" or "how many new users". For those, I build a small set of well-designed dashboards with filters, on top of certified datasets where metrics like revenue and active user are defined once in a semantic layer such as dbt metrics or Looker. I write a short data dictionary and run training sessions so business teams can answer simple questions themselves. For the remaining requests, I set up an intake form with a clear prioritisation rule tied to business impact, so the team spends time on deep analysis instead of pulling numbers. I would measure success by fewer repeat requests, dashboard usage, and whether people trust the numbers. Self-serve fails if definitions are unclear, so governance is part of the plan.

With 200 metric and segment combinations, at a 5% threshold you would expect about 10 to look significant by pure chance, so 3 significant results is actually not evidence of anything. This is the multiple comparisons problem. The correct approach is to fix one primary metric and a few guardrails before the test and judge the decision mainly on those. For secondary metrics, apply a correction such as Bonferroni, which is strict, or Benjamini-Hochberg to control the false discovery rate. Segment results that were not planned should be treated as ideas, not conclusions, and confirmed in a new test designed for that segment. I also look at whether the effect size is meaningful and whether there is a believable reason for it. I would explain this to the team kindly, because highlighting lucky results can lead to bad product decisions.

I start by asking what the forecast is for: budgeting, hiring or investor guidance changes how precise and how detailed it must be. Then I study at least two to three years of history, looking at trend, weekly and yearly seasonality, holidays such as Ramadan and Eid, and one-off events like a big contract that should be removed or modelled separately. I build simple baselines first, such as last year same quarter times recent growth, then try a time-series model like exponential smoothing if it adds value. I backtest by pretending it is an earlier date and forecasting a quarter I already know, to measure the typical error. I present a central estimate with a range, for example 12.4M with 80% likely between 11.6M and 13.1M, plus scenarios tied to key assumptions like the new store opening on time. I update it monthly as actuals arrive.

I would gently reframe the request: my job is to measure the campaign fairly, because a result that cannot survive scrutiny will hurt the leader later. Before reporting, I double-check my work: the comparison period, the control group or baseline, seasonality and whether the data is complete, and I might ask a peer to review it. Then I share the finding with the leader privately and early, not first in a big meeting. I show the evidence simply and explain what would have counted as success. I also look for useful lessons, such as one channel or segment that did respond, or ideas for a better test next time. I will not cherry-pick a date range or metric to make it look good, because once analysts do that, nobody can trust any dashboard. In my experience, leaders respect honest work delivered respectfully.

Standard A/B tests assume one user's treatment does not affect another user, but in a marketplace it does. If a variant gives some riders a discount, they book more and take drivers away from control riders. Control looks worse and the variant looks better, so the measured lift is exaggerated, and the real effect at full launch may be near zero. To reduce this I would randomise at a level where the shared supply is contained. A switchback test alternates the whole city between control and treatment in time blocks, such as 30 minutes. A geo test randomises whole zones or cities. These designs have fewer independent units, so power is lower and I need longer tests and cluster-aware analysis. I would also track both sides of the market, like driver earnings and wait times, not just rider conversions.

I begin with the decision: are we choosing offers, channels or budget per segment? That decides which features matter. Often a simple RFM model, using recency, frequency and monetary value scored into bands, gives clear and explainable groups like "loyal high spenders" or "lapsing one-time buyers". If we need richer patterns, I try clustering such as k-means on scaled features like order frequency, basket size, categories and discount use, and I pick the number of clusters by both statistics and whether the groups make business sense. A useful segmentation has groups that are large enough to target, clearly different in behaviour, and stable when I rerun it on another month. The real test is impact: I would run campaigns where each segment gets a tailored message versus a generic one, and measure the lift per segment.

First I would do a short post-mortem on both incidents to find root causes, for example a late upstream load and a changed status code that broke a filter. Then I fix the causes, not just the numbers. I add automated checks on the pipeline: freshness, row counts within an expected range, uniqueness of keys, no unexpected NULLs and reconciliation of revenue with the finance system, with alerts to me before leaders open the dashboard. If a check fails, the dashboard shows a clear warning banner instead of wrong numbers. I put every metric definition in one documented place and show the last refresh time on the page. Finally, I am open: I tell leaders what went wrong, what changed, and I send a short note whenever an issue is caught. Trust returns through a run of reliable weeks.