Advanced SQL

cters, ending in "mit" | Amit |

Combining Conditions with AND, OR, NOT

-- Corporate customers from Delhi
SELECT * FROM customers 
WHERE segment = 'Corporate' AND city = 'Delhi';

-- Customers from Delhi OR Mumbai
SELECT * FROM customers 
WHERE city = 'Delhi' OR city = 'Mumbai';

-- Customers NOT from Delhi
SELECT * FROM customers 
WHERE NOT city = 'Delhi';
-- same as: WHERE city != 'Delhi'

2.3 ORDER BY — Sorting Results

-- Sort by amount (lowest to highest — ascending is default)
SELECT * FROM orders ORDER BY amount;

-- Sort by amount, highest first
SELECT * FROM orders ORDER BY amount DESC;

-- Sort by multiple columns
SELECT * FROM orders ORDER BY product_category ASC, amount DESC;

2.4 LIMIT — Restricting Results

-- Top 3 highest orders
SELECT * FROM orders 
ORDER BY amount DESC 
LIMIT 3;

-- Skip first 2, then get next 3 (pagination)
SELECT * FROM orders 
ORDER BY amount DESC 
LIMIT 3 OFFSET 2;

2.5 DISTINCT — Unique Values Only

-- What cities do our customers come from?
SELECT DISTINCT city FROM customers;

-- How many unique cities?
SELECT COUNT(DISTINCT city) FROM customers;

CHAPTER 3: AGGREGATION — GROUP BY & HAVING

🧠 Hinglish mein samjho: Aggregate = bahut saari rows ko mila ke EK jawab dena. Jaise class mein "sabka average marks kitna hai?" — woh aggregate hai. GROUP BY = "Har section ka alag alag average batao" — woh grouping hai.

3.1 Aggregate Functions

Aggregate functions collapse multiple rows into a single value.

Function	Kya Karta Hai	Example
`COUNT(*)`	Kitni rows hain total	Kitne orders aaye?
`COUNT(column)`	Kitni rows mein ye column NULL nahi hai	Kitne logon ka phone hai?
`SUM(column)`	Sab jodo	Total revenue kitna hua?
`AVG(column)`	Average nikalo	Ek order ka average kitna hai?
`MIN(column)`	Sabse chhota	Sabse sasta order?
`MAX(column)`	Sabse bada	Sabse mehnga order?

-- Saare orders ka summary ek baar mein (bahut kaam aata hai reporting mein)
SELECT 
    COUNT(*)        AS total_orders,       -- 7 (kitne orders)
    SUM(amount)     AS total_revenue,      -- 36000 (total paisa)
    AVG(amount)     AS avg_order_value,    -- 5142.86 (average order)
    MIN(amount)     AS smallest_order,     -- 1500 (sabse chhota)
    MAX(amount)     AS largest_order       -- 12000 (sabse bada)
FROM orders;

3.2 GROUP BY — Aggregate Per Group

GROUP BY splits data into groups and applies aggregate functions to each group separately.

🧠 Socho aise: GROUP BY = "Mujhe har category ka ALAG answer chahiye." Bina GROUP BY ke = poore table ka ek hi answer milega.

-- Revenue by product category
SELECT 
    product_category,
    COUNT(*)    AS order_count,
    SUM(amount) AS total_revenue,
    AVG(amount) AS avg_order_value
FROM orders
GROUP BY product_category
ORDER BY total_revenue DESC;

Result:

product_category	order_count	total_revenue	avg_order_value
Technology	3	25000	8333.33
Furniture	2	7500	3750
Supplies	2	3500	1750

3.3 HAVING — Filtering After Aggregation

🧠 Ek line mein fark: WHERE = individual rows chhaanata hai (grouping se PEHLE). HAVING = groups ko chhaanata hai (grouping ke BAAD). WHERE individual student ko filter karta hai, HAVING poori class ko filter karta hai.

-- Sirf wo categories dikhao jinki total revenue > 5000
SELECT 
    product_category,
    SUM(amount) AS total_revenue
FROM orders
GROUP BY product_category
HAVING SUM(amount) > 5000;  -- HAVING isliye kyunki SUM ek aggregate hai

