Advanced SQL: Subqueries, CTEs, and Window Functions

Once you master SELECT, WHERE, and JOIN, you can answer simple questions. This page gives you the tools to answer hard questions — “Who are the top 3 earners in each department?”, “What is the running total of salaries?”, “Which employees earn above the company average?” — using subqueries, CTEs, and window functions. You will also learn how indexes make queries fast and how views let you save complex queries as reusable objects.

Subqueries

A subquery is a SELECT inside another SQL statement. The inner query runs first and its result is used by the outer query. You can place subqueries in the WHERE clause, the FROM clause, or even the SELECT list.

Subquery in WHERE

-- Employees who earn above the company average salary
SELECT employee_name, salary
FROM employee
WHERE salary > (
    SELECT AVG(salary) FROM employee
);

The inner query computes a single value (AVG(salary)). The outer query compares each row’s salary against it.

Subquery with IN

-- Employees who work in departments located in a specific city
-- (Assume a hypothetical dept_city column for illustration)
SELECT employee_name
FROM employee
WHERE department_id IN (
    SELECT department_id
    FROM department
    WHERE department_name IN ('Engineering', 'Finance')
);

Correlated Subquery

A correlated subquery references a column from the outer query. It re-executes for every row the outer query processes.

-- Employees who earn more than the average salary of their own department
SELECT e.employee_name, e.salary, e.department_id
FROM employee e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employee
    WHERE department_id = e.department_id   -- references outer row
);

Correlated subqueries are powerful but can be slow on large tables because they run once per row. A JOIN or window function often achieves the same result more efficiently.

Common Table Expressions (CTEs)

A CTE gives a subquery a name and makes it readable. You define it with WITH before the main query. CTEs can reference each other, making multi-step logic clean and debuggable.

Basic CTE Syntax

WITH cte_name AS (
    SELECT ...
    FROM   ...
)
SELECT *
FROM cte_name
WHERE ...;

Example: High Earners per Department

WITH dept_avg AS (
    SELECT
        department_id,
        AVG(salary) AS avg_salary
    FROM employee
    WHERE department_id IS NOT NULL
    GROUP BY department_id
)
SELECT
    e.employee_name,
    e.salary,
    da.avg_salary      AS dept_avg,
    e.salary - da.avg_salary AS above_avg_by
FROM employee e
JOIN dept_avg da ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary
ORDER BY above_avg_by DESC;

Chained CTEs

WITH
    dept_stats AS (
        SELECT department_id, AVG(salary) AS avg_sal, MAX(salary) AS max_sal
        FROM employee
        GROUP BY department_id
    ),
    high_performers AS (
        SELECT e.employee_name, e.salary, e.department_id
        FROM employee e
        JOIN dept_stats ds ON e.department_id = ds.department_id
        WHERE e.salary >= ds.avg_sal * 1.20   -- 20% above dept average
    )
SELECT hp.employee_name, hp.salary, d.department_name
FROM high_performers hp
JOIN department d ON hp.department_id = d.department_id
ORDER BY hp.salary DESC;

Use CTEs instead of deeply nested subqueries. Each CTE acts like a named, temporary result set — read the query top-to-bottom rather than inside-out.

Window Functions

Window functions perform calculations across a set of related rows without collapsing them the way GROUP BY does. Every row stays in the output; the function just adds an extra computed column. The key syntax element is OVER(...), which defines the “window” — which rows the function looks at when computing each result.

SELECT
    employee_name,
    department_id,
    salary,
    FUNCTION() OVER (
        PARTITION BY department_id   -- group rows into windows
        ORDER BY salary DESC         -- order within each window
    ) AS result_column
FROM employee;

ROW_NUMBER()

Assigns a unique sequential integer to each row within a window.

SELECT
    employee_name,
    department_id,
    salary,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
    ) AS rank_in_dept
FROM employee
WHERE department_id IS NOT NULL;

employee_name	department_id	salary	rank_in_dept
Sneha Patel	1	81000	1
Priya Reddy	1	72000	2
Rahul Sharma	1	65000	3
Karthik Nair	2	61000	1
Anitha Rao	2	56000	2

Top 3 Earners Per Department

Wrap the window function in a CTE and filter on the rank:

WITH ranked AS (
    SELECT
        employee_name,
        department_id,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rn
    FROM employee
    WHERE department_id IS NOT NULL
)
SELECT employee_name, department_id, salary
FROM ranked
WHERE rn <= 3
ORDER BY department_id, salary DESC;

