Research and data roles frequently include SQL, and the bar is higher than basic selects.
Window functions
The distinguishing skill. Know these cold:
- ROW_NUMBER, RANK, DENSE_RANK - and how they differ on ties.
- LAG and LEAD - previous and next row, which is how you compute returns from a price series.
- SUM and AVG OVER with a window frame - running totals and moving averages.
- PARTITION BY - the same computation applied per group.
Computing a daily return per instrument is the canonical question: use LAG over a partition by instrument ordered by date.
Joins
Be precise about behaviour, because this is where errors hide:
- INNER keeps only matches.
- LEFT keeps all left rows, filling unmatched right columns with NULL.
- FULL OUTER keeps everything.
- CROSS produces the Cartesian product.
The trap: a LEFT JOIN followed by a WHERE clause on the right table's columns silently becomes an inner join, because NULLs fail the predicate. Put the condition in the ON clause instead. Interviewers use this deliberately.
NULL semantics
NULL is not equal to anything, including NULL. Use IS NULL. Aggregates skip NULLs, but COUNT(*) does not skip rows. NOT IN with a NULL in the subquery returns no rows at all, which is a classic silent bug.
Time-series patterns
- Gaps and islands - finding consecutive runs, usually with a row-number difference trick.
- As-of joins - matching each trade to the most recent quote before it. Some databases support this natively; otherwise a correlated subquery or window function does it.
- Resampling - bucketing timestamps to intervals with date truncation.
Talk about cost
A strong answer mentions what the query will do at scale: which columns should be indexed, whether the join is on an indexed key, whether a window function forces a sort. Research datasets are large enough that this matters.
Practise in programming and DSA.