WHERE vs HAVING — The Key Difference

-- ❌ GALAT: WHERE mein aggregate function nahi chal sakta
SELECT product_category, SUM(amount) 
FROM orders 
WHERE SUM(amount) > 5000  -- ERROR! (Kyunki WHERE pehle chalta hai, tab tak SUM hua hi nahi)
GROUP BY product_category;

-- ✅ SAHI: HAVING use karo aggregate condition ke liye
SELECT product_category, SUM(amount) AS total
FROM orders 
GROUP BY product_category
HAVING SUM(amount) > 5000;  -- HAVING tab chalta hai jab SUM ho chuka hota hai

3.4 SQL Query Execution Order

🧠 BAHUT IMPORTANT — Interview mein zaroor poochte hain! Tum SQL likhte ho ek order mein, but database EXECUTE karta hai alag order mein. Isko yaad rakhna padega.

Step	Clause	Kya Hota Hai	Yaad Kaise Rakho
1	`FROM`	Table dhundho	"Pehle table toh lao"
2	`WHERE`	Individual rows chhaano	"Kaunse rows chahiye?"
3	`GROUP BY`	Groups banao	"Ab groups mein baanto"
4	`HAVING`	Groups chhaano	"Kaunse groups chahiye?"
5	`SELECT`	Columns chuno + calculate karo	"Ab dikhao kya chahiye"
6	`DISTINCT`	Duplicates hatao	"Aur unique rakhna"
7	`ORDER BY`	Sort karo	"Aur arrange karo"
8	`LIMIT/OFFSET`	Output chhota karo	"Aur itne hi do"

💡 Yaad rakhne ka trick: F-W-G-H-S-D-O-L = "Freshers Will Get Hired Soon, Don't Observe Long" 😄

Isi liye WHERE mein alias nahi use kar sakte — SELECT toh WHERE ke BAAD chalta hai!

CHAPTER 4: JOINs — Combining Tables

🧠 Ek line mein: JOIN = do tables ko jodna. Real life mein data ek table mein nahi hota — customer info alag, orders alag, products alag. JOIN se sab mila ke ek complete picture milti hai.

Shaadi ki analogy: INNER JOIN = sirf wo log aaye jinhe DONO families ne invite kiya. LEFT JOIN = ladke waale sab aaye + ladki waale se jitne match hue. 😄

4.1 Why JOINs?

Real data is stored across multiple tables (to avoid duplication — this is called normalization). JOINs let you combine data from multiple tables using a shared column (usually a key).

4.2 Types of JOINs — Visual Guide

INNER JOIN (Most Common)

Returns only rows that have a match in BOTH tables.

🧠 Socho: Sirf wahi rows aayengi jo DONO tables mein milti hain. Agar customer ne order nahi kiya, toh uska naam result mein nahi aayega.

Table A       Table B         INNER JOIN Result
┌───┬───┐    ┌───┬───┐       ┌───┬───┬───┐
│ 1 │ a │    │ 1 │ x │       │ 1 │ a │ x │  ← match mila
│ 2 │ b │    │ 2 │ y │       │ 2 │ b │ y │  ← match mila
│ 3 │ c │    │ 4 │ z │       └───┴───┴───┘
└───┴───┘    └───┴───┘       (3 gayab — B mein nahi hai)
                              (4 gayab — A mein nahi hai)

-- Customer ka naam uske orders ke saath dikhao (sirf jinke orders hain)
SELECT c.name, o.order_id, o.amount  -- c aur o = table ke chhote naam (alias)
FROM customers c                      -- LEFT table = customers, alias 'c'
INNER JOIN orders o                   -- RIGHT table = orders, alias 'o'
ON c.customer_id = o.customer_id;     -- Ye batata hai KIS column se match karna hai

LEFT JOIN (LEFT OUTER JOIN)

Returns ALL rows from the left table + matching rows from the right. Non-matches get NULL.

🧠 Socho: LEFT table ke SAARE rows aayenge — chahe match ho ya na ho. Match nahi mila toh right side mein NULL aa jayega. Interview mein sabse zyada poochha jaata hai!

