Data science & AI interview questions, answered the way you’d say them.

An INNER JOIN returns only the rows that match on both sides. A LEFT JOIN returns every left-table row, with NULLs where nothing matched on the right; a RIGHT JOIN is the same thing reversed, and most people just write it as a LEFT JOIN. A FULL OUTER JOIN returns everything from both tables, but MySQL lacks it, so there you add the unmatched RIGHT JOIN rows to a LEFT JOIN.

In MySQL, stack the two with UNION ALL, keeping only the RIGHT JOIN rows where the left side is NULL.

MySQL 8.4 manual: JOIN Clause

More SQL questionsThe full answer, in the guidePractise it in the SQL LabPractise it with Prism

First confirm the drop is real: check tracking, the data pipeline and the metric’s definition, because a broken event or late data can look exactly like a fall in demand. Then find when it started, split orders into sessions × conversion, and break both down by platform, app version, city, channel, new versus returning users and payment mode to see where it sits. Only then test causes, internal or external, against the data.

1. Is it real?   tracking, pipeline, definition, time zone
2. When?         sudden or gradual; which day; the same week last year
3. Where?        orders = sessions × conversion; by platform, version, city, channel
4. Why?          internal: release, outage, price change; external: festival, competitor sale
5. So what?      size the impact, fix or monitor, tell the people affected

Ask clarifying questions first, such as how the 12% was measured and against which baseline.

Dmitriev et al. (2017), A dirty dozen: twelve common metric interpretation pitfalls in online controlled experiments, KDD

More Cases and guesstimates questionsPractise it with Prism

Logically, SQL runs FROM and the joins first, then WHERE, GROUP BY, HAVING, SELECT (where window functions run), DISTINCT, ORDER BY and finally LIMIT. That explains why a SELECT alias works in ORDER BY but not in WHERE, and why filtering on a window function needs a subquery or CTE. One dialect detail: PostgreSQL accepts aliases in GROUP BY but not in HAVING, while MySQL accepts both.

PostgreSQL 18 docs: SELECT

More SQL questionsThe full answer, in the guidePractise it in the SQL LabPractise it with Prism

All three are window functions: they calculate across rows related to the current row and, unlike GROUP BY, keep every row. They differ only on ties: ROW_NUMBER never repeats, RANK repeats a number and then skips, and DENSE_RANK repeats without skipping. So two salaries tied for third get 3 and 4 from ROW_NUMBER, 3 and 3 then 5 from RANK, and 3 and 3 then 4 from DENSE_RANK.

In MySQL 8, don’t name a column rank: it’s a reserved word.

PostgreSQL 18 docs: Window FunctionsMySQL 8.4 manual: Keywords and Reserved Words

More SQL questionsThe full answer, in the guidePractise it in the SQL LabPractise it with Prism

Rank inside each department with DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) in a CTE, then keep the rows where the rank is 2. PARTITION BY is what restarts the ranking for every department. Changing the filter to rnk <= 3 gives the top three earners in each department instead.

