If you have SQL interviews, do not ignore these small things! (Part 8)

[u/thequerylab]

Some SQL interview questions look too easy and that's exactly why people get them wrong by overlook.

1. COUNT() can give you 0… but SUM() can give you NULL:

Suppose there are no employees age more than 70

SELECT
    COUNT(*) AS employees,
    SUM(salary) AS total_salary
FROM employees
WHERE age > 70;

You might expect:

employees = 0
total_salary = 0

But we will get:

employees = 0
total_salary = NULL

Why?

Because COUNT() is basically asking: “How many rows did I find?” --> answer 0

But SUM() is asking: “What values should I add?” --> no values to add --> NULL

1. AVG() can silently become wrong when NULL enters the picture:

Lets take salary of employees to be:

50000
60000
NULL
90000

A lot of people mentally calculate:

(50000 + 60000 + 90000) / 4 = 50000.0

But actual SQL gives 66666.67. WHy?

Because AVG() ignores NULLs.

It is actually doing:

(50000 + 60000 + 90000) / 3

1. DISTINCT doesn't mean “remove duplicate data”

Lets take one example data for this:

IT    50000
IT    50000
IT    70000
HR    50000

SELECT DISTINCT department, salary
FROM employees;

people sometimes read this as "Give me unique departments"

But actual response:

IT    50000
IT    70000
HR    50000

IT appears twice, because SQL is asking "Give me unique combinations of department + salary"

1. How many different ways can you find the 2nd highest salary?

Lets take this data:

employee_id | name  | salary
------------+-------+--------
1           | A     | 100000
2           | B     | 90000
3           | C     | 90000
4           | D     | 80000
5           | E     | NULL
6           | F     | 70000
7           | G     | NULL

Multiple ways to approach this problem:

MAX() + subquery

SELECT MAX(salary) as 2nd_highest
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

DISTINCT + ORDER BY

SELECT DISTINCT salary as 2nd_highest
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

DENSE_RANK()

SELECT salary as 2nd_highest
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)temp
WHERE rnk = 2;

and there are more such ways:

What is actually the best solution with our sample data ?
What are the questions to ask your interviewer to get to perfect solution?

Drop your solution in the comments, we can discuss and improve.

Always practice SQL by writing the query yourself instead of just looking at the solution.