How to Build a Data Analyst Assessment That Predicts Performance

A strong data analyst assessment tests six things: SQL reasoning, practical statistics, hands-on analysis in Python, data cleaning, communicating insights, and business sense. The best format combines short multiple choice questions for breadth, one or two realistic analysis tasks for depth, and a written or video explanation that shows whether the candidate can turn numbers into a decision.

Many data analyst hiring processes lean too heavily on one of these. Some test only SQL syntax, and end up hiring people who can write queries but cannot tell a manager what the results mean. Others rely on a long take-home project that strong candidates skip. This guide shows how to cover every skill in a structure candidates will actually complete.

The data analyst skills worth testing

Before writing a single question, decide what the role needs. A product analyst, a marketing analyst and a finance analyst share a core but weight it differently.

Skill	What good looks like	Best question format
SQL reasoning	Understands joins, grouping, window functions and how queries can silently return wrong results	Multiple choice and open-ended
Statistics	Knows when an average misleads, understands sampling, variance and basic experiment design	Multiple choice and open-ended
Python / pandas	Can load, reshape, aggregate and summarise data correctly	Coding task with test cases
Data cleaning	Spots duplicates, missing values, inconsistent formats and outliers, and handles them deliberately	Coding task and open-ended
Communication	Explains a finding clearly, with appropriate caveats, to a non-technical audience	Video or open-ended
Business sense	Asks what decision the analysis supports before choosing a method	Open-ended scenario

Testing SQL reasoning in a data analyst assessment

SQL is the daily tool for most analysts, but typing syntax from memory is not the skill that separates good analysts from weak ones. What matters is understanding what a query will return, and noticing when it is wrong. You can test that very effectively with reasoning questions rather than a live database.

Sample SQL questions

• Which query returns… Show four queries against an orders and customers table and ask which one returns the number of customers who placed at least one order in March. Distractors include a query that counts orders instead of customers and one that uses an inner join where a filter excludes valid rows.
• Explain the bug. Give a query that joins orders to order items and then sums order totals. Ask the candidate to explain why revenue is overstated (the join duplicates each order once per item) and how to fix it.
• Predict the output. Show a small table and a query using a window function such as a running total partitioned by customer. Ask what the third row returns.
• NULL handling. Ask why a WHERE status != 'cancelled' filter drops rows where status is NULL, and how to rewrite it.
• Choose an approach. In an open-ended question, ask how the candidate would find each customer’s first purchase date and what edge cases they would check.

These questions are quick to answer for someone who uses SQL daily, hard to fake for someone who does not, and they test exactly the mistakes that cause bad numbers in real reports.

Testing statistics and business sense

Analysts do not need to derive formulas, but they do need to avoid the traps that lead to wrong conclusions.

• Averages versus medians. “Average order value rose 20% this month, but most customers report spending less. What could explain this?” A good answer mentions a few very large orders skewing the mean.
• Experiment basics. “A test variant converted better after two days with 40 users per group. The product manager wants to ship it. What do you advise?”
• Correlation and causation. “Users who enable notifications retain better. Should we force notifications on for everyone?”
• Framing the question. “Sales asks for a dashboard of every metric by region. What would you ask before building it?”

Score these open-ended answers against a short rubric: did the candidate identify the core issue, suggest a sensible next step, and state their assumptions? Keeping a written rubric is the easiest way to make these questions fair, as covered in our guide to structured interview scorecards.

Hands-on analysis with Python coding tasks

A practical task shows whether a candidate can do the work, not just describe it. Python with pandas is a good choice because it covers loading, cleaning, reshaping and summarising data in one place, and the results can be checked automatically with test cases.

Sample Python tasks

1. Clean and aggregate. Given a list of transaction records with duplicate IDs, mixed date formats and missing amounts, write a function that removes duplicates, parses dates, drops or flags invalid rows, and returns total revenue per month.
2. Cohort retention. Given sign-up and activity records, return the share of each monthly sign-up cohort still active one month later.
3. Outlier detection. Return the order IDs whose amount is more than three times the median for that product category.
4. Funnel conversion. Given event logs, calculate the conversion rate between each step of a sign-up funnel, handling users who skip steps.

Design test cases that include the messy edge cases: an empty input, a customer with a single record, a month with no sales. Candidates who handle these thoughtfully tend to produce reliable reports on the job. For more on why realistic coding tasks outperform puzzles, see why real coding assessments produce better hires.

Testing communication of insights

An analyst who finds the right answer but cannot explain it creates little value. Add one short communication task:

• Video response: “In two minutes, explain the result of your cohort analysis to a marketing manager who has not seen the data. Include one recommendation and one caveat.”
• Written response: “Write a three-sentence summary of your findings for a weekly leadership update.”

Rate clarity, accuracy, appropriate caveats and whether there is a clear “so what”. This one task often separates otherwise similar candidates more than any technical question.

A recommended data analyst assessment structure

For a mid-level role, this 75 to 90 minute structure balances coverage with candidate time:

Section	Format	Suggested time	Weight
SQL reasoning	8–10 multiple choice plus 1 “explain the bug” open-ended	20 min	25%
Statistics and business sense	5 multiple choice plus 2 short scenarios	15 min	20%
Python analysis	1–2 coding tasks with test cases	30–40 min	35%
Communication	1 video or written explanation	10 min	20%

Adjust the weights by role. A reporting-focused analyst may need more SQL weight; a product analyst more statistics and communication. For junior or campus hires, shorten the Python section and lean on reasoning questions. Tell candidates the structure in advance so they can prepare and pace themselves.

Common mistakes when assessing data analysts

• Testing syntax recall instead of reasoning. Analysts look up syntax constantly. What they cannot look up is judgement about whether a result makes sense.
• Using clean, tidy datasets. Real data is messy. A task with perfectly formatted input tells you little about how a candidate handles the problems they will meet every week.
• Ignoring the question behind the question. If no part of the assessment asks what decision the analysis supports, you are hiring query writers rather than analysts.
• Making the test too long. A four-hour project filters for free time, not skill. Keep the screening stage focused and save deeper work for finalists.
• Scoring open-ended answers by feel. Without a rubric, two reviewers can rate the same explanation very differently.

How NirnAI helps you assess data analysts

NirnAI supports every part of this structure. Paste or upload your data analyst job description and AI question generation extracts the skills and seniority, then drafts role-specific multiple choice, coding, open-ended and video questions. Your team reviews and edits each question before publishing, and can clone the assessment for similar analyst roles.

Multiple choice questions are auto-scored, which suits SQL reasoning and statistics sections. NirnAI does not execute SQL, so SQL is tested through reasoning questions like the ones above. Hands-on analysis runs as Python coding tasks in an in-browser editor with syntax highlighting, autocomplete and test cases. Video and open-ended responses capture how candidates communicate, and structured scorecards keep ratings consistent across reviewers. See all assessment question types.

Analytics such as score distributions and section-level breakdowns show which skills are scarce in your pipeline, and standard proctoring gives reviewers integrity flags to assess. Start a 14-day free trial of NirnAI and build your first data analyst assessment today.