How to Answer SQL and Python Screening Tests Sent by Recruiters Before the First Interview: What to Expect, How to Prepare, and How to Avoid the Most Common Eliminating Mistakes
You've applied for a data analyst role, your resume made it through, and now there's an email in your inbox from a recruiter. It's not a calendar invite for a call. It's a link to a coding assessment — and you have 72 hours to complete it. Your stomach drops a little. You're not sure what's in there, how hard it will be, or whether you're going to freeze up on something you actually know how to do.
This moment happens to nearly every candidate for every data role, and it eliminates a staggering number of people who are genuinely qualified — not because they lack the knowledge, but because they walked into the test unprepared for the format, the pressure, or the specific type of question that shows up. The good news is that these screening tests are highly predictable. Once you understand what they're actually testing and where candidates reliably go wrong, you can prepare with surgical precision.
By the end of this lesson, you'll know exactly what SQL and Python screening tests look like, what competencies they're measuring, how to structure your preparation in the days leading up to an assessment, and — most importantly — how to avoid the small, preventable mistakes that get good candidates eliminated before a recruiter even looks at their portfolio.
What you'll learn:
- The anatomy of a recruiter-sent screening test and what platforms typically deliver them
- What SQL concepts are almost always tested and how to approach them under time pressure
- What Python tasks appear most frequently and what evaluators are actually looking for in your code
- A targeted preparation routine you can execute in 3–5 days before a test
- The most common eliminating mistakes and exactly how to avoid each one
Prerequisites
You should have basic familiarity with SQL (writing SELECT queries, filtering with WHERE) and have written at least a little Python (variables, loops, simple functions). You don't need to be advanced — this lesson is specifically designed for people who have foundational knowledge but haven't yet been tested on it professionally. If you're still deciding which tool to learn first, How to Choose Your First Data Tool to Learn: Python vs SQL vs Excel Based on the Role You Want is a good place to start.
What These Tests Actually Are (and Why Companies Use Them)
Before you can pass a screening test, you need to understand its purpose. Recruiters typically send these assessments after your resume clears an initial review — they're a cheap, scalable way to filter a pool of 50 applicants down to 8 before anyone spends 30 minutes on a phone call.
The tests are almost always delivered through one of a handful of platforms: HackerRank, Codility, Stratascratch, TestGorilla, or occasionally a company's own internal tool. Each has a slightly different interface, but they all share the same core structure: you get a dataset or schema description, a problem prompt, and a code editor where you write your answer. Most platforms run your code against hidden test cases and return a score — so you often won't know exactly where you went wrong.
The time limits vary. Some assessments give you 90 minutes for 5 questions. Others give you 30 minutes for 2 questions. Occasionally a recruiter will send a "take-home" style SQL test with no time limit, but these are less common at the screening stage.
Key insight: These tests are not measuring whether you can write perfect, production-grade code. They're measuring whether you have genuine baseline competency — whether you can read a table, understand what question is being asked of the data, and write code that produces the right answer. The bar is "can this person work independently with data?" not "is this person a senior engineer?"
Understanding this lowers the mental stakes considerably. You're not expected to be flawless. You're expected to be functional.
The SQL Questions You Will Almost Certainly See
SQL dominates data screening tests. Even roles that are primarily Python-focused will often include SQL questions, because SQL is the language almost every data professional uses every day regardless of specialization.
Here are the five question types that appear with near-certainty:
1. Aggregation with GROUP BY
You'll be given a table — say, an orders table with columns like customer_id, order_date, product_category, and revenue — and asked something like "find total revenue by category" or "count the number of orders per customer."
SELECT
product_category,
SUM(revenue) AS total_revenue,
COUNT(order_id) AS order_count
FROM orders
GROUP BY product_category
ORDER BY total_revenue DESC;
This is table stakes. If you can't write this cleanly and correctly, you will not pass. Practice until this syntax is automatic.
2. Filtering Aggregated Results with HAVING
A very common follow-up to GROUP BY problems asks you to filter the grouped results — for example, "find customers who placed more than 5 orders." Candidates who confuse WHERE and HAVING get eliminated here.
SELECT
customer_id,
COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(order_id) > 5;
The rule: WHERE filters rows before grouping. HAVING filters groups after aggregation. Memorize this. Write it on a sticky note if you have to.
3. JOINs Across Multiple Tables
Expect at least one JOIN question. The scenario is almost always business-realistic: you have a customers table and an orders table, and you need to combine them to answer a question like "find the total revenue for each customer along with their name and signup date."
SELECT
c.customer_name,
c.signup_date,
SUM(o.revenue) AS total_revenue
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_name, c.signup_date
ORDER BY total_revenue DESC;
Pay attention to the JOIN type. LEFT JOIN keeps all customers even if they have zero orders (they'll show NULL for revenue). INNER JOIN would drop customers with no orders. Many tests have a right answer that depends on choosing the correct join type — read the problem carefully.
4. Window Functions
This is where many candidates lose points. Window functions — specifically ROW_NUMBER(), RANK(), LAG(), and SUM() OVER() — appear frequently on analyst and data scientist screening tests, and they trip up candidates who learned only the basics.
A classic window function question: "For each customer, find the date of their second-most-recent order."
WITH ranked_orders AS (
SELECT
customer_id,
order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
)
SELECT customer_id, order_date
FROM ranked_orders
WHERE rn = 2;
If window functions are new to you, invest time here before any assessment. For a full breakdown with progressively harder examples, Master SQL Interview Questions: From Basic Queries to Advanced Analytics with Complete Solutions covers this terrain thoroughly.
5. Subqueries and CTEs
You'll often need to nest logic — either using a subquery (a SELECT inside another SELECT) or a Common Table Expression (CTE, written with the WITH keyword). CTEs are generally preferred because they're easier to read and debug.
Tip: When a problem feels complex, break it into steps. Write the inner query first, verify it produces the right intermediate result in your head, then wrap it. Don't try to write a three-level nested query in one shot.
The Python Questions You Will Almost Certainly See
Python screening questions at the analyst level almost always involve pandas, the data manipulation library that is the Python equivalent of SQL for tabular data. You may also see pure Python logic questions involving lists, dictionaries, or string manipulation.
Pandas DataFrame Operations
The most common scenario: you're given a DataFrame (or told to create one from a dictionary), and you're asked to filter, group, sort, or transform it.
import pandas as pd
# Typical setup — the test may give you a CSV path or a dict like this
data = {
'customer_id': [1, 2, 1, 3, 2, 1],
'product': ['laptop', 'phone', 'tablet', 'laptop', 'tablet', 'phone'],
'revenue': [1200, 800, 450, 1100, 400, 750]
}
df = pd.DataFrame(data)
# Common question: total revenue per customer, sorted descending
result = (
df.groupby('customer_id')['revenue']
.sum()
.reset_index()
.sort_values('revenue', ascending=False)
.rename(columns={'revenue': 'total_revenue'})
)
print(result)
Notice the chained method style. pandas operations are designed to be chained, and writing them this way — one operation per line — makes your code readable and debuggable. Evaluators notice clean code. It signals professional habits even at a junior level.
Handling Missing Data
Nearly every real-world dataset has nulls, and tests reflect this. You should know df.isnull().sum() to check for nulls, df.dropna() to remove them, and df.fillna(value) to replace them. A question might say "calculate average revenue per product, excluding missing values" — which is actually pandas' default behavior for .mean(), but you should know that explicitly.
String Operations and Apply
Some Python questions test whether you can clean or transform text columns. For instance: "extract the domain from a list of email addresses."
df['domain'] = df['email'].str.split('@').str[1]
If the transformation is more complex, you'll use .apply() with a lambda or a named function. Don't overcomplicate it — write a function that works for one row, then apply it.
Pure Python Logic
Occasionally a screening test will include a pure Python question with no pandas — something like "write a function that takes a list of transaction amounts and returns only those above the 90th percentile." This tests whether you can think algorithmically, not just use library functions.
def above_90th_percentile(amounts):
if not amounts:
return []
threshold = sorted(amounts)[int(len(amounts) * 0.9)]
return [x for x in amounts if x >= threshold]
Warning: Don't import libraries you don't need on pure Python questions. If you reach for import numpy to calculate a percentile when the problem could be solved with basic Python, it can signal over-reliance on tools rather than understanding. Use libraries when they genuinely simplify the solution, not as a crutch.
How to Prepare in the 3–5 Days Before a Test
You've received a screening test invite. Here's a focused preparation routine that actually works.
Day 1 — Diagnose your gaps. Go to a free practice platform (StrataScratch has free SQL problems with real interview questions tagged by company; LeetCode's database section is also solid). Attempt 5–8 SQL questions covering GROUP BY, JOINs, and window functions. Note where you hesitate, get the wrong answer, or have to look something up. That list is your study plan.
Day 2 — Drill SQL weak spots. Focus entirely on whatever tripped you up on Day 1. If window functions caused trouble, do ten window function problems in a row until the PARTITION BY / ORDER BY syntax is automatic. If JOIN types confused you, sketch out three scenarios and reason through which join type each requires before writing code.
Day 3 — Practice pandas. Load a dataset (the Titanic dataset, NYC taxi data, or any CSV from Kaggle) into a Jupyter notebook and practice common operations from memory: groupby, merge (equivalent to SQL JOIN), filtering with boolean masks, handling nulls, sorting, and renaming columns. Don't look things up — if you can't remember the syntax, make your best guess, run it, and observe the error. That error-and-correct loop builds actual memory.
Day 4 — Simulate test conditions. Pick 3 SQL problems and 2 Python problems, set a timer for 45 minutes, and do them in a plain text editor rather than a comfortable IDE with autocomplete. This is the single most important practice step. Most candidates practice in comfortable environments and then freeze when they're in an unfamiliar browser-based editor.
Day 5 — Rest and review. Don't cram new material. Review your notes from the week, re-read problems you got wrong, and make sure you can explain why your solution works, not just that it produces the right output.
Tip: If you want to build deeper, portfolio-ready competence rather than just passing a screening test, How to Set Up Your First Data Analyst Home Lab: Tools, Datasets, and Practice Projects to Build Real Skills Before Your First Job walks you through building a real practice environment from scratch.
What Evaluators Are Actually Looking For
When a technical reviewer looks at your screening test answers — and they do look, especially when you're borderline — they're not just checking if your output matched the expected answer. They're reading your code as a signal of how you'll behave on the job.
Readable code matters. Use aliases. Name your CTEs descriptively. Don't write a 200-character SELECT statement on one line. Break it up. Add a comment if a step is non-obvious. These habits signal someone who writes code for other people to read, not just for themselves.
Edge case awareness. If a problem says "find the top customer by revenue," a thoughtful candidate might wonder: what if two customers have the same revenue? Do I use RANK() or ROW_NUMBER()? Sometimes the test won't specify, but writing a solution that handles ties demonstrates real-world thinking.
Correct JOIN semantics. This trips up many beginners. If you use INNER JOIN when LEFT JOIN was required, you'll get a "correct-looking" answer that silently drops rows. The test's hidden cases will catch this.
Hands-On Exercise
Before your next screening test, complete the following practice sequence:
Problem 1 — SQL: You have two tables. employees has columns employee_id, name, department_id, and salary. departments has department_id and department_name. Write a query that returns each department name, the number of employees in it, and the average salary — but only for departments where the average salary exceeds $70,000. Sort results from highest to lowest average salary.
Try writing this without looking anything up. Then check: did you use HAVING correctly? Did you join on the right key? Did you include department_name in your GROUP BY?
Problem 2 — Python: Create a pandas DataFrame with columns date, product, and units_sold representing 10 rows of sales data (make up realistic values). Then write code to: (a) calculate total units sold per product, (b) identify the product with the highest total, and (c) add a column to the original DataFrame indicating whether each row is above or below the median for its product.
Step (c) is the hard part — it requires a groupby transform, which is a common source of confusion. The syntax is df.groupby('product')['units_sold'].transform('median').
Common Mistakes & Troubleshooting
Mistake 1: Not reading the schema carefully. Candidates assume column names based on what feels intuitive and then wonder why their query returns an error. Always read the schema description. Notice whether the join key is customer_id in both tables or id in one and customer_id in the other. One wrong column name and your entire query fails.
Mistake 2: Writing WHERE when you need HAVING. This is the single most common SQL error in screening tests. If you're filtering on an aggregated value (SUM, COUNT, AVG), use HAVING. If you're filtering on a raw column value, use WHERE. Both can appear in the same query.
Mistake 3: Forgetting to reset_index() in pandas after groupby. After a .groupby().sum(), the grouping column becomes the DataFrame's index, not a regular column. If you then try to reference it as a column, you'll get a KeyError. Always chain .reset_index() unless you specifically want the index behavior.
Mistake 4: Testing only the happy path. Your solution works on the example given in the prompt. Then the hidden test cases include NULLs, empty groups, or duplicate values — and your code breaks. Before submitting, mentally walk through: "What happens if a value is NULL? What if a group has only one member? What if two rows are tied?"
Mistake 5: Running out of time by over-engineering. Candidates with some experience sometimes spend 20 minutes optimizing a query that a simpler version would have answered correctly in 5 minutes. A working, slightly inelegant solution scores far higher than a partially-written elegant one. Get to a working answer first, then refine if time permits.
Warning: Don't Google answers during a timed assessment if the platform has browser-monitoring enabled (some do, and many will ask you to agree to monitoring before starting). Beyond the ethical issue, getting caught immediately disqualifies you and may get you flagged with that recruiter network. Practice enough beforehand that you don't need to.
Mistake 6: Submitting without checking column names in the output. Many tests compare your output column names to an expected schema. If the expected output has a column called total_revenue and you returned one called sum_revenue, you may get marked wrong even if the numbers are correct. Read the expected output format in the prompt before finalizing.
Summary & Next Steps
SQL and Python screening tests are highly predictable, and that predictability is your advantage. The vast majority of analyst-level assessments test the same core concepts: GROUP BY with aggregation, HAVING for filtered aggregates, JOINs across related tables, window functions for ranking and running totals, pandas groupby and merge operations, and basic Python logic. If you can execute those competently under mild time pressure, you will pass most screening tests you encounter.
The candidates who get eliminated aren't usually people who lack the knowledge. They're people who practiced in comfortable environments, didn't simulate test conditions, and got tripped up by familiar concepts presented in unfamiliar formats. A targeted 3–5 day preparation routine — diagnosing gaps, drilling weak spots, and running timed simulations in a plain editor — is enough to put you in a completely different category of prepared.
Once you've passed the screening test, the interview process continues. Acing the Data Analyst Technical Interview will walk you through what happens next, including the live coding components and case-study questions that follow a successful screen. And if you want to understand how the full hiring process fits together — from application to offer — Navigating the Data Hiring Process: How to Evaluate Offers, Compare Teams, and Choose the Right First Role gives you the full picture.
The screening test is a hurdle, but it's a learnable one. Start your practice session today, not the night before the link arrives.
