Coding
What to Know
Coding questions test practical SQL and scripting logic for data work. Expect SQL: SELECT order of execution (FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT), joins, subqueries, common table expressions, window functions (ROW_NUMBER, RANK, LAG/LEAD, running SUM OVER), CASE expressions, and aggregation with HAVING. Know the difference between WHERE (filters rows before aggregation) and HAVING (filters groups after). Understand window function framing and partition versus group. Also expect basic Python or pseudocode: loops, list/dict operations, functions, and reading or transforming data with libraries conceptually. Debugging skills matter: reading error messages, checking types, and handling nulls.
Traps
Classic traps: using a SELECT alias in WHERE (not allowed in most engines), confusing ROW_NUMBER (unique) with RANK (ties share a rank with gaps), and forgetting that COUNT with GROUP BY excludes null groups. Off-by-one errors in LAG/LEAD defaults, integer division truncating results, and string comparison case sensitivity are common. Another trap is assuming subquery order is preserved without ORDER BY. In Python, mutable default arguments and modifying a list while iterating cause subtle bugs. Watch for questions asking for the second-highest value, where DISTINCT plus ORDER BY plus LIMIT/OFFSET or a window function both work.
Worked Example
To find each customer's most recent order, use a CTE with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn, then filter WHERE rn = 1. Rule of thumb: for top-N-per-group, window functions beat correlated subqueries in readability and often performance. Remember window functions run after WHERE, so filter base rows first, then rank.