WITH ranked AS (
  SELECT department, name, salary,
         DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rnk = 2;

More SQL questionsThe full answer, in the guidePractise it in the SQL LabPractise it with Prism

Use a list for an ordered collection you’ll modify and a tuple for a record that stays fixed. A set keeps only unique values and is quick to answer “is this in here?”, and a dict maps keys to values. Checking membership in a set or dict takes about the same time at any size, while on a list it slows down as the list grows.

More Python questionsThe full answer, in the guidePractise it in the Python LabPractise it with Prism

It has seven stages. I ingest PDFs, tables and scans as clean text with metadata, because a parsing mistake spoils every step after it, then chunk with some overlap and index a vector per chunk, tagged with date and document type. Retrieval finds the top matches, often mixing vector and keyword search, and a re-ranker keeps the best few. The model answers from them with citations or admits the documents don’t cover it, and I score a fixed question set after every change.

More RAG questionsThe full answer, in the guidePractise it with Prism

Ingest each document with its metadata: owner, date and who may see it. Parse tables and scans carefully and chunk along headings. Retrieve with hybrid keyword and vector search, filtered by the user’s permissions, re-rank the top candidates, and have the model answer only from them, citing sources and saying when nothing covers the question. If live data is needed, such as ticket status, call tools with the user’s own access rights. Measure retrieval and faithfulness on a golden set, monitor feedback, latency and cost, and re-index as documents change.

Interviewers probe access control: filter by permission inside retrieval, never by asking the model to hide what a user mustn’t see.

Lewis et al., Retrieval-Augmented Generation for Knowledge-Intensive NLP Tasks (2020)OWASP Top 10 for LLM Applications 2025: LLM08 Vector and Embedding WeaknessesRagas documentation: Faithfulness

More System design questionsPractise it with Prism

WHERE filters individual rows before any grouping happens, while HAVING filters whole groups after aggregation, which is why only HAVING can use COUNT, SUM and the other aggregates. Put a condition in WHERE whenever it doesn’t need an aggregate, so fewer rows reach the grouping step.

Show both in one query, such as cities with at least 2 delivered orders: the status check goes in WHERE and the count in HAVING.

More SQL questionsThe full answer, in the guidePractise it in the SQL LabPractise it with Prism

A join pairs every match, so a key that appears m times on one side and n times on the other produces m × n rows. NULL never equals anything, not even another NULL, so a NULL key never matches. In the example, key 1 gives 2 × 3 = 6 rows, which is the inner join; a left join adds each unmatched left row once, making 8, a right join makes 7 and a full join 9.

left_t.k:   1, 1, 2, NULL          right_t.k:   1, 1, 1, 3
INNER JOIN   key 1 only: 2 × 3                     = 6 rows
LEFT JOIN    6 + unmatched left rows (2, NULL)     = 8 rows
RIGHT JOIN   6 + unmatched right row (3)           = 7 rows
FULL JOIN    6 + 2 + 1                             = 9 rows

In real work, compare row counts before and after a join: an unexpected jump usually means a key you assumed was unique isn’t.

PostgreSQL 18 docs: Table Expressions (joined tables)PostgreSQL 18 docs: Comparison Functions and Operators

More SQL questionsPractise it with Prism

Normalisation organises tables so each fact is stored once, avoiding redundancy and the inconsistencies of updating it in several places. In 1NF every value is atomic, with no repeating groups; in 2NF every non-key column depends on the whole key; in 3NF non-key columns depend only on the key, not on each other; BCNF tightens this so anything that determines another column is a superkey. The cost is more tables and joins, which is why reporting models often denormalise dimensions on purpose.

order_lines(order_id, product_id, customer_id, city, product_name, qty)   key: (order_id, product_id)
2NF breaks: customer_id needs only order_id, product_name only product_id  ->  move them out
3NF breaks: city depends on customer_id, a non-key column                   ->  move it out
Result:  orders(order_id, customer_id)       customers(customer_id, city)
         products(product_id, product_name)  order_items(order_id, product_id, qty)

Asked to normalise a table on the spot, name its key first, then test each column: does it depend on the whole key, and on nothing but the key?

IBM Db2 13 for z/OS docs: Normalization in database designSilberschatz, Korth and Sudarshan, Database System Concepts, 7th ed.: chapter 7 slides (relational design)Microsoft Learn: Understand star schema and the importance for Power BI

More SQL questionsPractise it with Prism

Three ways work. MAX(salary) below the overall maximum returns NULL when there’s no second value; DENSE_RANK handles ties and becomes any Nth by changing one number; LIMIT 1 OFFSET 1 needs DISTINCT, or a tie at the top breaks it. Offer more than one and say which edge cases each handles; on the example data, all three return 1800000.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

SELECT DISTINCT salary AS second_highest
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

The two edge cases to name: a tie for the top salary, and a table holding just one distinct salary.

More SQL questionsThe full answer, in the guidePractise it in the SQL LabPractise it with Prism