Day 02 Part 2 — SQL Advanced: Agenda

The basics get you to the data. Advanced SQL turns it into insight. This session covers the techniques senior analysts use every day: joining multiple tables, aggregating with GROUP BY, writing subqueries, and unlocking window functions for time-series and ranking analysis.

Session Overview

Duration: 3 hours Prerequisite: SQL Basics (SELECT, WHERE, ORDER BY, LIMIT, DISTINCT) Database: Standard SQL (MySQL and PostgreSQL — differences noted)

---

Learning Objectives

By the end of this session you will be able to:

• Join two or more tables using INNER JOIN, LEFT JOIN, and RIGHT JOIN
• Aggregate data with GROUP BY, COUNT, SUM, AVG, MIN, MAX
• Filter aggregated results with HAVING
• Write subqueries in WHERE, FROM, and SELECT
• Use CASE WHEN for conditional logic
• Apply window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) for advanced analytics

---

Session Flow

Time	Topic	File
0:00 – 0:45	JOINs — combining tables	01-joins
0:45 – 1:15	GROUP BY and HAVING — aggregation	02-group-by-and-having
1:15 – 1:45	Subqueries and CASE WHEN	03-subqueries
1:45 – 2:30	Window functions	04-window-functions
2:30 – 3:00	Case studies + business problems	05-advanced-sql-case-studies

---

Key Terms for This Session

Term	Meaning
JOIN	Combine rows from two tables based on a matching column
INNER JOIN	Only rows where the match exists in both tables
LEFT JOIN	All rows from the left table, NULL if no match in right
GROUP BY	Collapse rows into groups, then apply aggregate functions
HAVING	Filter groups after aggregation (WHERE filters rows before)
Subquery	A query nested inside another query
CASE WHEN	If/else logic inside SQL
Window function	Performs a calculation across a set of rows related to the current row, without collapsing them
PARTITION BY	Defines the "window" — the group within which the function applies
ROW_NUMBER()	Assigns a sequential number to each row within a partition
LAG / LEAD	Access the previous or next row's value

---

← SQL Basics Interview Questions · Next: JOINs →