Zeni Study

Pages
- Learn Python In 7 Days
  Day 1 : Variable + if/else
  Day 2: Loops
  Day 3 : Array + String
  Day 4 — Functions
  Day 5 — Dictionaries - Hashmap
  Day 6 — Files & Errors
  Day 7 — Mini Project & Programmer Thinking
- Machine Coding:
  Day 1 — Bank account + Wallet :
  Day 2 : RateLimiter + seat booking
  Day 3 : Library Management + Classroom Attendance System
  Day 4 : Bank account and notification system.
  Day 5 : Key-value and payment processor.
  Day 6 : Discount engine + Seat booking( in depth)
  Day 7: Parking lot System :
- Machine coding extended:
  Day 7.5 — Parking Lot (Constraint-Heavy Variant)
  Day 8 : Order + Payment System
  Day 8 — Extension Round (Mid-Interview Change)
  Day 9 — Ticket Booking with Expiry (Simulated Time)
  Day 9 — Extension Round (Seat Lock Ownership + Forced Unlock)
- SQL + DB Thinking course
  Day 1 — Tables, Rows & Thinking in Data
  Day 2 — Relationships & Foreign Keys (Thinking in Connections)
  Day 3 — Filtering, Aggregation & Answering Data Questions
  Day 4 — JOINs (Combining Tables the Right Way)
  Day 5 — Subqueries, EXISTS & NOT EXISTS (Thinking in Layers)
  Day 6 — Indexes, Constraints & How Databases Think
  Day 7 — Full SQL Interview Simulation (SDE-1 Ready)
  Extended Day 1 — Core Business Systems (Deep Practice)
  Extended Day 2 — Booking & Platform Systems (High-Depth Practice)
  Extended Day 3 — Content & Learning Platforms (Capstone Practice)
- LLD: Java
  DAY 1 — Core Foundations (SDE-2 Level)
  DAY 2 — State, Time & Domain Correctness
  DAY 3 — Extensibility & Failure Handling (SDE-2 Core Signal)
  DAY 4 — Concurrency, Scheduling & Consistency (SDE-2 Critical)
  DAY 5 — Workflow Design & Config-Driven Systems (SDE-2+)
  DAY 6 — Eventing & Usage-Based Systems (Advanced SDE-2)
  DAY 7 — Interview Simulation (Hard, Production-Grade)
  Hidden Edge Cases Interviewers Expect (Day-wise)
  Detailed Testing file:
- Assignment:
  If else Practice Set

Zeni Study

...

Day 3 — Filtering, Aggregation & Answering Data Questions

Explore

Day 3 — Filtering, Aggregation & Answering Data Questions

⁠

Day 3 — Filtering, Aggregation & Answering Data Questions

🎯 Day 3 Outcome

By the end of Day 3, the student should be able to:

Read a business question

Convert it into SQL logic

Use WHERE, GROUP BY, HAVING correctly

Avoid the most common SQL interview mistakes

Explain queries clearly in English

Interviewers don’t care if you remember syntax. They care if you can extract insight from data.

⁠

1️⃣ How Interviewers Think (Important Context)

Interviewers usually ask questions like:

“Who are our top users?”

“Which users placed more than 5 orders?”

“What is our total revenue?”

“Which day had highest sales?”

👉 These are data questions, not SQL questions.

⁠

2️⃣ Dataset We’ll Use Today

Assume this table exists:

orders (

order_id INT,

user_id INT,

amount INT,

order_date DATE

)

⁠

3️⃣ SELECT — Choosing What You Want

Basic Rule

SELECT decides what to show, not what to filter.

SELECT order_id, amount

FROM orders;

Read:

“Show order id and amount for all orders”

⁠

4️⃣ WHERE — Filtering Rows (Before Grouping)

Example

SELECT *

FROM orders

WHERE amount > 500;

Read:

“Only consider orders where amount is more than 500”

📌 WHERE filters individual rows

⁠

5️⃣ Aggregate Functions (Very Important)

Used to summarize data.

Function

Meaning

COUNT(*)

Number of rows

SUM(col)

Total

