SQL & pandas essentials for interviews

Many DS/analytics interviews include a live SQL (or pandas) round. The recurring patterns are aggregation, joins, window functions, and de-duplication — know these cold.

• GROUP BY + aggregates: counts/sums/averages per key; HAVING filters groups (vs WHERE, which filters rows before grouping).
• JOINs: inner vs left/right/full — and why a LEFT JOIN + IS NULL finds non-matches.
• Window functions: ROW_NUMBER(), RANK(), LAG() over PARTITION BY ... ORDER BY ... — for top-N-per-group, running totals, period-over-period.
• In pandas: groupby().agg(), merge(), pivot_table(), drop_duplicates(), and boolean masks mirror the same operations.

-- Top earner per department (classic window-function question)
SELECT department, name, salary
FROM (
  SELECT department, name, salary,
         RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 1;