SQL Interview Questions: From Basics to Advanced Queries

Solution using HAVING: SELECT d.department_name, AVG(e.salary) as avg_dept_salary FROM employees e JOIN departments d ON e.department_id = d.id GROUP BY d.department_name HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees) Key concepts: • GROUP BY to aggregate by department • HAVING to filter groups (not WHERE — WHERE filters rows before aggregation, HAVING filters after) • A subquery for the company-wide average Alternative using a CTE: WITH company_avg AS ( SELECT AVG(salary) as avg_sal FROM employees ) SELECT d.department_name, AVG(e.salary) FROM employees e JOIN departments d ON e.department_id = d.id, company_avg GROUP BY d.department_name HAVING AVG(e.salary) > company_avg.avg_sal The CTE approach is more readable and avoids recomputing the subquery.

Solution: SELECT employee_name, department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as salary_rank FROM employees Understanding the variants: • RANK() — leaves gaps after ties (1, 1, 3) • DENSE_RANK() — doesn't leave gaps (1, 1, 2) • ROW_NUMBER() — assigns unique numbers even for ties (1, 2, 3 — non-deterministic for ties) When to use each: • Use RANK when you want to know position including ties • Use DENSE_RANK when you want the count of distinct values ahead of you • Use ROW_NUMBER when you need unique ordering (e.g., deduplication — take the first row per group) The PARTITION BY clause resets the ranking for each department; without it, you'd rank across the entire company.

Solution: SELECT date, revenue, AVG(revenue) OVER ( ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as rolling_7day_avg FROM daily_revenue ORDER BY date Key details: • ROWS BETWEEN 6 PRECEDING AND CURRENT ROW gives you exactly 7 rows (current + 6 prior) • The alternative RANGE BETWEEN is date-aware but works differently — it considers actual date values, not row positions Handling gaps in dates: For a true calendar-day rolling average with gaps, either: • Fill the gaps first (generate a date series and LEFT JOIN) • Use RANGE with an interval Edge case: The first 6 rows will have averages over fewer than 7 days. To exclude those, wrap in a subquery and filter WHERE row_number >= 7.

Step 1: Analyze the execution plan Run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to see the query plan. Look for: • Sequential scans on large tables (suggests a missing index) • Nested loop joins on large tables (consider hash or merge joins) • High estimated vs actual row counts (suggests stale statistics — run ANALYZE) Step 2: Check for missing indexes • If the WHERE clause filters on a column with no index, add one • But don't over-index — each index slows writes and consumes storage • Consider composite indexes for multi-column filters Step 3: Rewrite the query • Replace correlated subqueries with JOINs • Use EXISTS instead of IN for large subqueries • Avoid SELECT * — fetch only needed columns • Use LIMIT for exploratory queries Step 4: Consider the data model • Denormalization for read-heavy workloads • Materialized views for expensive aggregations • Partitioning for very large tables (by date, region, etc.)