Let's Talk About Interviews (Part 2) 😎

In the last post, we covered soft skills, the STAR methodology, and Excel. That's the basic set. Now let's move on to SQL.

If you've mastered Excel well, you already understand the basics: how data is linked, what problems arise with joins, how groupings work. Transitioning to SQL at the start is essentially 5 commands: SELECT, FROM, WHERE, JOIN, GROUP BY. Window functions and temporary tables are useful but not critical at the beginning.

Now to the main point. The main problem is not syntax. The problem is with blocks that don't break the query. The code runs, the interpreter is silent, but the result is not what the interviewer expects. This is where people fail.

1️⃣NOT IN + NULL: silence instead of data

In SQL: NULL ≠ empty ≠ 0. Those who studied Python know: zero is zero, NULL is emptiness, nothing. Keep that in mind.

Task: find users not in the blacklist.

❌
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM blocked)

Seems correct. But if blocked has even one row with user_id = NULL, the result is 0 rows. SQL cannot compare with NULL and silently returns emptiness.

✅
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM blocked b
WHERE b.user_id = u.id
)

By the way, NOT EXISTS is also faster: it takes an id, goes to the second table, finds the first match, stops. NOT IN scans the entire table from top to bottom. On large tables, the difference is noticeable.

2️⃣ LEFT JOIN that silently became INNER JOIN

We'll cover JOIN types in detail in future posts of this series. For now, just the essence of the trap.
❌
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'completed'

What happens: WHERE kills NULL rows from the right table. Customers without orders simply disappear. LEFT JOIN turned into INNER JOIN. And the result is off.
✅
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id
AND o.status = 'completed'

One line with AND. The difference is fundamental.

3️⃣AVG lies beautifully

Here's a task. 10 rows of sales. Three have NULL. What will AVG return? The average of 7, not 10. If the values look plausible, it's hard to notice the trick. The number is realistic but distorted.
❌
SELECT AVG(salary) FROM employees


✅
SELECT AVG(COALESCE(salary, 0)) FROM employees

COALESCE: encounters NULL, replaces with 0. You can do without it, but then explain to the interviewer that you understand AVG's behavior and accept it consciously. Both options will be accepted 👌

4️⃣Execution order: the main trick

We write: SELECT → FROM → WHERE → GROUP BY.
Executed: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Illogical? You bet 🤷‍♀️

❌
SELECT category, AVG(sales) AS avg_sales
FROM orders
WHERE avg_sales > 1000
GROUP BY category

The alias avg_sales does not yet exist at the WHERE stage. The system simply doesn't know this field.

✅
SELECT category, AVG(sales) AS avg_sales
FROM orders
GROUP BY category
HAVING AVG(sales) > 1000

HAVING is filtering after grouping. We'll cover it in more detail in a separate post of the series.

5️⃣Forgot syntax? Don't panic

It happens: nerves, haven't touched the topic in a while. Well, it happens to everyone. Say directly: "I understand the logic, I can describe the steps. I'd Google the exact syntax." Any reasonable specialist will appreciate that. Understanding logic is one thing. Googling a function takes 30 seconds.

Bonus at the end: the first question in a test is "What DBMS are you using?"
DATE_TRUNC — PostgreSQL. DATE_FORMAT — MySQL. The correct query for the wrong database = an error out of nowhere.

In the next SQL post: Retention and cohorts. And tomorrow, a post and article about my team's case with the company Grand-Alpha. They deal with animal feed.

Question for you. Have you ever had a situation where you forgot the answer and just couldn't remember no matter what? How did you handle it? 👇

#sql
@data_dzen