AVG(col)

Average

MAX(col)

Highest

MIN(col)

Lowest

There are no rows in this table

⁠

Example — Total Revenue

SELECT SUM(amount)

FROM orders;

⁠

Example — Number of Orders

SELECT COUNT(*)

FROM orders;

⁠

6️⃣ GROUP BY — Asking “Per X” Questions

The Most Important Concept Today

Anytime you hear “per user”, “per day”, “per product” 👉 You need GROUP BY.

⁠

Example — Total Spend Per User

SELECT user_id, SUM(amount)

FROM orders

GROUP BY user_id;

Read:

“Group orders by user, then calculate total amount per user”

⁠

🚨 Golden Rule (Never Forget)

Every column in SELECT must be:

In GROUP BY, OR

Inside an aggregate function

❌ Wrong:

SELECT user_id, order_date, SUM(amount)

FROM orders

GROUP BY user_id;

⁠

7️⃣ HAVING — Filtering Groups (AFTER Grouping)

This is where most students get confused.

Key Difference

Clause

Filters

WHERE

Rows

HAVING

Groups

There are no rows in this table

⁠

Example — Users Who Spent More Than 1000

SELECT user_id, SUM(amount)

FROM orders

GROUP BY user_id

HAVING SUM(amount) > 1000;

Read:

“First group by user, then keep only users whose total spend > 1000”

⁠

❌ Common Mistake

WHERE SUM(amount) > 1000 -- ❌ INVALID

Aggregates cannot be used in WHERE.

⁠

8️⃣ Order of Execution (Interview Favorite)

SQL runs logically as:

FROM

→ WHERE

→ GROUP BY

→ HAVING

→ SELECT

→ ORDER BY

Memorize this once. It explains all SQL behavior.

⁠

9️⃣ Interview-Style Examples (Must Understand)

⁠

Example 1 — Average Order Value

SELECT AVG(amount)

FROM orders;

⁠

Example 2 — Orders Per Day

SELECT order_date, COUNT(*)

FROM orders

GROUP BY order_date;

⁠

Example 3 — Days With More Than 5 Orders

SELECT order_date, COUNT(*)

FROM orders

GROUP BY order_date

HAVING COUNT(*) > 5;

⁠

🔟 Practice Problems (DO THESE SERIOUSLY)

Problem 1 — Revenue Questions

Write SQL to find:

Total revenue

Average order value

Highest order amount

⁠

Problem 2 — User Analysis

Write SQL to find:

Total spend per user

Users with more than 3 orders

Users who spent more than 2000

⁠

Problem 3 — Date-Based

Write SQL to find:

Orders per day

Days with total revenue > 5000

⁠

🔍 HINTS (Read Only If Stuck)

“Per X” → GROUP BY X

Filtering before grouping → WHERE

Filtering after grouping → HAVING

COUNT(*) counts rows, not values

⁠

✅ Solutions (Read After Attempting)

Solution — Problem 1

SELECT SUM(amount) FROM orders;

SELECT AVG(amount) FROM orders;

SELECT MAX(amount) FROM orders;

⁠

Solution — Problem 2

SELECT user_id, SUM(amount)

FROM orders

GROUP BY user_id;

SELECT user_id, COUNT(*)

FROM orders

GROUP BY user_id

HAVING COUNT(*) > 3;

SELECT user_id, SUM(amount)

FROM orders

GROUP BY user_id

HAVING SUM(amount) > 2000;

⁠

Solution — Problem 3

SELECT order_date, COUNT(*)

FROM orders

GROUP BY order_date;

SELECT order_date, SUM(amount)

FROM orders

GROUP BY order_date

HAVING SUM(amount) > 5000;

⁠

🧠 Reflection Questions (Must Answer)

Why can’t we use aggregates in WHERE?

What does GROUP BY actually do internally?

When would HAVING be unnecessary?

Why is GROUP BY so common in interviews?

⁠

⭐ Day 3 Golden Rule

SQL is about asking the right question, not writing fancy syntax.

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ··· in the right corner or using a keyboard shortcut (

CtrlP

) instead.