Skip to content

DayThree.md

Latest commit

 

History

History
346 lines (216 loc) · 5.24 KB

DayThree.md

File metadata and controls

346 lines (216 loc) · 5.24 KB
Raw

Day 3 is where SQL becomes job-powerful. This is the day interviewers care about most. You move from “retrieving rows” → to analyzing data. 💯

🟢 DAY 3 – Aggregations (Critical for Jobs) (4 Hours)

⏱ Hour 1 – Aggregate Functions (Academic + Practical Depth)

Aggregation means:

Taking many rows and producing a single summarized value.

1️⃣ COUNT()

Counts rows.

SELECT COUNT(*) FROM users;

Counts all rows including NULL values.

Count specific column:

SELECT COUNT(purchases) FROM users;

2️⃣ SUM()

Adds numeric values.

SELECT SUM(purchases) FROM users;

3️⃣ AVG()

Returns mean value.

SELECT AVG(age) FROM users;

4️⃣ MIN() and MAX()

SELECT MIN(age), MAX(age) FROM users;

🔬 Academic Understanding

Aggregate functions:

  • Collapse multiple rows
  • Return one result (unless grouped)
  • Ignore NULL values (except COUNT(*))

Internally:

Database engine:

  1. Scans rows
  2. Applies function accumulator
  3. Returns final result

For large datasets: Indexes may help MIN/MAX But SUM/AVG usually require scanning relevant rows.

⏱ Hour 2 – GROUP BY (Core Concept)

This is where SQL thinking changes.

What GROUP BY Does

It changes result structure.

Instead of: “One result for whole table”

You get: “One result per group”

Example Dataset

Make sure your users table includes purchases and country.

Example 1 – Count users per country

SELECT country, COUNT(*)
FROM users
GROUP BY country;

Output:

country count
USA 3
UK 2

Why GROUP BY Is Strict

Every selected column must either:

  • Be aggregated
  • Or be inside GROUP BY

This fails:

SELECT country, age
FROM users
GROUP BY country;

Why?

Because SQL doesn’t know which age to pick.

HAVING (Very Important)

HAVING filters AFTER grouping.

Example:

SELECT country, COUNT(*)
FROM users
GROUP BY country
HAVING COUNT(*) > 2;

WHERE filters rows. HAVING filters groups.

Execution order:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

⏱ Hour 3 – Business Problem Simulation

Now we think like a company.

1️⃣ Top Customers

SELECT name, purchases
FROM users
ORDER BY purchases DESC
LIMIT 3;

2️⃣ Total Purchases Per Country

SELECT country, SUM(purchases)
FROM users
GROUP BY country;

3️⃣ Average Age Per Country

SELECT country, AVG(age)
FROM users
GROUP BY country;

4️⃣ Countries With High Engagement

SELECT country, SUM(purchases)
FROM users
GROUP BY country
HAVING SUM(purchases) > 10;

5️⃣ Most Recent Signup Year

SELECT MAX(signup_year) FROM users;

6️⃣ Every user who signed up in the most recent year

SELECT *
FROM users
WHERE signup_year = (SELECT MAX(signup_year) FROM users);

⏱ Hour 4 – Deep Academic Understanding

Why GROUP BY Changes Result Shape

Without GROUP BY:

Rows → Single summary

With GROUP BY:

Rows → Buckets → Summary per bucket

This is similar to:

MapReduce pattern in distributed systems.

Internal Execution Model (Simplified)

When you run:

SELECT country, COUNT(*)
FROM users
GROUP BY country;

Database:

  1. Reads rows
  2. Hashes or sorts by country
  3. Creates aggregation buckets
  4. Maintains counters
  5. Outputs grouped result

Two strategies:

  • Hash aggregation
  • Sort aggregation

We revisit this when discussing indexes.

🧪 Practice Questions (Solve Without Looking at Solutions)

1️⃣ Beginner

Count total users.

2️⃣ Beginner+

Find average age of users.

3️⃣ Intermediate

Find total purchases made by users from USA.

4️⃣ Intermediate+

Find number of users per signup_year.

5️⃣ Advanced

Find countries where average age is greater than 28.

6️⃣ Advanced+

Find the signup_year with the highest number of users. (Hint: GROUP BY + ORDER BY + LIMIT)

7️⃣ Interview Classic

Find the second highest age in the table. (You may use ORDER BY + LIMIT with offset in PostgreSQL/MySQL.)

🧠 Conceptual Test Questions (Academic Level)

1️⃣ Why does COUNT(column) ignore NULL values?

2️⃣ What is the difference between WHERE and HAVING?

3️⃣ Why does SQL require grouped columns to appear in GROUP BY?

4️⃣ How does aggregation scale with large datasets?

5️⃣ What is the difference between COUNT(*) and COUNT(column)?

🔥 Strong Practice Links

Focus specifically on:

  • GROUP BY
  • HAVING
  • Aggregate interview questions

🎯 End of Day 3 Goals

You should now:

✔ Understand aggregation deeply ✔ Write GROUP BY confidently ✔ Use HAVING properly ✔ Think in terms of business metrics ✔ Solve common SQL interview problems

Tomorrow (Day 4): We enter JOINS.

That is where SQL becomes real backend engineering. It’s also where most beginners struggle.