Why Data Analyst Candidates Freeze on the Simplest Questions

Interviewer: What is the difference between WHERE and HAVING?

Candidate: WHERE is used to... filter... and HAVING is
also used to filter but... with... aggregate... I think
HAVING is used with GROUP BY... and WHERE is... not?

(The candidate knows this, but is trying to recall a
sentence instead of picturing what the query does.)

Interviewer: What is the difference between WHERE and HAVING?

Candidate: Picture an orders table with city and amount.

WHERE filters rows before grouping, so
  WHERE amount > 500
keeps only big orders.

HAVING filters groups after aggregation, so
  GROUP BY city HAVING SUM(amount) > 100000
keeps only cities with large total sales.

So WHERE works on individual rows, HAVING on group
results. I can use both in one query.

CREATE TABLE customers (id INT, name VARCHAR(20), email VARCHAR(40));
INSERT INTO customers VALUES
  (1, 'Asha', 'asha@example.com'),
  (2, 'Ravi', NULL),
  (3, 'Meena', 'meena@example.com');

SELECT COUNT(*)     AS all_rows,      -- 3
       COUNT(email) AS with_email     -- 2
FROM customers;

SELECT name FROM customers WHERE email = NULL;   -- no rows
SELECT name FROM customers WHERE email IS NULL;  -- Ravi

-- Goal: all customers with their order count
SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;
-- Customers with no orders show 0 (COUNT skips NULL ids)

-- Trap: filtering the right table in WHERE
SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'delivered'
GROUP BY c.name;
-- Customers with no orders disappear, like an INNER JOIN.
-- Fix: move the condition into the ON clause:
--   LEFT JOIN orders o ON o.customer_id = c.id
--                     AND o.status = 'delivered'

Prices in A2:A10, tax rate in B1

C2:  =A2*(1+$B$1)
  Drag down -> C3 becomes =A3*(1+$B$1)
  The price moves, the tax rate stays fixed.

Product codes in E2:E100, prices in G2:G100
Look up code in J2:

  =VLOOKUP(J2, E2:G100, 3, FALSE)
  =XLOOKUP(J2, E2:E100, G2:G100, "Not found")

Orders sheet: city in B, month in C
  =COUNTIFS(B:B, "Noida", C:C, "Oct")

Interviewer: What is the difference between VLOOKUP and XLOOKUP?

Candidate: Say I have product codes and prices and I
want the price for one code.

VLOOKUP needs the code column to be the first column
of the range, and I give it a column number. If
someone inserts a column, the number can break.

XLOOKUP takes the lookup column and the return column
separately, so it can look left, it does exact match
by default, and I can set a 'not found' message.

If the company uses an older Excel version, I would
use INDEX with MATCH instead.

DAY 1  WHERE vs HAVING, ORDER of SQL clauses
DAY 2  COUNT variants, NULL behaviour, COALESCE
DAY 3  INNER, LEFT, FULL joins; the LEFT JOIN trap
DAY 4  GROUP BY, DISTINCT, UNION vs UNION ALL
DAY 5  Excel references, XLOOKUP, COUNTIFS, SUMIFS
DAY 6  Pivot tables, basic charts, when to use each
DAY 7  Mixed quiz with a friend, answered aloud

Each day: build a 3-row example, write the query or
formula, say the rule in one sentence.

MY EXAMPLE TABLES (memorise these)

customers(id, name, email, city)
  1 Asha  asha@..   Noida
  2 Ravi  NULL      Delhi
  3 Meena meena@..  NULL

orders(order_id, customer_id, amount, status)
  101  1  500   delivered
  102  1  1200  cancelled
  103  3  800   delivered

Ravi has no orders. Meena has no city.
Those two facts answer most NULL and JOIN questions.

IF I FREEZE ON A BASIC QUESTION

1. Breathe once.
2. Say: "Let me picture a small table for this."
3. Describe the table in one sentence.
4. Walk through what the query or formula does.
5. State the rule.
6. Add one edge case if I know it.

Never say "I know this, I just can't remember."
Show it with an example instead.