Table A       Table B         LEFT JOIN Result
┌───┬───┐    ┌───┬───┐       ┌───┬───┬──────┐
│ 1 │ a │    │ 1 │ x │       │ 1 │ a │  x   │  ← match mila
│ 2 │ b │    │ 2 │ y │       │ 2 │ b │  y   │  ← match mila
│ 3 │ c │    │ 4 │ z │       │ 3 │ c │ NULL │  ← match nahi mila, NULL aaya
└───┴───┘    └───┴───┘       └───┴───┴──────┘

-- SAARE customers dikhao, chahe unhone order kiya ho ya nahi
-- (Jinke order nahi hain unke columns mein NULL aayega)
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

💡 DecisionTree mein: "Show me all customers and their orders (including those with no orders)" — yeh churn analysis ka pehla step hai! LEFT JOIN + WHERE o.order_id IS NULL = customers who never ordered.

RIGHT JOIN

Returns ALL rows from the right table + matching from left. (Less common — you can usually rewrite as LEFT JOIN by swapping tables.)

FULL OUTER JOIN

Returns ALL rows from BOTH tables. Non-matches get NULL on the missing side.

FULL OUTER JOIN Result
┌───┬──────┬──────┐
│ 1 │  a   │  x   │
│ 2 │  b   │  y   │
│ 3 │  c   │ NULL │ ← No match in B
│ 4 │ NULL │  z   │ ← No match in A
└───┴──────┴──────┘

CROSS JOIN

Returns the Cartesian product — every row from A paired with every row from B. If A has 3 rows and B has 4, result has 12 rows.

-- All combinations of products and regions (useful for matrix reports)
SELECT p.product_name, r.region_name
FROM products p
CROSS JOIN regions r;

SELF JOIN

A table joined with itself. Useful for hierarchical data.

-- Find employees and their managers (both in 'employees' table)
SELECT 
    e.name AS employee, 
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Summary of All JOINs

CHAPTER 5: SUBQUERIES & CTEs

🧠 Simple bhasha mein: Subquery = query ke andar query. Jaise ek answer dhundne ke liye pehle ek chhota answer chahiye. CTE = wahi cheez, but zyada readable — naam deke upar likh do, phir neeche use karo.

5.1 Subqueries (Nested Queries)

A subquery is a query inside another query. Think of it as solving a problem in steps.

Subquery in WHERE

-- Customers who placed orders above the average order value
SELECT * FROM customers
WHERE customer_id IN (
    SELECT customer_id 
    FROM orders 
    WHERE amount > (SELECT AVG(amount) FROM orders)
);

How to read this:

Innermost query: SELECT AVG(amount) FROM orders → gets average (≈5143)
Middle query: finds customer_ids with orders above 5143
Outer query: gets full customer details for those IDs

Subquery in SELECT (Scalar Subquery)

-- Each order with the overall average for comparison
SELECT 
    order_id,
    amount,
    (SELECT AVG(amount) FROM orders) AS overall_avg,
    amount - (SELECT AVG(amount) FROM orders) AS diff_from_avg
FROM orders;

Subquery in FROM (Derived Table)

-- Find the customer with the highest total spending
SELECT name, total_spent
FROM (
    SELECT 
        c.name,
        SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.name
) AS customer_spending
ORDER BY total_spent DESC
LIMIT 1;

5.2 CTEs (Common Table Expressions)

A CTE is like a subquery, but named and defined at the top using WITH. It makes complex queries readable.

-- Same query as above, but using a CTE (much cleaner!)
WITH customer_spending AS (
    SELECT 
        c.name,
        SUM(o.amount) AS total_spent
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.name
)
SELECT name, total_spent
FROM customer_spending
ORDER BY total_spent DESC
LIMIT 1;

Multiple CTEs

-- Find categories where revenue is above the overall average category revenue
WITH category_revenue AS (
    SELECT 
        product_category,
        SUM(amount) AS total_revenue
    FROM orders
    GROUP BY product_category
),
avg_category_revenue AS (
    SELECT AVG(total_revenue) AS avg_rev
    FROM category_revenue
)
SELECT cr.*
FROM category_revenue cr, avg_category_revenue acr
WHERE cr.total_revenue > acr.avg_rev;

