What's the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates. Example: WHERE status = 'paid' … GROUP BY customer_id HAVING SUM(total) > 1000. Filtering in WHERE when you can is also faster, because fewer rows reach the aggregation.

Explain INNER, LEFT and FULL OUTER JOIN.

INNER keeps only rows that match in both tables. LEFT keeps every row from the left table and fills unmatched right-side columns with NULL. FULL OUTER keeps everything from both sides. Mention a pitfall: a filter on the right table in WHERE turns a LEFT JOIN into an INNER JOIN — put it in the ON clause instead.

What's the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(column) counts rows where that column isn't NULL. COUNT(DISTINCT column) counts unique non-NULL values. Interviewers use this to check you think about NULLs.

How would you find duplicate rows in a table?

Group by the columns that define a duplicate and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. To see or delete the extra rows, number them with ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) and keep row 1.

Find the second-highest salary in each department.

Use a window function: SELECT * FROM (SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2. Explain why DENSE_RANK (ties share a rank, no gaps) beats ROW_NUMBER here.

What's the difference between ROW_NUMBER, RANK and DENSE_RANK?

All number rows within a window. ROW_NUMBER is always unique (1,2,3,4). RANK gives ties the same number and skips the next (1,2,2,4). DENSE_RANK gives ties the same number without gaps (1,2,2,3).

Calculate a 7-day rolling average of daily revenue.

Aggregate to one row per day first (and fill missing days with a date spine if needed), then: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Note ROWS counts rows, so missing days would silently widen the window — that's why the date spine matters.

Write a query for month-over-month user retention (cohorts).

1) Find each user's cohort month: MIN(DATE_TRUNC('month', event_date)). 2) Join activity back to cohorts and compute months since cohort. 3) COUNT(DISTINCT user_id) by cohort and month offset, divided by the cohort size. Present it as a triangle: cohorts as rows, months since signup as columns.

A query that used to take 5 seconds now takes 5 minutes. How do you investigate?

Check what changed: data volume, a new join causing row explosion, or a changed filter. Read the EXPLAIN plan for full table scans and bad join orders, check indexes on join and filter columns, look for functions on indexed columns in WHERE, and confirm joins are on unique keys (fan-out is the classic cause). Pre-aggregate before joining where you can.

When would you use the median instead of the mean?

When data is skewed or has outliers — salaries, house prices, order values, session lengths. One very large value pulls the mean up but barely moves the median. Reporting both is often the best answer.

If there were truly no effect, the p-value is the probability of seeing a result at least as extreme as the one you observed. A small p-value (below a threshold like 0.05) means the data would be surprising under 'no effect'. It's not the probability that the hypothesis is true, and it says nothing about how big or important the effect is.

Explain Type I and Type II errors in an A/B test.

Type I (false positive): you conclude the variant works when it doesn't — controlled by α, usually 5%. Type II (false negative): you miss a real effect — controlled by power (1 − β), usually 80%. Bigger samples reduce Type II errors without raising Type I.

Correlation vs causation — how would you show a feature caused an increase in retention?

Correlation might come from selection (engaged users adopt features) or confounders. The gold standard is a randomised experiment. Without one, use quasi-experimental methods — difference-in-differences against a comparable group, regression discontinuity, or matching — and state the assumptions each relies on.

What's the difference between a confidence interval and a prediction interval?

A confidence interval covers where a population parameter (like the mean) likely is. A prediction interval covers where a single new observation will likely fall, so it's always wider because it includes individual variation as well as uncertainty about the mean.

Your A/B test is significant after 3 days, but you planned 2 weeks. Should you stop?

No. Checking repeatedly and stopping at the first significant result ('peeking') inflates the false-positive rate well above 5%. Early results also over-weight the users who visit most and miss weekly cycles. Run to the planned sample size, or use a sequential testing method designed for early stopping.

What is Simpson's paradox? Give an example.

A trend that appears in every subgroup reverses when the groups are combined, because the groups have different sizes or mixes. Example: a variant converts better on both mobile and desktop but worse overall, because it got far more mobile traffic, which converts lower. Always check results by key segments.

VLOOKUP vs XLOOKUP vs INDEX/MATCH — which do you use?

XLOOKUP when available: it looks in any direction, defaults to exact match and handles not-found values. INDEX/MATCH for compatibility with older versions. VLOOKUP breaks when columns are inserted and can't look left — if you use it, always set the last argument to FALSE.

How would you clean a messy export before analysis?

