SQL Interview: Aggregation, GROUP BY and Subqueries

Aggregation

Q. COUNT(*) versus COUNT(column) versus COUNT(DISTINCT column)?

COUNT(*) counts rows. COUNT(column) counts non NULL values in that column. COUNT(DISTINCT column) counts distinct non NULL values. The gap between the first two is exactly the number of missing values.

Q. How do aggregates treat NULL?

Every aggregate except COUNT(*) ignores NULL. So AVG(bonus) is SUM(bonus) / COUNT(bonus), not divided by the row count - employees with no bonus are excluded from the denominator entirely. If they should count as zero, write AVG(COALESCE(bonus, 0)).

Q. What does SUM return over zero rows?

NULL, not zero. COUNT returns 0. Wrap it: COALESCE(SUM(total), 0).

GROUP BY

Q. What is the rule for the SELECT list with GROUP BY?

Every selected column must either appear in the GROUP BY or be inside an aggregate. PostgreSQL, SQL Server and Oracle reject anything else; MySQL 8 rejects it too by default under ONLY_FULL_GROUP_BY, and older or loosened MySQL returns an arbitrary value from the group - which looks like it worked and is not reproducible.

Q. WHERE versus HAVING?

Put row conditions in WHERE - it reduces the work before grouping. Use HAVING only for conditions on aggregates.

Q. What is the logical order of evaluation of a SELECT?

FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT. Two consequences worth stating: a SELECT alias is unavailable in WHERE but available in ORDER BY, and window functions run after HAVING so they cannot be filtered without a subquery.

Q. How do you group by month?

SELECT EXTRACT(YEAR FROM order_date)  AS yr,
       EXTRACT(MONTH FROM order_date) AS mth,
       COUNT(*), SUM(total)
FROM   orders
GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)
ORDER BY yr, mth;

The follow up: how do you show months with no orders? Generate the month list and left join to it - grouping can only produce rows for values present in the data.

Subqueries

Q. Correlated versus uncorrelated?

An uncorrelated subquery is independent and can be run on its own; it is evaluated once. A correlated subquery references a column from the outer query and is conceptually evaluated once per outer row.

Q. IN versus EXISTS?

Both test membership. EXISTS stops at the first match and is NULL safe. NOT IN is not: if the subquery returns even one NULL, NOT IN returns no rows at all, because the comparison becomes UNKNOWN. Prefer NOT EXISTS.

-- Returns nothing if any employee has a NULL dept_id
SELECT name FROM departments WHERE id NOT IN (SELECT dept_id FROM employees);

-- Correct regardless
SELECT d.name FROM departments d
WHERE  NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

Q. Join or subquery - which is faster?

Usually the same. Modern optimisers compile IN, EXISTS and semi joins to similar plans. Choose by intent: a join when you need columns from the other table, EXISTS when you only need to know whether a match exists. The real difference is that a join can duplicate rows and EXISTS cannot.

Q. Find the second highest salary.

-- With window functions
WITH ranked AS (
    SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM   employees
)
SELECT salary FROM ranked WHERE rnk = 2;

-- Without them, for an older MySQL
SELECT MAX(salary) FROM employees
WHERE  salary < (SELECT MAX(salary) FROM employees);

Say why you chose DENSE_RANK: two people on the top salary should not push the "second highest salary" down to third place.

Q. Find duplicates.

SELECT email, COUNT(*) AS copies
FROM   employees
GROUP BY email
HAVING COUNT(*) > 1;

The follow up: now delete all but one. Number them with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete where rn > 1 - then add the UNIQUE constraint, because the cleanup is not the fix.

Questions to have an answer ready for

1. Why does AVG ignore NULLs, and when is that the wrong answer for a report?
2. Write the query for departments whose average salary exceeds 80000, excluding inactive staff.
3. Explain the NOT IN NULL trap to someone who has not met it.
4. Write the Nth highest salary query two different ways.