When to Use CTE vs Subquery

Use CTE When...	Use Subquery When...
Query is complex with many steps	Simple, one-level nesting
You need to reference the same result multiple times	Result is used only once
You want readable, self-documenting code	Quick inline filter
Recursive queries (hierarchical data)	N/A

CHAPTER 6: WINDOW FUNCTIONS (Advanced — Frequently Asked!)

🧠 Sabse important concept for interviews! Interviewers isse poochke dekhte hain ki tumhara SQL advanced level ka hai ya nahi. Zaroor padho.

6.1 What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row — WITHOUT collapsing the result set (unlike GROUP BY).

🧠 GROUP BY vs Window Function ka fark aise samjho:

GROUP BY = Class photo — 3 sections hain toh 3 photos (rows collapse ho gayi)

Window Function = Har student ka individual photo, but photo pe likha hai ki woh kaunse section mein hai aur section mein rank kya hai (rows wahi rehti hain, extra info add hoti hai)

Syntax pattern:

FUNCTION_NAME() OVER (
    PARTITION BY column    -- Kis group ke andar calculate karna hai (optional)
    ORDER BY column        -- Kis order mein calculate karna hai (optional)
)
-- PARTITION BY = GROUP BY jaisa groups banata hai, but rows collapse nahi hoti
-- ORDER BY = ranking ya running total ke liye zaruri

6.2 Ranking Functions

ROW_NUMBER, RANK, DENSE_RANK

🧠 Teeno mein fark ghanta bhar padh ke bhi nahi samajh aayega — but TIES mein dekhoge toh turant samajh aa jayega (neeche table dekho).

SELECT 
    customer_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,      -- Seedha 1,2,3,4... — koi repeat nahi
    RANK()       OVER (ORDER BY amount DESC) AS rank_val,      -- Tie pe same rank, but SKIP karta hai
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_val -- Tie pe same rank, SKIP nahi karta
FROM orders;

Jab sab amounts alag hain — teeno same dikhte hain:

amount	ROW_NUMBER	RANK	DENSE_RANK
12000	1	1	1
8000	2	2	2
5000	3	3	3
4500	4	4	4

But jab TIE hoti hai (do orders ₹5000 ke) — TAB fark dikhta hai:

amount	ROW_NUMBER	RANK	DENSE_RANK
12000	1	1	1
5000	2	2	2
5000	3 (alag number)	2 (same rank)	2 (same rank)
4500	4	4 (3 SKIP hua!)	3 (skip NAHI hua)

🧠 Yaad kaise rakho:

ROW_NUMBER = Roll number — har kisi ko unique milta hai, chahe marks same ho

RANK = Sports ranking — tie pe same rank, next SKIP hota hai (Gold, Gold, Bronze — Silver nahi)

DENSE_RANK = Exam ranking — tie pe same rank, next SKIP NAHI hota (1st, 1st, 2nd)

💡 Interview trick: "Nth highest salary" pooche toh DENSE_RANK use karo. "Unique row number" chahiye toh ROW_NUMBER.

PARTITION BY — Ranking Within Groups

-- Rank orders within each product category
SELECT 
    product_category,
    order_id,
    amount,
    RANK() OVER (
        PARTITION BY product_category 
        ORDER BY amount DESC
    ) AS rank_within_category
FROM orders;

This ranks orders separately for each category — like doing GROUP BY but keeping all rows.

6.3 LEAD and LAG

🧠 LAG = peeche dekho (previous row), LEAD = aage dekho (next row). Month-over-month growth calculate karna ho toh LAG MUST hai. DecisionTree mein roz use hota hai.

-- Har order ko uske pichle order se compare karo
SELECT 
    order_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY order_date)  AS prev_order_amount,   -- 1 row peeche dekho
    amount - LAG(amount, 1) OVER (ORDER BY order_date) AS change_from_prev  -- fark nikalo
FROM orders;
-- LAG(amount, 1) = "mujhse 1 row pehle waale ka amount batao"
-- LAG(amount, 2) = "mujhse 2 row pehle waala" — aur door dekhna ho toh number badhao

Result:

