SQL for AI and ML roles: what interviews test
SQL for machine learning interviews: the joins, window functions and aggregations that come up, and how to write feature queries that do not leak the future.
On this page
SQL for machine learning roles is tested on joins, aggregation, window functions and one topic that general SQL rounds ignore: writing feature queries that do not leak future information into training data. Interviewers are not looking for obscure syntax. They want to see that you can turn raw event tables into a correct training set, with exactly one row per example and nothing in it that the model could not have known at prediction time.
SQL is core for AI data engineers and AI backend engineers and expected at working level for every other AI role, so it belongs near the start of the AI engineer roadmap alongside Python.
How much SQL do AI and ML roles need?
AI and ML roles need SQL up to window functions and multi-step queries with common table expressions; depth beyond that depends on the role. Everyone should be able to build a clean dataset from several tables. Data-heavy and backend roles go further into modelling, performance and incremental pipelines.
| Role | SQL depth expected | Typical interview task |
|---|---|---|
| AI data engineer | Deep: modelling, performance, incremental loads | Design tables and write a pipeline query that handles late data |
| AI backend engineer | Core: application queries, transactions, schema design | Query and update application data correctly under load |
| Data or applied scientist | Working, analysis-heavy: windows, cohorts, experiment metrics | Compute retention or a test metric from raw events |
| ML engineer | Working: joins, windows, feature queries | Build a training set without leakage |
| GenAI and LLM engineer | Working: joins, filters, aggregates | Analyse application logs or evaluation results |
| AI platform and MLOps engineer | Working: metadata and log queries | Find which runs or deployments match a condition |
The AI data engineer role is where SQL carries the most weight, because that role owns the tables every model reads from.
Which SQL topics come up most in interviews?
The SQL topics that come up most are joins, grouped aggregation, window functions and date handling, usually combined in one question. A typical prompt gives you two or three tables, such as users, orders and events, and asks for a result that needs all four.
Joins. Know inner, left and anti joins, and know what each does to row counts. The classic trap is fan-out: joining orders to order items and then summing an order-level amount counts that amount once per item. The defence is to aggregate each table to the grain you need before joining.
Aggregation. Know the difference between WHERE and HAVING, how COUNT(*) differs from COUNT(column) when the column has NULLs, and how to do conditional aggregation with CASE inside SUM or COUNT. NULL handling is a favourite follow-up: NULL = NULL is not true, and NOT IN against a list containing NULL returns no rows.
Window functions. These compute a value per row using a set of related rows, without collapsing them. They are the natural tool for the questions AI teams ask every day.
| Function | What it does | Typical AI use |
|---|---|---|
| ROW_NUMBER | Numbers rows within a partition, no ties | Keep the latest record per customer |
| RANK and DENSE_RANK | Rank with ties, with or without gaps | Top items per category |
| LAG and LEAD | Read the previous or next row | Time since last event, change from prior value |
| SUM or AVG over a frame | Running or rolling aggregate | Rolling activity features |
Keeping the latest row per entity is the single most common pattern:
SELECT customer_id, plan, updated_at
FROM (
SELECT
customer_id,
plan,
updated_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC
) AS rn
FROM customer_plans
) AS ranked
WHERE rn = 1;Be ready to say what happens on ties. If two rows share the same updated_at, ROW_NUMBER picks one arbitrarily, so a production query adds a second ordering column to make the choice stable.
What is data leakage in a feature query?
Data leakage in a feature query is when a training row includes information from after the moment the prediction would have been made. The model learns from the future, scores well offline and then fails in production, where the future is not available. Leakage is a query bug as often as a modelling bug, which is why SQL rounds for AI roles test it.
The usual cause is computing a feature over a customer's whole history instead of the history up to the prediction time. Suppose you are predicting loan default at the moment of application. A feature such as "total transactions" computed today includes transactions made after the application, some of which exist only because the loan was granted.
Leaks the future
- Joins all of a customer's transactions
- Uses the customer table as it is today
- Feature values change if you rerun the query next month
- Offline metrics look better than production
Point-in-time correct
- Joins only transactions before the application time
- Uses the customer attributes valid at that time
- Rerunning later gives the same training rows
- Offline metrics match what production will see
The fix is to anchor every join to the prediction timestamp:
-- One row per loan application, using only data known before it.
SELECT
a.application_id,
a.customer_id,
a.applied_at,
COUNT(t.txn_id) AS txn_count_90d,
COALESCE(SUM(t.amount), 0) AS txn_amount_90d
FROM applications AS a
LEFT JOIN transactions AS t
ON t.customer_id = a.customer_id
AND t.txn_at < a.applied_at
AND t.txn_at >= a.applied_at - INTERVAL '90 days'
GROUP BY a.application_id, a.customer_id, a.applied_at;Three choices in that query are deliberate. The time conditions sit in the ON clause, not in WHERE, so an applicant with no transactions keeps a row with zeros instead of disappearing. COUNT(t.txn_id) counts matched rows only, where COUNT(*) would return one for an applicant with none. And the strict < excludes anything at or after the application time.
Leakage has other forms beyond time, including target leakage and leakage between train and test sets; machine learning fundamentals covers those.
How should you approach a SQL question in an AI interview?
Approach a SQL question by stating the grain of the answer before writing anything, then building the query in small verified steps. Interviewers follow your reasoning more easily, and the grain statement alone prevents most fan-out mistakes.
State the grain
Say what one row of the result represents: one customer, one application, one user per day.
Check the keys
For each table, ask what makes a row unique and whether a join on it can multiply rows.
Fix the time anchor
If the result feeds a model, name the prediction time and filter every table relative to it.
Build in steps
Use common table expressions: aggregate each source to the target grain, then join the small results.
Test the edges
Walk through an entity with no matching rows, a NULL, and a tie, and say what the query returns.
Practise by writing queries against small tables you make up yourself, where you can count the right answer by hand. The same checking habit applies when you move the result into Pandas, as described in Python for AI engineers.
SQL questions also appear inside other rounds. A design discussion about a feature pipeline often turns into "write the query for that feature"; see the ML system design interview framework and coding interviews for AI roles for how the rounds fit together.
To see how much SQL your target role expects next to its other skills, compare the tracks in the role explorer.
Key takeaways
- SQL is core for AI data engineers and AI backend engineers, and expected at working level for every other AI role.
- Most wrong answers in SQL rounds come from join fan-out: a join that multiplies rows before an aggregate.
- Window functions answer the questions AI teams actually ask: latest record per entity, rolling activity, rank within a group.
- A feature query must be anchored to the prediction time, or the model trains on information it will not have in production.
- State the grain of your result, one row per what, before you write the query.
Frequently asked questions
Do GenAI and LLM engineers need SQL?
Yes, at a working level. LLM applications still read from databases: documents and metadata for retrieval, logs of prompts and responses, evaluation results and usage data. Analysing why a RAG system fails usually means querying those logs. A GenAI engineer rarely faces the hardest SQL round, but is expected to join, filter and aggregate without help.
Which SQL dialect should I practise for interviews?
Practise in any mainstream dialect with full window function support and learn the standard forms. Interviewers care about the logic, and most will accept small syntax differences in date arithmetic or string functions if you say which dialect you are writing. Avoid depending on features that exist in only one engine.
Is Pandas a substitute for SQL in machine learning work?
No. Pandas works on data that fits in memory on one machine; production training data usually lives in a warehouse and is far larger. The normal pattern is to do the heavy joins and aggregation in SQL, close to the data, and bring a much smaller result into Pandas for modelling. Interviews test both.
How do I explain a SQL answer in an interview?
Say the grain first: one row per customer, per order, per day. Then name the tables, the join keys and whether each join can duplicate rows. Write the query in steps with common table expressions, and finish by stating one edge case you checked, such as customers with no orders or tied ranks.
Do AI engineers need to know query optimisation?
A working understanding is enough for most AI roles: filter early, avoid selecting unused columns, know that joins on unindexed or skewed keys are slow, and read a query plan at a basic level. AI data engineers are tested more deeply, including partitioning, incremental loads and the cost of large scans.