Advanced SQL Queries – Subqueries, CTEs, and Window Functions

Advanced SQL Queries – Subqueries, CTEs, and Window Functions

By now, you know how to retrieve, filter, summarize, join, and modify data in SQL. But real-world data analysis often requires more complex queries. This is where advanced SQL techniques come in: subqueries, Common Table Expressions (CTEs), and window functions.

In this blog, we’ll break these concepts down with clear examples so you can write queries for analytics and reporting.

1. Subqueries (Nested Queries)

A subquery is a query inside another query. It lets you use the result of one query as input for another.

Example: Employees with Above-Average Salary

SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

  • The inner query calculates the average salary.

  • The outer query selects employees earning more than the average.

Types of Subqueries

  • Scalar subquery – returns a single value (like the example above).

  • Column subquery – returns a single column used with IN.

  • Row subquery – returns a row for comparison with (col1, col2).

2. Common Table Expressions (CTEs)

A CTE is a temporary result set you can reference within a query. It makes complex queries easier to read and maintain.

Example: Top 5 Highest Paid Employees

WITH TopSalaries AS (
    SELECT first_name, last_name, salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 5
)
SELECT *
FROM TopSalaries;

  • WITH TopSalaries AS (...) defines the temporary table.

  • Makes queries modular and readable.

CTEs with Aggregation

WITH DepartmentSalary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT *
FROM DepartmentSalary
WHERE avg_salary > 60000;

  • Finds departments with average salary above 60,000.

3. Window Functions

Window functions perform calculations across rows related to the current row, without collapsing rows like GROUP BY.

Example: Ranking Employees by Salary

SELECT first_name, last_name, salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

  • Assigns a rank based on salary.

  • RANK() allows ties; ROW_NUMBER() assigns unique numbers.

Other Common Window Functions

  • ROW_NUMBER() – sequential numbering.

  • DENSE_RANK() – like RANK(), but no gaps in ranking.

  • LEAD() / LAG() – access next or previous row values.

Example: Salary Difference from Previous Employee

SELECT first_name, last_name, salary,
       LAG(salary) OVER (ORDER BY salary) AS prev_salary,
       salary - LAG(salary) OVER (ORDER BY salary) AS diff
FROM employees;

  • Shows how each salary differs from the previous.

4. Tips for Using Advanced Queries

  • Use subqueries for one-off calculations.

  • Use CTEs for readability and multi-step queries.

  • Use window functions for analytics like ranking, running totals, or moving averages.

  • Combine these tools for powerful, production-ready queries.

Advanced SQL queries take your data analysis to the next level. With subqueries, CTEs, and window functions, you can:

  • Perform calculations across multiple rows.

  • Break down complex problems into readable steps.

  • Analyze trends, ranks, and differences in data.


Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more