order_date	amount	prev_order_amount	change_from_prev
2023-06-01	5000	NULL (pehle koi nahi)	NULL
2023-06-15	3000	5000	-2000 (gira)
2023-07-01	8000	3000	+5000 (badha)
2023-07-20	2000	8000	-6000 (bahut gira)

💡 DecisionTree use case: Monthly revenue growth calculate karna, customer ka order frequency gir raha hai ya badh raha — churn predict karne ka pehla signal!

6.4 Running Totals and Moving Averages

-- Running total of revenue over time
SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

ROWS BETWEEN frame specification:

Frame	Meaning
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	All rows from start to current (default for running total)
`ROWS BETWEEN 2 PRECEDING AND CURRENT ROW`	Current row + 2 previous (3-period moving average)
`ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Current row ± 1 neighbor

CHAPTER 7: CASE WHEN — Conditional Logic

CASE WHEN is SQL's if-else. It creates new calculated columns based on conditions.

-- Categorize orders by size
SELECT 
    order_id,
    amount,
    CASE 
        WHEN amount >= 10000 THEN 'High Value'
        WHEN amount >= 5000  THEN 'Medium Value'
        WHEN amount >= 2000  THEN 'Low Value'
        ELSE 'Very Low'
    END AS order_tier
FROM orders;

Use CASE with Aggregation

-- Count orders by tier (pivot-style)
SELECT 
    product_category,
    COUNT(CASE WHEN amount >= 5000 THEN 1 END) AS high_value_orders,
    COUNT(CASE WHEN amount < 5000 THEN 1 END)  AS low_value_orders
FROM orders
GROUP BY product_category;

CHAPTER 8: NULL HANDLING

8.1 What Is NULL?

NULL means no value / unknown / missing. It's NOT zero, NOT an empty string.

[!CAUTION] NULL = NULL is FALSE in SQL! You must use IS NULL / IS NOT NULL.

-- ❌ WRONG — This will return nothing
SELECT * FROM customers WHERE phone = NULL;

-- ✅ CORRECT
SELECT * FROM customers WHERE phone IS NULL;

8.2 Functions to Handle NULLs

-- COALESCE: Returns the FIRST non-null value
SELECT COALESCE(phone, email, 'No Contact') AS contact FROM customers;

-- NULLIF: Returns NULL if two values are equal (prevents division by zero)
SELECT revenue / NULLIF(cost, 0) AS margin FROM sales;
-- If cost = 0, NULLIF returns NULL, so division returns NULL instead of error

-- COUNT behavior with NULLs
SELECT 
    COUNT(*)     AS total_rows,       -- Counts ALL rows (including NULLs)
    COUNT(phone) AS rows_with_phone   -- Counts only non-NULL phone values
FROM customers;

CHAPTER 9: STRING & DATE FUNCTIONS

9.1 String Functions

Function	What It Does	Example	Result
`UPPER(s)`	Uppercase	`UPPER('hello')`	HELLO
`LOWER(s)`	Lowercase	`LOWER('HELLO')`	hello
`LENGTH(s)`	String length	`LENGTH('SQL')`	3
`SUBSTRING(s, start, len)`	Extract part	`SUBSTRING('Hello', 1, 3)`	Hel
`CONCAT(s1, s2)`	Join strings	`CONCAT('Hi', ' ', 'There')`	Hi There
`TRIM(s)`	Remove whitespace	`TRIM(' hi ')`	hi
`REPLACE(s, old, new)`	Replace text	`REPLACE('Hello', 'l', 'r')`	Herro
`LEFT(s, n)`	First N chars	`LEFT('Hello', 3)`	Hel
`RIGHT(s, n)`	Last N chars	`RIGHT('Hello', 3)`	llo

9.2 Date Functions

Function (PostgreSQL)	What It Does	Example
`CURRENT_DATE`	Today's date	2023-09-17
`CURRENT_TIMESTAMP`	Current date+time	2023-09-17 14:30:00
`EXTRACT(part FROM date)`	Get year/month/day	`EXTRACT(YEAR FROM '2023-06-15')` → 2023
`DATE_TRUNC('month', date)`	Truncate to period	`DATE_TRUNC('month', '2023-06-15')` → 2023-06-01
`AGE(date1, date2)`	Difference	`AGE('2023-12-01', '2023-06-01')` → 6 months
`date + INTERVAL '30 days'`	Add time	Adds 30 days to a date

-- Revenue by month
SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- Orders in the last 30 days
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

CHAPTER 10: UNION & SET OPERATIONS

-- UNION: Combine results, remove duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;

-- UNION ALL: Combine results, keep duplicates (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- INTERSECT: Only rows in BOTH queries
SELECT city FROM customers
INTERSECT
SELECT city FROM suppliers;

-- EXCEPT: Rows in first query but NOT in second
SELECT city FROM customers
EXCEPT
SELECT city FROM suppliers;

Rule: Both queries must have same number of columns with compatible data types.

CHAPTER 11: INTERVIEW QUESTIONS WITH SOLUTIONS

🧠 Yeh section sabse important hai! Inme se 4-5 questions toh zaroor poochhe jaayenge. Har question ke 2-3 methods hain — interview mein sabse pehle SIMPLE method batao, phir bolo "iska ek aur advanced approach hai..." — impression bahut achha padta hai.

Q1. Find the second highest salary.

🧠 Socho: "Second highest" = sabse bada nikalke baaki mein se sabse bada. Ya phir ranking lagao aur rank=2 nikalo.

-- Method 1: Subquery (EASY — pehle yeh batao)
-- Socho: Sabse badi salary nikalo, phir usse CHHOTI waalon mein se MAX nikalo
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: DENSE_RANK (BEST — "Nth highest" ke liye)
-- Socho: Sab pe rank lagao, phir rank=2 chun lo
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;
-- RANK nahi DENSE_RANK kyunki? Kyunki RANK skip karta hai ties pe, DENSE_RANK nahi.

-- Method 3: OFFSET (SIMPLEST — agar interviewer allow kare)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- 1 skip karo, phir 1 lo = second highest

Q2. Find duplicate emails in a table.

SELECT email, COUNT(*) AS occurrence
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Q3. Customers who never placed an order.

-- Using LEFT JOIN + NULL check (most intuitive)
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Using NOT IN
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM orders);

-- Using NOT EXISTS (most efficient)
SELECT c.name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Q4. Top 3 customers by total spending.

SELECT 
    c.name,
    SUM(o.amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 3;

Q5. Month-over-month revenue growth.

WITH monthly_revenue AS (
    SELECT 
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT 
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 / 
        NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 
        2
    ) AS growth_pct
FROM monthly_revenue
ORDER BY month;

Q6. Find the most popular product category per city.

WITH city_category AS (
    SELECT 
        c.city,
        o.product_category,
        COUNT(*) AS order_count,
        RANK() OVER (PARTITION BY c.city ORDER BY COUNT(*) DESC) AS rnk
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.city, o.product_category
)
SELECT city, product_category, order_count
FROM city_category
WHERE rnk = 1;

Combining Conditions with AND, OR, NOT​

2.3 ORDER BY — Sorting Results​

2.4 LIMIT — Restricting Results​

2.5 DISTINCT — Unique Values Only​

CHAPTER 3: AGGREGATION — GROUP BY & HAVING

3.1 Aggregate Functions​

3.2 GROUP BY — Aggregate Per Group​

3.3 HAVING — Filtering After Aggregation​

WHERE vs HAVING — The Key Difference​

3.4 SQL Query Execution Order​

CHAPTER 4: JOINs — Combining Tables

4.1 Why JOINs?​

4.2 Types of JOINs — Visual Guide​

INNER JOIN (Most Common)​

LEFT JOIN (LEFT OUTER JOIN)​

RIGHT JOIN​

FULL OUTER JOIN​

CROSS JOIN​

SELF JOIN​

Summary of All JOINs​

CHAPTER 5: SUBQUERIES & CTEs

5.1 Subqueries (Nested Queries)​

Subquery in WHERE​

Subquery in SELECT (Scalar Subquery)​

Subquery in FROM (Derived Table)​

5.2 CTEs (Common Table Expressions)​

Multiple CTEs​

When to Use CTE vs Subquery​