RANK() and DENSE_RANK()

Both assign ranks but handle ties differently.

SELECT
    employee_name,
    salary,
    RANK()       OVER (ORDER BY salary DESC) AS rank_skips,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no_gaps
FROM employee;

employee_name	salary	rank_skips	rank_no_gaps
Sneha Patel	81000	1	1
Harsha Vardhan	76000	2	2
Priya Reddy	72000	3	3
Rahul Sharma	65000	4	4
Neha Kapoor	64000	5	5

If two employees share a salary: RANK() would give them both rank 3 and skip rank 4. DENSE_RANK() gives them both rank 3 and continues with rank 4 for the next employee.

LAG() and LEAD()

Access the value from a previous (LAG) or following (LEAD) row within the window.

-- Compare each employee's salary to the next-lower earner
SELECT
    employee_name,
    salary,
    LAG(salary)  OVER (ORDER BY salary DESC) AS prev_salary,
    LEAD(salary) OVER (ORDER BY salary DESC) AS next_salary,
    salary - LEAD(salary) OVER (ORDER BY salary DESC) AS salary_gap
FROM employee
ORDER BY salary DESC;

Running Total (Cumulative SUM)

SELECT
    employee_name,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM employee;

CASE WHEN Expressions

CASE WHEN is SQL’s conditional logic — like if/elif/else inside a query.

Simple Classification

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 75000 THEN 'Senior'
        WHEN salary >= 60000 THEN 'Mid-level'
        ELSE                      'Junior'
    END AS level
FROM employee
ORDER BY salary DESC;

Conditional Aggregation

You can combine CASE WHEN with aggregate functions to create pivot-like summaries.

-- Count employees by level in a single row
SELECT
    COUNT(CASE WHEN salary >= 75000 THEN 1 END) AS senior_count,
    COUNT(CASE WHEN salary BETWEEN 60000 AND 74999 THEN 1 END) AS mid_count,
    COUNT(CASE WHEN salary < 60000 THEN 1 END) AS junior_count
FROM employee;

String Functions

SQLite provides built-in string functions for cleaning and transforming text data.

SELECT
    UPPER(employee_name)         AS name_upper,
    LOWER(employee_name)         AS name_lower,
    LENGTH(employee_name)        AS name_length,
    SUBSTR(employee_name, 1, 5)  AS first_5_chars,
    REPLACE(city, 'Hyderabad', 'HYD') AS city_short
FROM employee;

Function	Purpose
`UPPER(s)`	Convert to uppercase
`LOWER(s)`	Convert to lowercase
`LENGTH(s)`	Character count
`SUBSTR(s, start, len)`	Extract substring (1-indexed)
`REPLACE(s, old, new)`	Substitute occurrences
`TRIM(s)`	Remove leading and trailing spaces
`INSTR(s, sub)`	Position of first occurrence

Date Functions

Store dates as TEXT in 'YYYY-MM-DD' format and use SQLite’s date functions to manipulate them.

-- Employees who joined in the last 2 years
SELECT employee_name, joining_date
FROM employee
WHERE joining_date >= DATE('now', '-2 years')
ORDER BY joining_date DESC;

-- Months since each employee joined
SELECT
    employee_name,
    joining_date,
    CAST(
        (JULIANDAY('now') - JULIANDAY(joining_date)) / 30
    AS INTEGER) AS months_tenure
FROM employee
ORDER BY months_tenure DESC;

Indexes

An index is a data structure that lets the database find rows matching a condition without scanning every row in the table. Think of it as the index at the back of a book.

Why Indexes Matter

Without an index, a query like WHERE city = 'Hyderabad' reads every single row (a full table scan). With an index on city, the database jumps directly to the matching rows.

-- Create an index on the city column
CREATE INDEX idx_employee_city ON employee(city);

-- Create a composite index for queries filtering on both columns
CREATE INDEX idx_emp_dept_salary ON employee(department_id, salary);

When to Index

Good candidates

Columns used frequently in WHERE clauses
Columns used in JOIN conditions
Columns used in ORDER BY on large tables
Foreign key columns

When to avoid

Very small tables (full scan is fast enough)
Columns you update very frequently (indexes slow down writes)
Columns with very low cardinality (e.g., a boolean column)

Query Plan

Check how SQLite executes your query with EXPLAIN QUERY PLAN:

EXPLAIN QUERY PLAN
SELECT * FROM employee WHERE city = 'Hyderabad';

