SQL
The Vintage Field Manual
From SELECT to Window Functions — learn SQL by understanding what the database is thinking, not by memorising a pile of syntax.
☞ Before We Touch SQL
It is about transforming one table into another.”
When an interviewer gives you a complicated SQL problem, do not immediately start typing.
Ask one question:
One customer?
One order?
One department?
One day?
That answer determines much of the SQL that follows.
๐บ The SQL Map
What do I want?"] B["FROM
Where is it?"] C["JOIN
Need another table?"] D["WHERE
Filter rows"] E["GROUP BY
Choose the grain"] F["AGGREGATE
COUNT / SUM / AVG"] G["HAVING
Filter groups"] H["CASE WHEN
Business rules"] I["NULL / COALESCE
Missing data"] J["CTE / SUBQUERY
Break into steps"] K["WINDOW FUNCTION
Analyse without collapsing"] L["LAG / LEAD
Previous / Next"] M["DATE FUNCTIONS"] N["STRING FUNCTIONS"] O["UNION / UNION ALL"] P["ORDER BY
Present the result"] A --> B --> C --> D --> E --> F --> G G --> H --> I --> J --> K --> L L --> M --> N --> O --> P
๐งญ The Sequence
๐ The Example Database
Throughout this guide, imagine this tiny e-commerce database.
| customers | orders |
|---|---|
|
customer_id name city |
order_id customer_id order_date amount status |
SELECT
Question → What information do I want?
SELECT name, city
FROM customers;
Think of it as: “Which fields belong in my answer?”
FROM
Question → Where is my starting data?
SELECT *
FROM customers;
DISTINCT
Question → Do duplicate values matter?
SELECT DISTINCT city
FROM customers;
SELECT COUNT(DISTINCT hacker_id)
FROM Submissions;
means:
How many unique hackers?
WHERE
Question → Which individual rows should survive?
SELECT *
FROM orders
WHERE status = 'completed';
AND / OR / NOT
SELECT *
FROM orders
WHERE status = 'completed'
AND amount > 500;
IN / BETWEEN / LIKE
SELECT *
FROM customers
WHERE city IN ('Kolkata','Delhi');
SELECT *
FROM orders
WHERE amount BETWEEN 500 AND 1000;
SELECT *
FROM customers
WHERE name LIKE 'A%';
IN → “Is it in this set?”
BETWEEN → “Is it inside this range?”
LIKE → “Does it match this pattern?”
JOIN
Question → Is the information I need in another table?
SELECT
c.name,
o.amount
FROM customers AS c
JOIN orders AS o
ON c.customer_id = o.customer_id;
LEFT JOIN
SELECT
c.name,
o.amount
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;
GROUP BY
Question → One output row per WHAT?
SELECT
customer_id,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;
Aggregation
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total,
AVG(amount) AS average,
MIN(amount) AS minimum,
MAX(amount) AS maximum
FROM orders
GROUP BY customer_id;
GROUP BY → create buckets
SUM/COUNT/AVG → measure buckets
HAVING
SELECT
customer_id,
SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;
WHERE → filter rows
HAVING → filter groups
ORDER BY
SELECT *
FROM orders
ORDER BY amount DESC;
CASE WHEN
SELECT
amount,
CASE
WHEN amount >= 1000 THEN 'HIGH'
WHEN amount >= 500 THEN 'MEDIUM'
ELSE 'LOW'
END AS order_category
FROM orders;
NULL
SELECT *
FROM customers
WHERE city IS NULL;
Don't write:
WHERE city = NULL;
COALESCE
SELECT
name,
COALESCE(city, 'Unknown') AS city
FROM customers;
SUBQUERY
SELECT *
FROM orders
WHERE amount >
(
SELECT AVG(amount)
FROM orders
);
CTE
WITH customer_total AS
(
SELECT
customer_id,
SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT *
FROM customer_total
WHERE total > 1000;
UNION
SELECT name
FROM customers_kolkata
UNION
SELECT name
FROM customers_delhi;
Use:
UNION ALL
when duplicates should remain.
JOIN
→ columns side-by-side
UNION
→ rows one-after-another
WINDOW FUNCTIONS ⭐
Question → Can I analyse rows without collapsing them?
SELECT
customer_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders;
3 Alice rows
↓
1 Alice row
Window function:
3 Alice rows
↓
3 Alice rows
+
customer total
GROUP BY collapses.
WINDOW FUNCTIONS preserve.
PARTITION BY
SUM(amount) OVER (
PARTITION BY customer_id
)
Alice's rows → Window A
Bob's rows → Window B
Charlie → Window C
It resembles GROUP BY logically, but rows remain visible.
ROW_NUMBER
SELECT
customer_id,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY amount DESC
) AS rn
FROM orders;
- latest record
- top record
- first transaction
- deduplication
- top-N per group
RANK / DENSE_RANK
SELECT
name,
salary,
RANK() OVER (
ORDER BY salary DESC
) AS salary_rank
FROM employees;
For:
100
100
90
RANK:
1
1
3
DENSE_RANK:
1
1
2
ROW_NUMBER:
1
2
3
LAG
SELECT
order_date,
amount,
LAG(amount) OVER (
ORDER BY order_date
) AS previous_amount
FROM orders;
- previous transaction
- month-over-month comparison
- salary changes
- price changes
LEAD
SELECT
order_date,
amount,
LEAD(amount) OVER (
ORDER BY order_date
) AS next_amount
FROM orders;
LAG ← past | current | future → LEAD
ORDER BY inside OVER()
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
)
ORDER BY outside OVER()
→ final display order
ORDER BY inside OVER()
→ analytical sequence
DATE FUNCTIONS — SQL SERVER
SELECT
order_date,
YEAR(order_date) AS order_year,
MONTH(order_date) AS order_month,
DAY(order_date) AS order_day
FROM orders;
Calculate difference:
SELECT
DATEDIFF(day, '2026-01-01', order_date)
FROM orders;
Add time:
SELECT
DATEADD(day, 7, order_date)
FROM orders;
STRING FUNCTIONS — SQL SERVER
SELECT
UPPER(name) AS upper_name,
LOWER(name) AS lower_name,
LEN(name) AS name_length
FROM customers;
Combine:
SELECT
CONCAT(name, ' - ', city) AS customer_info
FROM customers;
⚙️ What SQL Server Logically Thinks
One of the most useful interview mental models is the logical processing order.
FROM
↓
JOIN
↓
WHERE
↓
GROUP BY
↓
HAVING
↓
SELECT
↓
DISTINCT
↓
ORDER BY
↓
TOP / OFFSET-FETCH
The written order of a SELECT statement is different from the logical processing order. This explains many SQL interview questions.
๐ง The Interview Pocket Card
| When the interviewer says... | Think... |
|---|---|
| Show me... | SELECT |
| From this table... | FROM |
| Only these records... | WHERE |
| Information from another table... | JOIN |
| One row per customer... | GROUP BY customer_id |
| Total / average / count... | SUM / AVG / COUNT |
| Groups above... | HAVING |
| Classify into categories... | CASE |
| Unique... | DISTINCT |
| Missing... | NULL / COALESCE |
| Previous record... | LAG |
| Next record... | LEAD |
| Top 1 per group... | ROW_NUMBER() |
| Rank with ties... | RANK / DENSE_RANK |
| Analyse but keep every row... | WINDOW FUNCTION |
| Break complicated query into steps... | CTE |
| Combine result sets... | UNION / UNION ALL |
| Previous/next date... | LAG / LEAD + ORDER BY |
๐ The Golden Rules
Rule #1
Before writing SQL:
Rule #2
Ask whether your problem wants:
COLLAPSE
↓
GROUP BY
or
KEEP ROWS
↓
WINDOW
Rule #3
Remember:
WHERE
→ rows
HAVING
→ groups
Rule #4
Remember:
JOIN
→ horizontal
UNION
→ vertical
๐งฉ The Universal SQL Algorithm
1. What should ONE output row represent?
2. What is my starting table?
3. Do I need another table?
4. Which raw rows should survive?
5. Do I need groups?
6. What should I calculate per group?
7. Do I need to filter those groups?
8. Do I need business rules?
9. Can values be NULL?
10. Is the problem becoming multi-step?
11. Do I need to preserve rows while analysing?
12. Do I need previous/next rows?
13. Do I need ranking?
14. Do I need date transformation?
15. Do I need string transformation?
16. Do I need to combine result sets?
17. How should the final result be sorted?
☞ One Last Mental Picture
BUSINESS QUESTION
│
▼
"What is ONE row?"
│
▼
FROM
│
▼
JOIN
│
▼
WHERE
│
▼
┌────────┴────────┐
│ │
COLLAPSE KEEP ROWS
│ │
▼ ▼
GROUP BY WINDOW
│ │
▼ ▼
AGGREGATION RANK / LAG
│ │
└────────┬────────┘
▼
CASE
│
▼
NULL HANDLING
│
▼
CTE / SUBQUERY
│
▼
FINAL TABLE
│
▼
ORDER BY
and start seeing table transformations.
No comments:
Post a Comment