Questions on SQL, data visualisation, statistical thinking, and communicating insights to stakeholders.
Structure as: Question, Data, Method, Finding, Impact. Always end with the business outcome, not the chart.
Strong answers follow a clear narrative: business question, data sources, methodology, key findings, and measurable impact (revenue saved, decisions influenced, processes improved). Look for evidence that the analysis drove real action, not just a report that sat on a shelf.
Establishes the candidate's analytical maturity. Do they think in terms of business impact or just technical output? Can they explain their work to a non-technical audience?
Use a concrete example: "I needed each employee's salary alongside their department average - window functions let me keep every row while adding the aggregate."
Good answers explain that window functions compute values across a set of rows related to the current row without collapsing them. Common examples: running totals (SUM OVER), rankings (ROW_NUMBER, RANK), moving averages, and comparing each row to its group average. Key distinction: GROUP BY reduces rows, window functions preserve them.
Fundamental SQL skill for mid-level analysts. If they cannot explain window functions, their SQL depth may be limited. Ask a follow-up with a specific scenario.
Always document your cleaning decisions. Future you (or your colleague) needs to know why you dropped those 200 rows.
Strong answers follow a systematic approach: profile the data first (counts, distributions, null rates), assess data quality issues by column, decide on handling strategies (imputation, deletion, flagging) based on the analysis goal, document all cleaning decisions, and validate the cleaned data against known benchmarks. Best candidates mention reproducibility.
Tests practical data skills. Real analysts spend 80% of their time on data quality. Red flag: candidates who assume data is always clean or who delete missing values without thought.
Always check if the result is practically significant, not just statistically significant. A 0.01% improvement might be "real" but not worth shipping.
Look for: defining success metrics before the test, calculating sample size for statistical power, checking for statistical significance (p-values, confidence intervals), watching for novelty effects, ensuring proper randomisation, considering practical significance vs statistical significance, and segmenting results. Pitfalls: peeking at results early, multiple comparisons, Simpson's paradox.
Core data analyst skill. Strong candidates mention both statistical and practical significance. Ask: "The test is significant at p=0.04 but the effect size is tiny. What do you recommend?"
Ask yourself: "What comparison is the audience making?" Then choose the chart that makes that comparison obvious.
Strong answers match chart type to data relationship: bar charts for comparison, line charts for trends over time, scatter plots for correlation, histograms for distribution. They should mention when pie charts are misleading (too many slices, similar sizes), when dual-axis charts distort, and the importance of labelling and context.
Tests communication and visual literacy. The best analysts obsess over whether their chart tells the right story. Red flag: defaulting to the same chart type for everything.
Treat "it does not feel right" as useful signal, not an insult. Their intuition might be based on data you do not have yet.
Best answers show empathy and rigour: take the concern seriously, review methodology for errors, understand what their intuition is based on (different data source, different time period, different segment), present the data transparently, and be willing to dig deeper rather than just defending the analysis.
Tests soft skills and intellectual honesty. Do they get defensive or curious? Can they maintain credibility while acknowledging uncertainty? The best analysts welcome challenges to their work.
Lead with the business implication, then the evidence. "We should do X because Y" is better than "The regression coefficient is Z."
Strong answers use analogies, concrete examples, and visual aids rather than technical jargon. They should mention tailoring the level of detail to the audience, leading with the "so what" before the "how", and creating narrative around the data. Best candidates test comprehension and iterate.
Communication is what separates good analysts from great ones. Ask them to explain a concept (e.g., confidence intervals) as if you were a non-technical VP.
Show that you investigated the surprise thoroughly before raising the alarm. One unexpected data point is a curiosity; a validated pattern is an insight.
Look for intellectual curiosity: did they investigate the unexpected result rather than dismiss it? Did they validate it through multiple angles? How did they communicate the surprising finding? Strong answers show that the candidate influenced real decisions based on data, not just confirmed what people already believed.
Reveals analytical courage. Some analysts only tell stakeholders what they want to hear. The best ones surface uncomfortable truths backed by solid evidence.
Show that you think about who could be harmed by your analysis, not just who benefits from it.
Look for awareness of: GDPR/data protection regulations, anonymisation techniques, potential for bias in data and algorithms, responsible use of personal data, and the line between insight and surveillance. Best candidates have practical examples of ethical decisions they have made.
Increasingly important in data roles. Candidates who dismiss privacy concerns or have never considered bias in their data are a risk. Look for thoughtful engagement with these issues.
Talk about a tool you genuinely love and know deeply. Explain the specific workflow improvement, not just features.
The specific tool matters less than the reasoning. Strong answers explain why the tool fits their workflow, what problems it solved, its limitations, and when they would choose something different. Look for genuine enthusiasm and depth of knowledge, not just name-dropping popular tools.
Tests depth vs breadth. A candidate who knows one tool deeply and understands its trade-offs is more valuable than one who has superficially used everything. Also reveals learning style and curiosity.
Use a concrete example: "Overall retention looked stable at 40%, but when we split by signup month, we saw newer cohorts retaining at only 25%."
Strong answers explain grouping users by a shared characteristic (signup date, first action, acquisition channel) and tracking their behaviour over time. Best examples show how aggregate metrics looked fine but cohort analysis revealed declining retention in newer cohorts, or that a specific channel produced lower-quality users.
Tests analytical depth beyond surface metrics. Candidates who only work with aggregate numbers miss critical trends. Cohort analysis is fundamental to understanding product health.
Always start with the questions the data must answer, not the data you have. The business question determines the grain, dimensions, and facts.
Look for: understanding the business questions first, identifying required granularity, choosing appropriate schemas (star, snowflake), defining dimensions and facts, considering query patterns, and planning for scalability. Best candidates discuss the importance of getting the grain right and how modelling decisions affect downstream analysis.
Senior analyst skill. Candidates who jump into building tables without understanding requirements create models that need constant rework. Ask: "How do you handle slowly changing dimensions?"
Always check your assumptions before trusting the results. A residual plot takes 30 seconds and can save you from presenting misleading findings.
Strong answers cover: use cases (predicting outcomes, understanding relationships, controlling for confounds), key assumptions (linearity, independence, homoscedasticity, normality of residuals), and practical diagnostics (residual plots, VIF for multicollinearity). Best candidates discuss when simpler methods are better and how to communicate regression results to non-technical audiences.
Tests statistical depth. Not all analyst roles need regression, but understanding it reveals statistical literacy. Ask follow-up: "How do you explain a regression coefficient to a marketing manager?"
Every chart on a dashboard should answer a specific question. If you cannot state the question, the chart should not be there.
Look for: understanding the audience and their decisions, leading with the most important metric, progressive disclosure (summary to detail), consistent formatting, appropriate chart types, avoiding clutter, providing context (targets, comparisons, trends), and making it actionable rather than just informational.
Tests communication and design thinking. Analysts who create cluttered, everything-on-one-page dashboards frustrate stakeholders. Those who design with decision-making in mind create tools people actually use.
Check the data pipeline before investigating the business. Many "metric drops" are actually incomplete data loads or tracking issues.
Strong answers follow a systematic process: verify the data pipeline first (is the data complete? recent deploys? source changes?), then segment the drop (by region, channel, device, cohort), check for external factors (seasonality, holidays, competitor actions), and communicate findings with appropriate caveats.
Practical analytical skill. Red flag: jumping to business conclusions without checking data quality first. Good sign: systematic elimination of data issues before investigating business causes.
Build the data model well and document it thoroughly. If stakeholders get different answers from the same data, trust erodes. A metrics dictionary is essential.
Best answers balance empowerment with governance: creating clean, well-documented data models, providing training, establishing naming conventions, maintaining a single source of truth for key metrics, and staying available for complex questions while deflecting routine ones to self-serve tools.
Reveals scalability thinking. Analysts who do everything themselves become bottlenecks. Those who enable others multiply their impact. Ask: "How do you handle it when someone's self-serve analysis produces wrong results?"
Lead with the data, not your opinion. "The numbers show X, which could mean Y or Z. I recommend we investigate further" is better than "the project is failing."
Strong answers show courage and tact: presenting facts without editorialising, providing context and possible explanations, suggesting next steps or deeper investigation, delivering the message privately before a large meeting, and offering solutions alongside problems. Best candidates prepare for pushback with additional data.
Tests integrity and political skill. Analysts who sugarcoat data are dangerous. Those who present it bluntly without context burn bridges. The best analysts deliver truth with empathy and solutions.
Quantify the impact: hours saved per week, errors eliminated, and what you did with the freed-up time. The last point is what makes automation strategic, not just technical.
Look for: identifying the repetitive work, choosing appropriate tools (Python, SQL, BI tool scheduling), ensuring reliability and error handling, validating automated output matches manual, and measuring time saved. Best candidates also mention how automation freed them to do higher-value analytical work.
Practical skill that reveals initiative and efficiency mindset. Analysts who do not automate repetitive work are not leveraging their technical skills. Ask: "How did you validate the automated output?"
Always state your assumptions and confidence level. "Based on available data, I am 70% confident that X, but we could improve this by collecting Y" shows intellectual honesty.
Best answers show: transparency about data limitations, using multiple data sources to triangulate, sensitivity analysis to test how conclusions change with different assumptions, clearly communicating confidence levels, and recommending data collection to fill gaps. They should never present uncertain findings as certain.
Crucial skill. Real-world data is never perfect. Analysts who wait for perfect data never deliver. Those who pretend incomplete data is complete are dangerous. Look for the balanced, transparent approach.
Always start with a simple baseline (last period, seasonal naive) before trying complex models. If you cannot beat the baseline, the complex model is not adding value.
Strong answers cover: decomposing time series into trend, seasonality, and residuals, choosing appropriate models (moving averages, exponential smoothing, ARIMA, Prophet), handling stationarity, cross-validation with time-based splits (not random), and accuracy metrics (MAE, MAPE, RMSE). Best candidates discuss when simple methods outperform complex ones and how to communicate forecast uncertainty.
Advanced analytical skill. Tests statistical depth and practical judgment. Ask: "How do you communicate forecast uncertainty to stakeholders who want a single number?" to gauge communication maturity.
Always start with the execution plan. Look for full table scans and missing indexes first - they are the most common culprits and the easiest to fix.
Strong answers describe a systematic approach: using EXPLAIN or query plans, identifying full table scans, checking index usage, evaluating join strategies, considering query rewriting, and testing improvements. Best candidates discuss the trade-offs between query performance and readability, and when to denormalise for performance.
Practical SQL skill. Analysts who can only write queries but not optimise them become bottlenecks as data grows. Ask for a specific before/after example to gauge real experience.
Use a simple analogy: "If you flip a coin 10 times and get 7 heads, is the coin biased or were you just lucky? Statistical significance helps us answer that question for business experiments."
Strong answers avoid jargon and use concrete analogies: comparing statistical significance to signal versus noise, explaining confidence levels as "how sure we are this is not a coincidence", and distinguishing statistical significance from practical significance. Best candidates make the concept accessible without oversimplifying.
Tests communication skill, not statistical knowledge. Can they make complex concepts accessible? This is the single most important skill for an analyst working with non-technical stakeholders.
Watch stakeholders try to use your dashboard. If they open it and immediately open a spreadsheet to do their own calculations, the dashboard is not answering their real questions.
Strong answers show user-centred thinking applied to internal tools: observing how stakeholders actually used (or did not use) the dashboard, gathering feedback, simplifying the design, adding context and actionability, and measuring adoption. Best candidates treat dashboard design as a product problem, not a technical one.
Reveals whether the analyst thinks about their audience. Dashboards that nobody uses are waste. Analysts who iterate based on stakeholder feedback create tools that drive decisions.
Show that you built data quality checks into the pipeline, not as an afterthought. Describe what happens when a check fails: does the pipeline halt, alert, or load with a warning?
Strong answers cover: source data extraction, transformation logic, data validation checks at each stage, error handling and alerting, idempotent loads for safe reruns, logging, and monitoring. Best candidates discuss the trade-offs between batch and streaming, and how they ensured downstream consumers could trust the data.
Tests data engineering awareness. Not all analysts build pipelines, but understanding them is important. Candidates who can discuss pipeline reliability and failure handling show operational maturity.
Context matters more than statistics. A 10% drop on a Monday after a bank holiday is normal. The same drop on a Wednesday is worth investigating. Always check for obvious explanations before escalating.
Strong answers describe a process: establishing baselines, understanding expected variation, using statistical methods (z-scores, IQR, moving averages) or simple rules, and investigating anomalies before raising alarms. Best candidates discuss both false positives (crying wolf) and false negatives (missing real issues), and how they calibrated their detection thresholds.
Tests analytical judgment. Analysts who flag every fluctuation lose credibility. Those who miss genuine anomalies miss their core responsibility. Look for calibrated judgment and a systematic investigation process.
Structure your analysis like a story: "Here is what we expected, here is what we found, here is why it matters, and here is what we should do about it." The methodology goes in the appendix.
Strong answers show: structuring analysis as a narrative (context, tension, resolution), leading with the insight not the methodology, using visualisations that reinforce the story, tailoring depth to the audience, and ending with clear recommendations. Best candidates describe how their narrative directly led to a decision or action.
Communication is the analyst's ultimate deliverable. The best analysis is worthless if it does not drive action. Ask: "Walk me through the slides you would use to present this finding."
Start with a metrics dictionary that defines every key metric with its exact calculation, data source, and owner. When two people disagree about a number, the dictionary settles it.
Strong answers cover: data dictionaries, metric definitions, data lineage documentation, access controls, data quality monitoring, ownership assignment, and regular audits. Best candidates discuss the cultural challenge of getting people to maintain documentation and how they made governance a habit rather than a burden.
Tests organisational thinking. Analysts in growing organisations face the challenge of maintaining data trust at scale. Candidates who proactively establish governance practices prevent the "nobody trusts our data" problem.
When you find that finance and marketing define "active user" differently, bring both parties together and document the agreed definition at the SQL level. Ambiguity causes more damage than any wrong metric.
Strong answers describe: discovering that teams used different definitions for the same term, facilitating alignment discussions, documenting precise SQL-level definitions, establishing a single source of truth, and communicating changes. Best candidates discuss the political aspects of metric definition and how to get buy-in for standardisation.
Practical and underrated skill. Metric disagreements waste enormous amounts of time and erode trust. Analysts who can facilitate alignment and maintain clear definitions provide outsized value.
Segments must be actionable to be useful. Can the marketing team actually target them? Can the product team build features for them? If not, simplify until they can.
Strong answers cover: defining segmentation goals (marketing targeting, product prioritisation, pricing), choosing variables, analytical methods (RFM, clustering, behavioural cohorts), validating segments against real outcomes, and making segments usable by non-analytical teams. Best candidates discuss the trade-off between statistical sophistication and practical utility.
Tests analytical depth and practical judgment. Analysts who produce statistically elegant but practically useless segments are solving the wrong problem. Ask: "How did the business actually use the segments you created?"
Ask: "If this report showed X, what would you do differently?" This question forces the stakeholder to think about decisions, not just data. If they cannot answer, the report may not be needed.
Strong answers show a diagnostic approach: asking what decisions the report will support, who the audience is, what actions they would take based on different findings, what data they currently look at, and what questions keep them up at night. Best candidates transform vague requests into clear analytical questions before starting any work.
Foundational analyst skill. Analysts who build what is asked without questioning requirements produce shelfware. Those who diagnose the real need first deliver impactful work. Ask: "Have you ever talked a stakeholder out of a report?"