Look for USING INDEX in the output — that confirms the index is being used.

Views

A view is a saved query stored in the database that you can query like a table. It does not store data itself — it re-runs the underlying query each time you select from it.

Create a View

CREATE VIEW v_employee_details AS
SELECT
    e.employee_id,
    e.employee_name,
    e.salary,
    e.city,
    d.department_name,
    CASE
        WHEN e.salary >= 75000 THEN 'Senior'
        WHEN e.salary >= 60000 THEN 'Mid-level'
        ELSE 'Junior'
    END AS level
FROM employee e
LEFT JOIN department d ON e.department_id = d.department_id;

Query a View

-- Views look and behave exactly like tables
SELECT * FROM v_employee_details WHERE level = 'Senior';

SELECT department_name, COUNT(*) AS headcount
FROM v_employee_details
GROUP BY department_name;

Drop a View

DROP VIEW IF EXISTS v_employee_details;

Views are perfect for hiding complex JOINs and CASE logic from application code. Your Python script can run SELECT * FROM v_employee_details without knowing anything about the underlying table structure.

Complete Analytics Example

This query uses CTEs, window functions, and CASE expressions to produce a department-level performance report in one statement:

WITH ranked_salaries AS (
    SELECT
        e.employee_name,
        e.salary,
        d.department_name,
        RANK() OVER (
            PARTITION BY e.department_id
            ORDER BY e.salary DESC
        ) AS dept_rank,
        AVG(e.salary) OVER (
            PARTITION BY e.department_id
        ) AS dept_avg_salary
    FROM employee e
    JOIN department d ON e.department_id = d.department_id
)
SELECT
    department_name,
    employee_name,
    salary,
    ROUND(dept_avg_salary, 0)           AS dept_avg,
    ROUND(salary - dept_avg_salary, 0)  AS vs_dept_avg,
    dept_rank,
    CASE
        WHEN dept_rank = 1 THEN '🥇 Top Earner'
        WHEN dept_rank = 2 THEN '🥈 Second'
        WHEN dept_rank = 3 THEN '🥉 Third'
        ELSE 'Other'
    END AS standing
FROM ranked_salaries
WHERE dept_rank <= 3
ORDER BY department_name, dept_rank;

Next: NumPy

Move into data analysis — learn NumPy arrays, vectorized operations, and broadcasting for fast numerical computing in Python.

Project Development

SQL & Databases

Data Analysis

Advanced SQL: Subqueries, CTEs, and Window Functions

Subqueries

Subquery in WHERE

Subquery with IN

Correlated Subquery

Common Table Expressions (CTEs)

Basic CTE Syntax

Example: High Earners per Department

Chained CTEs

Window Functions

ROW_NUMBER()

Top 3 Earners Per Department

RANK() and DENSE_RANK()

LAG() and LEAD()

Running Total (Cumulative SUM)

CASE WHEN Expressions

Simple Classification

Conditional Aggregation

String Functions

Date Functions

Indexes

Why Indexes Matter

When to Index

Good candidates

When to avoid

Query Plan

Views

Create a View

Query a View

Drop a View

Complete Analytics Example

Next: NumPy

​Subqueries

​Subquery in WHERE

​Subquery with IN

​Correlated Subquery

​Common Table Expressions (CTEs)

​Basic CTE Syntax

​Example: High Earners per Department

​Chained CTEs

​Window Functions

​ROW_NUMBER()

​Top 3 Earners Per Department

​RANK() and DENSE_RANK()

​LAG() and LEAD()

​Running Total (Cumulative SUM)

​CASE WHEN Expressions

​Simple Classification

​Conditional Aggregation

​String Functions

​Date Functions

​Indexes

​Why Indexes Matter

​When to Index

Good candidates

When to avoid

​Query Plan

​Views

​Create a View

​Query a View

​Drop a View

​Complete Analytics Example

Next: NumPy

Subqueries

Subquery in WHERE

Subquery with IN

Correlated Subquery

Common Table Expressions (CTEs)

Basic CTE Syntax

Example: High Earners per Department

Chained CTEs

Window Functions

ROW_NUMBER()

Top 3 Earners Per Department

RANK() and DENSE_RANK()

LAG() and LEAD()

Running Total (Cumulative SUM)

CASE WHEN Expressions

Simple Classification

Conditional Aggregation

String Functions

Date Functions

Indexes

Why Indexes Matter

When to Index

Query Plan

Views

Create a View

Query a View

Drop a View

Complete Analytics Example