Tech

SQL and data analysis

Write queries that return the right rows: count joins before running them, handle NULL without losing data, filter groups with HAVING, rank with window functions and keep queries fast and safe.

6 concepts7 min read25-question test, from a bank of 33Updated

Concept 1 of 61 min

Joins and how many rows they return

The rule
An INNER JOIN returns one row per matching pair, and a LEFT JOIN adds one row with NULL columns for each unmatched left row.
Worked examples

Counting joined rows

Given
Clients4
Orders, client 13
Orders, client 22
  1. INNER JOIN = 3 + 2 = 5 rows
  2. LEFT JOIN = 5 + 2 = 7 rows

The LEFT JOIN keeps clients 3 and 4, with NULLs.

A CROSS JOIN

Given
Clients4
Other table6 rows
  1. Rows = 4 × 6 = 24

A CROSS JOIN pairs every row with every row.

A sum inflated by a join

Given
Credit limit500
Trades3
  1. SUM after join = 3 × 500 = 1,500

The limit is counted once per trade, so aggregate before joining.

The picture
Formula
LEFT JOIN trades t ON t.client_id=c.id AND t.status = 'open'
rows of a CROSS JOIN=rows of A × rows of B
Why it holds

A CROSS JOIN returns every pair, and a condition on the right table in WHERE turns a LEFT JOIN into an inner one.

A join to a table with several rows per key repeats the left rows, and UNION removes duplicates while UNION ALL keeps them. Count the rows of a join before running it and most bugs show up.

Unmatched clients get NULL in the right table's columns, so WHERE t.status = 'open' drops them: put the condition in the ON clause to keep every client. Summing a client column after joining to trades counts it once per trade: aggregate before joining.

Removing duplicates needs a sort or a hash, so UNION ALL is cheaper when duplicates are impossible or wanted.

Watch out
  • Filtering the right table in WHERE after a LEFT JOIN.
  • Summing a parent column after joining to child rows.
  • Using UNION when UNION ALL is meant.
In the interview
  1. After a join, your table has more rows than before. What happened, and how do you fix it?

  2. What is the difference between an inner join and a left join? When would you use each?

  3. A report of revenue per client doubled after someone added a join to the contacts table. How would you find and fix the cause?

    A complete answer covers

    • Some clients have several contacts, so their rows repeat.
    • Count rows per client before and after the join.
    • Aggregate revenue first, then join, or join to one contact per client.
    • Add a check that totals match the source.

Also in our technical interviews, on camera, with a score.

Concept 2 of 61 min

NULL: three-valued logic

The rule
Any comparison with NULL is unknown, so WHERE status = NULL returns nothing and IS NULL is the only test.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 3 of 61 min

GROUP BY, HAVING and the order of evaluation

The rule
A query runs logically FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, so WHERE filters rows before grouping and HAVING filters groups.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 4 of 61 min

Window functions: running totals, ranks and changes

The rule
A window function adds a value to each row without collapsing them: SUM(pnl) OVER (ORDER BY day) is a running total.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 5 of 61 min

Indexes, transactions and silent traps

The rule
An index speeds reads but slows writes, and a composite index on (desk, trade_date) serves desk or desk and date but not date alone.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Concept 6 of 61 min

AdvancedWhen the planner ignores your index

The rule
An index pays only for selective filters: each matching row costs about one random page read, while a full scan reads every page in sequence.

The why, the charts, the worked example and the interview questions are in Essentials, with every quiz.

See the plansSign in

Recap

  • An inner join returns one row per matching pair, a left join adds unmatched left rows, a cross join multiplies; a WHERE on the right table turns a LEFT JOIN into an inner one.
  • NULL is unknown: = NULL is never true, NOT IN with a NULL returns nothing, aggregates skip NULL and COUNT(*) does not.
  • Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; WHERE filters rows, HAVING filters groups.
  • Window functions keep every row: running totals, RANK, ROW_NUMBER for the top of each group, LAG for changes.
  • Indexes speed reads and slow writes; a composite index starts from its leftmost column; transactions are all or nothing.
Now test it

25 questions on SQL and data analysis

Timed, like the real one, drawn from a bank of 33. Every answer explained.

Test yourself →