Work on a copy. TRIM and CLEAN text, standardise case, convert text-numbers and text-dates to real types, split combined fields (TEXTSPLIT or Text to Columns), remove duplicates, and flag rather than delete outliers. For anything repeatable, use Power Query so the steps rerun on next month's file.

When would you use a pivot table instead of formulas?

For quick exploration and summarising by several dimensions — drag fields to slice and dice. Use formulas (SUMIFS, or GROUPBY in 365) when the output must sit in a fixed layout, feed other calculations or update without refreshing.

How do you choose the right chart?

Start from the question. Change over time: line chart. Compare categories: sorted bar chart. Part of a whole: stacked bar (pie only for 2–3 parts). Relationship between two numbers: scatter. Distribution: histogram or box plot. Then remove everything that doesn't help answer it.

A stakeholder wants a dashboard with 25 charts. What do you do?

Ask what decisions it should support and who uses it. Lead with 3–5 headline metrics tied to those decisions, with drill-downs for detail. Agree on metric definitions up front. A focused dashboard gets used; a crowded one gets ignored.

What makes a chart misleading?

Truncated y-axes on bar charts, inconsistent scales between panels, dual axes that imply a relationship, cherry-picked date ranges, 3D effects, and colours that suggest an order that doesn't exist. Bar charts should start at zero; line charts can zoom in if labelled clearly.

Daily active users dropped 10% yesterday. How do you investigate?

First rule out data issues: tracking or pipeline bugs, late data, a definition change. Then check whether it's expected: holiday, seasonality, a known outage. Segment by platform, app version, country, acquisition channel and new vs returning to find where the drop is concentrated. Check releases and experiments that shipped. Size the impact and report what you found and ruled out.

What metrics would you track for a job board?

North star: successful applications (apply clicks that reach the employer) per active job seeker. Supply: active jobs, fresh jobs per day, coverage by role and location. Demand: searches, search-to-click rate, saved jobs, alert signups. Quality: apply rate per view, employer repeat posting. Guardrails: page load time, expired-job rate.

How would you measure the impact of a feature you can't A/B test?

Options: compare before/after against a control group that didn't get it (difference-in-differences), use a staggered rollout by region, use an instrument like a random eligibility rule, or a synthetic control built from similar markets. Be explicit about confounders, and validate the method on a pre-period where you know there was no effect.

How do you define an 'active user'?

It depends on the product's core value: an action that shows the user got value — sending a message, applying to a job, completing a lesson — not just opening the app. Pick a time window that matches the natural usage cycle (daily for messaging, weekly or monthly for job search) and document the definition.

Tell me about a time your analysis changed a decision.

Use STAR: Situation, Task, Action, Result. Lead with the business question, explain what you analysed and why, the surprising finding, how you communicated it, and the measurable outcome — revenue, time saved, a feature changed or killed. Keep it under two minutes.

How do you explain a technical finding to a non-technical stakeholder?

Lead with the answer and the 'so what', not the method. Use one clear chart, plain words and concrete numbers ('about 1 in 5 users' instead of '19.7%'). State your confidence and caveats briefly, and end with a recommended action.

Tell me about a time you found an error in your own analysis after sharing it.

Show ownership: you flagged it quickly to the people who used it, corrected it, explained the impact on the conclusion, and changed your process to prevent it — peer review, reconciliation checks against a known total, or automated data tests.

Two stakeholders want conflicting metrics definitions. How do you resolve it?

Understand why each definition matters to them — they're usually answering different questions. Show both numbers side by side with the differences explained, propose a single company definition owned by one team and documented in a metrics glossary, and escalate to a decision-maker if needed rather than quietly picking one.

Data Analyst Interview Questions

Common SQL, statistics, Excel, product-sense and behavioural questions from analyst interviews, with model answers. Filter by topic and difficulty.

30 questions

More free tools

All tools →

Salary Calculator
Typical pay by role and experience level, from live job posts on Gigsouk.
Resume ↔ Job Matcher
Compare your resume with any job description.
Resume Bullet Builder
Turn what you did into strong, metric-driven resume bullets.
Tech Skills Checklist
Data, full stack, AWS, GCP, Azure, DevOps and more — tick what you know, see what to learn next.
SQL Case Study Practice
Practice SQL with realistic business analytics problems.
How to Become a Data Analyst
A free five-phase roadmap — SQL, stats, viz and a portfolio.

Put your skills to work

Thousands of open roles from companies hiring now — apply directly with the employer.

Data & AI jobs Browse all jobs