Practice and reinforce the concepts from Lesson 6
It's 7 AM on Monday morning. You're the newest data analyst at DataCorp, and your phone is buzzing with urgent messages. The quarterly board meeting is in 48 hours, and the CEO needs complex analytics that simple queries can't answer:
The senior analysts are stuck. They've been writing messy, unreadable queries with multiple subqueries that even they can't debug. The CFO just walked by your desk and said: "We need these numbers. $50 million in budget allocation depends on this analysis."
Your Mission: Master subqueries and Common Table Expressions (CTEs) to deliver critical business intelligence. You'll learn to write queries that are not only powerful but also readable and maintainable-the kind of SQL that impresses both databases and senior leadership.
What You'll Learn:
Time Estimate: 60-75 minutes
First, let's set up DataCorp's employee and sales databases. Run these commands in your PostgreSQL environment:
-- Drop existing tables if they exist
DROP TABLE IF EXISTS sales CASCADE;
DROP TABLE IF EXISTS employees CASCADE;
DROP TABLE IF EXISTS departments CASCADE;
-- Departments table
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
budget DECIMAL(12,2),
location VARCHAR(100)
);
-- Employees table
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
job_title VARCHAR(100),
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL,
manager_id INT REFERENCES employees(employee_id),
performance_rating DECIMAL(3,2)
);
-- Sales table
CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
employee_id INT REFERENCES employees(employee_id),
amount DECIMAL(10,2),
sale_date DATE,
region VARCHAR(50)
);
-- Insert department data
INSERT INTO departments (name, budget, location) VALUES
('Engineering', 2500000, 'San Francisco'),
('Sales', 1800000, 'New York'),
('Marketing', 950000, 'Austin'),
('HR', 600000, 'Chicago'),
('Finance', 1200000, 'Boston');
-- Insert employee data (50+ employees across 5 departments)
INSERT INTO employees (name, department, job_title, salary, hire_date, manager_id, performance_rating) VALUES
-- Engineering (15 employees)
('Alice Chen', 'Engineering', 'VP Engineering', 185000, '2018-01-15', NULL, 4.8),
('Bob Martinez', 'Engineering', 'Senior Engineer', 145000, '2019-03-22', 1, 4.5),
('Carol Wu', 'Engineering', 'Senior Engineer', 142000, '2019-06-10', 1, 4.6),
('David Kim', 'Engineering', 'Mid Engineer', 105000, '2020-02-18', 1, 4.2),
('Emma Johnson', 'Engineering', 'Mid Engineer', 108000, '2020-05-20', 1, 4.4),
('Frank Lee', 'Engineering', 'Junior Engineer', 78000, '2021-08-15', 2, 3.9),
('Grace Park', 'Engineering', 'Junior Engineer', 80000, '2021-09-01', 2, 4.1),
('Henry Zhou', 'Engineering', 'Mid Engineer', 112000, '2020-11-10', 1, 4.3),
('Iris Wang', 'Engineering', 'Senior Engineer', 148000, '2019-01-05', 1, 4.7),
('Jack Thompson', 'Engineering', 'Mid Engineer', 98000, '2021-03-12', 1, 3.8),
('Kate Singh', 'Engineering', 'Junior Engineer', 75000, '2022-01-20', 3, 4.0),
('Liam Brown', 'Engineering', 'Mid Engineer', 106000, '2020-07-08', 1, 4.2),
('Maya Patel', 'Engineering', 'Senior Engineer', 140000, '2019-09-15', 1, 4.5),
('Nathan Hill', 'Engineering', 'Junior Engineer', 82000, '2021-11-22', 2, 4.0),
('Olivia Green', 'Engineering', 'Mid Engineer', 110000, '2020-10-05', 1, 4.3),
-- Sales (15 employees)
('Paul Rivera', 'Sales', 'VP Sales', 165000, '2017-06-01', NULL, 4.6),
('Quinn Adams', 'Sales', 'Senior Sales Rep', 125000, '2018-08-15', 16, 4.7),
('Rachel Foster', 'Sales', 'Senior Sales Rep', 122000, '2019-02-20', 16, 4.5),
('Sam Mitchell', 'Sales', 'Sales Rep', 95000, '2020-04-10', 16, 4.3),
('Tina Lopez', 'Sales', 'Sales Rep', 92000, '2020-06-18', 16, 4.2),
('Uma Davis', 'Sales', 'Junior Sales Rep', 68000, '2021-07-25', 17, 3.9),
('Victor Chang', 'Sales', 'Junior Sales Rep', 70000, '2021-10-12', 17, 4.0),
('Wendy Scott', 'Sales', 'Sales Rep', 98000, '2020-09-05', 16, 4.4),
('Xavier Moore', 'Sales', 'Senior Sales Rep', 128000, '2018-11-20', 16, 4.6),
('Yara Wilson', 'Sales', 'Sales Rep', 90000, '2020-12-08', 16, 4.1),
('Zack Turner', 'Sales', 'Junior Sales Rep', 72000, '2022-02-15', 18, 3.8),
('Amy Phillips', 'Sales', 'Sales Rep', 94000, '2020-08-22', 16, 4.2),
('Brian Clark', 'Sales', 'Senior Sales Rep', 130000, '2018-05-10', 16, 4.8),
('Chloe Harris', 'Sales', 'Sales Rep', 96000, '2020-10-30', 16, 4.3),
('Derek Young', 'Sales', 'Junior Sales Rep', 69000, '2021-12-05', 17, 3.9),
-- Marketing (10 employees)
('Elena Rodriguez', 'Marketing', 'Marketing Director', 142000, '2018-03-10', NULL, 4.5),
('Felix Anderson', 'Marketing', 'Senior Marketer', 108000, '2019-07-22', 31, 4.3),
('Gina Thomas', 'Marketing', 'Senior Marketer', 105000, '2019-11-15', 31, 4.4),
('Hugo Jackson', 'Marketing', 'Marketing Specialist', 82000, '2020-05-18', 31, 4.1),
('Ivy White', 'Marketing', 'Marketing Specialist', 85000, '2020-08-25', 31, 4.2),
('James Martin', 'Marketing', 'Junior Marketer', 62000, '2021-09-10', 32, 3.8),
('Kelly Garcia', 'Marketing', 'Junior Marketer', 64000, '2021-11-28', 32, 3.9),
('Leo Martinez', 'Marketing', 'Marketing Specialist', 88000, '2020-06-12', 31, 4.3),
('Monica Robinson', 'Marketing', 'Senior Marketer', 110000, '2019-04-20', 31, 4.5),
('Noah Walker', 'Marketing', 'Marketing Specialist', 80000, '2020-09-15', 31, 4.0),
-- HR (6 employees)
('Oprah Hall', 'HR', 'HR Director', 128000, '2017-09-05', NULL, 4.4),
('Peter Allen', 'HR', 'Senior HR Manager', 95000, '2019-01-12', 41, 4.2),
('Quincy Young', 'HR', 'HR Specialist', 72000, '2020-03-20', 41, 4.0),
('Rosa King', 'HR', 'HR Specialist', 75000, '2020-07-15', 41, 4.1),
('Steve Wright', 'HR', 'Junior HR Coordinator', 58000, '2021-10-08', 42, 3.8),
('Tara Lopez', 'HR', 'HR Specialist', 70000, '2020-11-22', 41, 3.9),
-- Finance (8 employees)
('Ulysses Green', 'Finance', 'CFO', 195000, '2016-05-01', NULL, 4.9),
('Vera Adams', 'Finance', 'Senior Accountant', 115000, '2018-06-15', 47, 4.5),
('Will Baker', 'Finance', 'Senior Accountant', 112000, '2018-09-20', 47, 4.4),
('Xena Carter', 'Finance', 'Accountant', 88000, '2020-02-10', 47, 4.2),
('Yuki Tanaka', 'Finance', 'Accountant', 85000, '2020-04-25', 47, 4.1),
('Zoe Evans', 'Finance', 'Junior Analyst', 68000, '2021-07-18', 48, 3.9),
('Aaron Ross', 'Finance', 'Accountant', 90000, '2020-01-15', 47, 4.3),
('Beth Collins', 'Finance', 'Junior Analyst', 65000, '2021-09-22', 48, 3.8);
-- Insert sales data (30+ sales records)
INSERT INTO sales (employee_id, amount, sale_date, region) VALUES
-- Q1 2024 Sales
(17, 45000, '2024-01-05', 'Northeast'),
(18, 38000, '2024-01-12', 'Northeast'),
(19, 52000, '2024-01-18', 'West'),
(20, 29000, '2024-01-25', 'Midwest'),
(21, 31000, '2024-01-28', 'South'),
(24, 44000, '2024-02-02', 'West'),
(25, 48000, '2024-02-08', 'Northeast'),
(17, 55000, '2024-02-15', 'Northeast'),
(18, 42000, '2024-02-20', 'West'),
(28, 39000, '2024-02-25', 'South'),
(19, 61000, '2024-03-05', 'West'),
(20, 33000, '2024-03-10', 'Midwest'),
(25, 52000, '2024-03-15', 'Northeast'),
(17, 47000, '2024-03-22', 'South'),
(28, 44000, '2024-03-28', 'West'),
-- Q2 2024 Sales
(18, 58000, '2024-04-03', 'Northeast'),
(19, 49000, '2024-04-08', 'West'),
(24, 51000, '2024-04-15', 'South'),
(25, 46000, '2024-04-20', 'Northeast'),
(17, 63000, '2024-04-25', 'West'),
(20, 35000, '2024-05-02', 'Midwest'),
(28, 48000, '2024-05-10', 'South'),
(19, 54000, '2024-05-15', 'West'),
(18, 41000, '2024-05-22', 'Northeast'),
(25, 59000, '2024-05-28', 'Northeast'),
(17, 66000, '2024-06-05', 'West'),
(24, 53000, '2024-06-12', 'South'),
(19, 57000, '2024-06-18', 'West'),
(28, 45000, '2024-06-25', 'Northeast'),
(20, 38000, '2024-06-28', 'Midwest');
-- Verify data
SELECT 'Departments' as table_name, COUNT(*) as record_count FROM departments
UNION ALL
SELECT 'Employees', COUNT(*) FROM employees
UNION ALL
SELECT 'Sales', COUNT(*) FROM sales;
💡 Tip: Run the verification query at the end to confirm you have 5 departments, 54 employees, and 30+ sales records.
A scalar subquery returns a single value that can be used in comparisons. This is perfect for benchmarking against company-wide metrics.
-- Find employees earning above the company average
SELECT name, department, salary,
(SELECT AVG(salary) FROM employees) as company_avg,
salary - (SELECT AVG(salary) FROM employees) as diff_from_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
Expected Output: ~27 employees earning above $98,000 (company average)
💡 Tip: Notice we use the same subquery
(SELECT AVG(salary) FROM employees)three times. This works, but it's inefficient. We'll solve this with CTEs later!
A correlated subquery references columns from the outer query. It runs once for each row, making it perfect for department-specific comparisons.
-- Find employees earning above THEIR department's average
SELECT e.name, e.department, e.salary,
(SELECT AVG(salary)
FROM employees
WHERE department = e.department) as dept_avg,
e.salary - (SELECT AVG(salary)
FROM employees
WHERE department = e.department) as diff_from_dept_avg
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department = e.department
)
ORDER BY e.department, e.salary DESC;
Expected Output: High performers in each department (Engineering: Alice, Bob, Carol, Iris, Maya; Sales: Paul, Quinn, Rachel, Xavier, Brian; etc.)
💡 Tip: Correlated subqueries can be slow on large datasets because they execute once per row. Use
EXPLAIN ANALYZEto check performance.
This is where readable SQL begins. CTEs let you define named temporary result sets that make complex queries easy to understand and maintain.
-- Find high earners using a CTE
WITH high_earners AS (
SELECT name, department, salary
FROM employees
WHERE salary > 100000
)
SELECT * FROM high_earners ORDER BY salary DESC;
Why CTEs Matter:
Let's rewrite the correlated subquery example with a CTE:
-- Calculate department averages ONCE, then join
WITH dept_stats AS (
SELECT department,
AVG(salary) as avg_salary,
COUNT(*) as emp_count,
MAX(salary) as max_salary,
MIN(salary) as min_salary
FROM employees
GROUP BY department
)
SELECT e.name, e.department, e.salary,
d.avg_salary,
d.emp_count,
e.salary - d.avg_salary as diff_from_avg,
ROUND((e.salary - d.avg_salary) / d.avg_salary * 100, 2) as pct_above_avg
FROM employees e
JOIN dept_stats d ON e.department = d.department
WHERE e.salary > d.avg_salary
ORDER BY pct_above_avg DESC;
Expected Output: Same results as the correlated subquery, but faster and easier to read!
You can define multiple CTEs separated by commas. This is incredibly powerful for complex analysis.
-- Compare employees to BOTH department AND company averages
WITH
dept_averages AS (
SELECT department, AVG(salary) as avg_sal
FROM employees
GROUP BY department
),
company_average AS (
SELECT AVG(salary) as overall_avg
FROM employees
),
performance_tiers AS (
SELECT
CASE
WHEN performance_rating >= 4.5 THEN 'Top Performer'
WHEN performance_rating >= 4.0 THEN 'Solid Performer'
ELSE 'Needs Improvement'
END as tier,
COUNT(*) as count,
AVG(salary) as avg_salary
FROM employees
GROUP BY tier
)
SELECT e.name, e.department, e.salary, e.performance_rating,
da.avg_sal as dept_avg,
ca.overall_avg as company_avg,
CASE
WHEN e.performance_rating >= 4.5 THEN 'Top Performer'
WHEN e.performance_rating >= 4.0 THEN 'Solid Performer'
ELSE 'Needs Improvement'
END as performance_tier
FROM employees e
JOIN dept_averages da ON e.department = da.department
CROSS JOIN company_average ca
WHERE e.salary > da.avg_sal AND e.salary > ca.overall_avg
ORDER BY e.performance_rating DESC, e.salary DESC;
Expected Output: Elite employees who earn above both their department AND company average (~20 employees)
💡 Tip: Use
CROSS JOINwhen you need to combine every row with a single-row result set (like company averages).
Let's solve the same problem both ways to see the readability difference.
Subquery Version (hard to read):
SELECT e.name, e.department, e.salary,
(SELECT AVG(salary) FROM employees WHERE department = e.department) as dept_avg,
(SELECT AVG(salary) FROM employees) as company_avg,
e.salary - (SELECT AVG(salary) FROM employees WHERE department = e.department) as dept_diff,
e.salary - (SELECT AVG(salary) FROM employees) as company_diff
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department = e.department)
AND e.salary > (SELECT AVG(salary) FROM employees)
ORDER BY e.salary DESC;
CTE Version (clean and clear):
WITH
dept_stats AS (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
),
company_stats AS (
SELECT AVG(salary) as avg_salary
FROM employees
)
SELECT e.name, e.department, e.salary,
d.avg_salary as dept_avg,
c.avg_salary as company_avg,
e.salary - d.avg_salary as dept_diff,
e.salary - c.avg_salary as company_diff
FROM employees e
JOIN dept_stats d ON e.department = d.department
CROSS JOIN company_stats c
WHERE e.salary > d.avg_salary AND e.salary > c.avg_salary
ORDER BY e.salary DESC;
Which would you rather debug at 2 AM? The CTE version wins every time.
When checking for existence in a subquery, EXISTS can be significantly faster than IN.
Using IN (scans entire subquery result):
-- Find employees in high-budget departments
SELECT name, department, salary
FROM employees
WHERE department IN (
SELECT name
FROM departments
WHERE budget > 1000000
)
ORDER BY department, salary DESC;
Using EXISTS (stops at first match):
-- Same query with EXISTS (often faster)
SELECT e.name, e.department, e.salary
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.name = e.department
AND d.budget > 1000000
)
ORDER BY e.department, e.salary DESC;
Expected Output: All employees in Engineering, Sales, and Finance (budget > $1M)
💡 Tip: Use
EXISTSwhen you only need to check if a match exists. UseINwhen you need the actual values from the subquery for other operations.
Performance Rule of Thumb:
EXISTS: Best for correlated checks (department-by-department)IN: Best for small, static lists of valuesJOIN: Often fastest for large result setsSometimes you need to nest subqueries multiple levels deep. This is where CTEs really shine for readability.
Subquery Version (3 levels deep):
-- Find employees in top-performing departments
SELECT name, department, salary, performance_rating
FROM employees
WHERE department IN (
SELECT department
FROM employees
GROUP BY department
HAVING AVG(performance_rating) > (
SELECT AVG(performance_rating) FROM employees
)
)
ORDER BY department, salary DESC;
CTE Version (much clearer):
-- Same query with CTEs
WITH
company_performance AS (
SELECT AVG(performance_rating) as avg_rating
FROM employees
),
dept_performance AS (
SELECT department,
AVG(performance_rating) as avg_rating,
COUNT(*) as team_size
FROM employees
GROUP BY department
),
top_departments AS (
SELECT d.department
FROM dept_performance d
CROSS JOIN company_performance c
WHERE d.avg_rating > c.avg_rating
)
SELECT e.name, e.department, e.salary, e.performance_rating
FROM employees e
JOIN top_departments t ON e.department = t.department
ORDER BY e.department, e.salary DESC;
Expected Output: Employees in Engineering, Sales, and Marketing (departments with above-average performance ratings)
Now it's your turn! Complete these exercises to demonstrate mastery of subqueries and CTEs.
Business Question: The CEO wants to identify sales representatives who generated above-average revenue in Q2 2024. Create a report showing their names, total sales, and how much they exceeded the average.
Starter Code (70% provided):
-- Calculate sales performance for Q2 2024 (April-June)
WITH sales_performance AS (
SELECT
e.name,
e.department,
-- TODO: Calculate total sales for each employee
-- Hint: Use SUM(s.amount) and group by employee
_____ as total_sales,
COUNT(s.sale_id) as num_sales
FROM employees e
JOIN sales s ON e.employee_id = s.employee_id
WHERE s.sale_date >= '2024-04-01' AND s.sale_date < '2024-07-01'
GROUP BY e.employee_id, e.name, e.department
),
average_sales AS (
-- TODO: Calculate the average total_sales from sales_performance CTE
SELECT _____ as avg_sales
FROM _____
)
SELECT
sp.name,
sp.total_sales,
a.avg_sales,
-- TODO: Calculate how much they exceeded the average
sp.total_sales - a.avg_sales as above_average,
-- TODO: Calculate percentage above average (round to 2 decimals)
ROUND((_____) * 100, 2) as pct_above_avg
FROM sales_performance sp
CROSS JOIN average_sales a
-- TODO: Filter to only show employees above average
WHERE _____
ORDER BY sp.total_sales DESC;
Success Criteria:
Expected Output: 4-5 top performers including Quinn Adams, Paul Rivera, and others
Business Question: Find employees who simultaneously:
Your Task: Write this query from scratch using CTEs. No starter code provided.
Hints:
PERCENTILE_CONT(0.5) for median (50th percentile)PERCENTILE_CONT(0.75) for 75th percentilePERCENTILE_CONT(fraction) WITHIN GROUP (ORDER BY column)Success Criteria:
Expected Output: ~10-15 high earners (mostly VPs, Directors, and Senior roles)
Business Question: Find all employees who have made at least one sale. First write it with IN, then rewrite with EXISTS.
Version 1 - Using IN:
-- TODO: Complete this query using IN
SELECT name, department, job_title
FROM employees
WHERE employee_id IN (
-- TODO: Write subquery to get distinct employee_ids from sales table
_____
)
ORDER BY name;
Version 2 - Using EXISTS:
-- TODO: Rewrite using EXISTS
SELECT e.name, e.department, e.job_title
FROM employees e
WHERE EXISTS (
-- TODO: Write correlated subquery checking if sales exist for this employee
_____
)
ORDER BY e.name;
Success Criteria:
e.employee_id = s.employee_idExpected Output: 7-8 employees (all from Sales department)
Business Question: Create a comprehensive department report showing:
Your Task: Build this entirely from scratch using CTEs.
Hints:
(total_payroll / budget) * 100Success Criteria:
Expected Output: 5 rows (one per department) with Engineering likely showing highest utilization
Use these verification queries to check your answers:
-- Verify TODO 1: Top sales performers in Q2
-- Should show 4-5 people with sales > ~$50K
SELECT COUNT(*) as num_top_performers
FROM (
SELECT e.employee_id, SUM(s.amount) as total
FROM employees e
JOIN sales s ON e.employee_id = s.employee_id
WHERE s.sale_date >= '2024-04-01' AND s.sale_date < '2024-07-01'
GROUP BY e.employee_id
HAVING SUM(s.amount) > (
SELECT AVG(total_sales)
FROM (
SELECT SUM(amount) as total_sales
FROM sales
WHERE sale_date >= '2024-04-01' AND sale_date < '2024-07-01'
GROUP BY employee_id
) sub
)
) results;
-- Expected: 4-5 rows
-- Verify TODO 2: Double comparison (dept median + company 75th percentile)
-- Should show ~10-15 high earners
WITH dept_medians AS (
SELECT department,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median_sal
FROM employees
GROUP BY department
),
company_p75 AS (
SELECT PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as p75_sal
FROM employees
)
SELECT COUNT(*) as num_elite_earners
FROM employees e
JOIN dept_medians d ON e.department = d.department
CROSS JOIN company_p75 c
WHERE e.salary > d.median_sal AND e.salary > c.p75_sal;
-- Expected: 10-15 rows
-- Verify TODO 3: Employees with sales
-- Should show 7-8 employees (from Sales dept)
SELECT COUNT(DISTINCT employee_id) as employees_with_sales
FROM sales;
-- Expected: 7-8 employees
-- Verify TODO 4: Department budget utilization
-- Should show 5 departments with varying utilization rates
SELECT department,
SUM(salary) * 12 as annual_payroll,
(SELECT budget FROM departments d WHERE d.name = e.department) as budget
FROM employees e
GROUP BY department;
-- Expected: 5 rows, Engineering likely highest utilization
| Scenario | Best Approach | Why |
|---|---|---|
| Single aggregate comparison | Scalar subquery | Simple, one value needed |
| Row-by-row comparison | Correlated subquery or CTE + JOIN | CTE more readable |
| Complex multi-step logic | CTE | Readable, debuggable |
| Checking existence | EXISTS | Stops at first match |
| Small static list | IN | Clean syntax for (1,2,3) |
| Large result set merging | JOIN | Optimized by query planner |
| Reusing same calculation | CTE | Calculate once, use many times |
| Temporary staging data | CTE or temp table | CTE for queries, temp table for procedures |
When CTEs Can Be Slower:
WITH cte AS MATERIALIZED or AS NOT MATERIALIZED to control behaviorWhen Subqueries Can Be Slower:
Always Profile Your Queries:
EXPLAIN ANALYZE
WITH your_cte AS (...)
SELECT ...;
CTEs can be recursive for hierarchical data like org charts:
-- Find all employees in Alice Chen's reporting chain
WITH RECURSIVE org_chart AS (
-- Base case: Start with Alice
SELECT employee_id, name, manager_id, 1 as level
FROM employees
WHERE name = 'Alice Chen'
UNION ALL
-- Recursive case: Find direct reports
SELECT e.employee_id, e.name, e.manager_id, o.level + 1
FROM employees e
JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart ORDER BY level, name;
We'll explore recursive CTEs in advanced SQL courses!
Once you've completed all TODOs and verified your results:
activity06_yourname.sqlSubmit via: Activity 6 Submission Form
By completing this activity, you've mastered:
✅ Scalar subqueries for single-value comparisons ✅ Correlated subqueries for row-by-row analysis ✅ CTEs for readable, maintainable complex queries ✅ Multiple CTEs to break down multi-step logic ✅ EXISTS vs IN performance optimization ✅ Multi-level nested queries for advanced filtering ✅ When to use each approach based on scenario and performance
Real-World Application: These skills are essential for business intelligence, reporting dashboards, and data analysis. Senior developers write CTEs for complex queries because they're easier to review, test, and maintain.
Next Steps: In Activity 7, we'll combine subqueries with window functions to create even more powerful analytics queries!
💡 Pro Tip: Bookmark this activity! The CTE patterns you learned today are used in 80% of professional SQL analysis work. When you're stuck on a complex query in the future, come back and review the "Multiple CTEs" and "CTE vs Subquery" sections.