Deloitte SQL Interview Questions

Using DENSE_RANK: SELECT dept_id, salary FROM (SELECT dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM employees) r WHERE rnk = 2. The DENSE_RANK approach handles ties correctly and is easily extensible to any Nth rank — preferred over nested MAX at Deloitte.

A CTE is a named temporary result set defined with WITH clause, existing for the duration of the query. Use CTEs when: referencing the same derived dataset multiple times (avoids repetition), needing recursive queries for org hierarchies, or needing readability for compliance audits. A CTE named monthly_revenue_by_region is self-documenting for client handover.

Logical execution order: FROM (load tables and apply JOINs), WHERE (filter rows), GROUP BY (aggregate), HAVING (filter groups), SELECT (compute column expressions), DISTINCT (remove duplicates), ORDER BY (sort), LIMIT/TOP (restrict rows). Critical insight: you cannot use a SELECT alias in WHERE because WHERE runs before SELECT, but you CAN use it in ORDER BY.

WHERE filters individual rows before aggregation — cannot reference aggregate functions. HAVING filters groups after GROUP BY is applied — can reference aggregates. Example: WHERE status = 'Active' eliminates inactive employees before grouping; HAVING COUNT(*) > 10 keeps only departments with more than 10 active people after grouping. At Deloitte, this distinction matters in financial summaries where pre-filter and post-aggregate filters have very different business meanings.

Detect: SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1. Remove via ROW_NUMBER CTE: WITH deduped AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_date DESC) AS rn FROM customers) DELETE FROM deduped WHERE rn > 1. Keeps most recent record per email. Always validate counts before deleting in Deloitte production environments.

An index is a B-tree data structure allowing the engine to locate rows without full table scans. Clustered index: determines physical storage order of rows — one per table (typically PK). Non-clustered: separate structure with row pointers — multiple allowed. Index columns appearing in WHERE, JOIN ON, and ORDER BY clauses. Downside: every INSERT/UPDATE/DELETE must update all indexes — over-indexing slows write-heavy tables.