Advanced Queries

Subqueries, CTEs, and window functions.

Beyond Basic Queries

Once you master basic SELECT statements, joins, and aggregations, you're ready for advanced querying techniques. Subqueries let you nest queries inside queries. CTEs (Common Table Expressions) make complex queries readable and reusable. Window functions perform calculations across rows without grouping them. These tools unlock powerful analytical capabilities and help you write cleaner, more maintainable SQL.

Our Sample Tables

Every subquery, CTE, and window-function result below was produced by running the query against this data on PostgreSQL 16, so you can reproduce any of them yourself. A few later "pattern" examples reference other illustrative tables to keep those patterns generic.

-- Every subquery, CTE, and window-function example in this lesson runs
-- against these four tables. (A handful of later "pattern" examples use
-- other illustrative tables, like products or inventory_snapshots, to
-- keep those patterns generic; they aren't part of this schema.)
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name TEXT);
INSERT INTO departments VALUES (1,'Engineering'), (2,'Sales'), (3,'Product');

CREATE TABLE employees (
    employee_id INT PRIMARY KEY, name TEXT, dept_id INT,
    salary INT, manager_id INT
);
INSERT INTO employees VALUES
 (1,'Bob CEO',        1, 92000, NULL),
 (2,'Alice Director', 1, 85000, 1),
 (3,'Jane Manager',   1, 72000, 2),
 (4,'John Doe',       1, 72000, 3),
 (5,'Carol White',    2, 65000, 1),
 (6,'David Green',    2, 62000, 5),
 (7,'Eve Black',      3, 58000, 1);

CREATE TABLE customers (customer_id INT PRIMARY KEY, name TEXT, email TEXT);
-- order_date is set relative to CURRENT_DATE so the "last 30 days"
-- query below stays meaningful whenever you run this.
CREATE TABLE orders (
    order_id INT PRIMARY KEY, customer_id INT,
    order_date DATE, total NUMERIC(10,2)
);

SELECT name, dept_id, salary, manager_id FROM employees ORDER BY employee_id;

Expected Output:

┌────────────────┬─────────┬────────┬────────────┐
│      name      │ dept_id │ salary │ manager_id │
├────────────────┼─────────┼────────┼────────────┤
│ Bob CEO        │       1 │  92000 │            │
│ Alice Director │       1 │  85000 │          1 │
│ Jane Manager   │       1 │  72000 │          2 │
│ John Doe       │       1 │  72000 │          3 │
│ Carol White    │       2 │  65000 │          1 │
│ David Green    │       2 │  62000 │          5 │
│ Eve Black      │       3 │  58000 │          1 │
└────────────────┴─────────┴────────┴────────────┘

Note Jane Manager and John Doe both earn 72000. That tie is deliberate:
it is what makes RANK and DENSE_RANK differ later in the lesson.

Subqueries

A subquery is a query nested inside another query. Use it to break complex problems into smaller, logical steps.

Subquery in WHERE Clause

Find employees who earn more than the average salary.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Expected Output:

┌────────────────┬────────┐
│      name      │ salary │
├────────────────┼────────┤
│ Bob CEO        │  92000 │
│ Alice Director │  85000 │
└────────────────┴────────┘

Subquery in SELECT Clause

Show each employee with the department's average salary.

SELECT
    name,
    salary,
    (
        SELECT AVG(salary)
        FROM employees e2
        WHERE e2.dept_id = e1.dept_id
    ) AS dept_avg
FROM employees e1;

Expected Output:

┌────────────────┬────────┬────────────────────┐
│      name      │ salary │      dept_avg      │
├────────────────┼────────┼────────────────────┤
│ Bob CEO        │  92000 │ 80250.000000000000 │
│ Alice Director │  85000 │ 80250.000000000000 │
│ Jane Manager   │  72000 │ 80250.000000000000 │
│ John Doe       │  72000 │ 80250.000000000000 │
│ Carol White    │  65000 │ 63500.000000000000 │
│ David Green    │  62000 │ 63500.000000000000 │
│ Eve Black      │  58000 │ 58000.000000000000 │
└────────────────┴────────┴────────────────────┘

Subquery in FROM Clause

Find departments with average salary above $75,000.

SELECT dept_name, avg_salary
FROM (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_averages
JOIN departments ON departments.dept_id = dept_averages.dept_id
WHERE avg_salary > 75000;

Expected Output:

┌─────────────┬────────────────────┐
│  dept_name  │     avg_salary     │
├─────────────┼────────────────────┤
│ Engineering │ 80250.000000000000 │
└─────────────┴────────────────────┘

Subquery with IN

Find customers who have placed orders in the last 30 days.

SELECT name, email
FROM customers
WHERE customer_id IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
);

Expected Output:

┌─────────────┬───────────────────┐
│    name     │       email       │
├─────────────┼───────────────────┤
│ Bob Smith   │ bob@example.com   │
│ Carol White │ carol@example.com │
└─────────────┴───────────────────┘

Subquery with EXISTS

Find products that have never been ordered.

SELECT product_name, price
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

Expected Output:

┌────────────────┬────────┐
│  product_name  │ price  │
├────────────────┼────────┤
│ Vintage Camera │ 899.00 │
└────────────────┴────────┘

💡 Tip: Use EXISTS instead of IN when checking for existence, it's often faster because it stops searching once it finds a match.

Common Table Expressions (CTEs)

CTEs (WITH clauses) create temporary named result sets that exist only for the duration of a query. They make complex queries more readable and maintainable.

Basic CTE

Find high-value customers (those who spent over $1,000).

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
)
SELECT c.name, ct.total_spent
FROM customer_totals ct
JOIN customers c ON c.customer_id = ct.customer_id
WHERE ct.total_spent > 1000
ORDER BY ct.total_spent DESC;

Expected Output:

┌─────────────┬─────────────┐
│    name     │ total_spent │
├─────────────┼─────────────┤
│ Bob Smith   │     1850.00 │
│ Carol White │     1320.00 │
└─────────────┴─────────────┘

Multiple CTEs

Compare department averages to company average.

WITH dept_averages AS (
    SELECT
        dept_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
company_average AS (
    SELECT AVG(salary) AS company_avg
    FROM employees
)
SELECT
    d.dept_name,
    da.avg_salary,
    ca.company_avg,
    da.avg_salary - ca.company_avg AS difference
FROM dept_averages da
JOIN departments d ON d.dept_id = da.dept_id
CROSS JOIN company_average ca
ORDER BY difference DESC;

Expected Output:

┌─────────────┬────────────────────┬────────────────────┬─────────────────────┐
│  dept_name  │     avg_salary     │    company_avg     │     difference      │
├─────────────┼────────────────────┼────────────────────┼─────────────────────┤
│ Engineering │ 80250.000000000000 │ 72285.714285714286 │   7964.285714285714 │
│ Sales       │ 63500.000000000000 │ 72285.714285714286 │  -8785.714285714286 │
│ Product     │ 58000.000000000000 │ 72285.714285714286 │ -14285.714285714286 │
└─────────────┴────────────────────┴────────────────────┴─────────────────────┘

Recursive CTE

Find an employee's entire management chain.

WITH RECURSIVE management_chain AS (
    -- Base case: start with the employee
    SELECT
        employee_id,
        name,
        manager_id,
        0 AS level
    FROM employees
    WHERE employee_id = 4     -- John Doe

    UNION ALL

    -- Recursive case: find their manager
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        mc.level + 1
    FROM employees e
    JOIN management_chain mc ON e.employee_id = mc.manager_id
)
SELECT level, name
FROM management_chain
ORDER BY level;

Expected Output:

┌───────┬────────────────┐
│ level │      name      │
├───────┼────────────────┤
│     0 │ John Doe       │
│     1 │ Jane Manager   │
│     2 │ Alice Director │
│     3 │ Bob CEO        │
└───────┴────────────────┘

💡 CTE vs Subquery: Use CTEs when you need to reference the same result set multiple times, or when readability matters. Use subqueries for simple, one-time calculations.

Window Functions

Window functions perform calculations across rows that are related to the current row, without collapsing them into groups. Think of them as "looking through a window" at nearby rows.

ROW_NUMBER()

Assign a unique sequential number to each row.

SELECT
    name,
    salary,
    -- Jane and John both earn 72000. With ORDER BY salary DESC alone,
    -- which of them gets 3 and which gets 4 is arbitrary and can change
    -- between runs. Add a tiebreaker to make numbering reproducible.
    ROW_NUMBER() OVER (ORDER BY salary DESC, name) AS row_num
FROM employees
ORDER BY row_num;

Expected Output:

┌────────────────┬────────┬─────────┐
│      name      │ salary │ row_num │
├────────────────┼────────┼─────────┤
│ Bob CEO        │  92000 │       1 │
│ Alice Director │  85000 │       2 │
│ Jane Manager   │  72000 │       3 │
│ John Doe       │  72000 │       4 │
│ Carol White    │  65000 │       5 │
│ David Green    │  62000 │       6 │
│ Eve Black      │  58000 │       7 │
└────────────────┴────────┴─────────┘

RANK() and DENSE_RANK()

Rank rows with ties. RANK skips numbers after ties, DENSE_RANK doesn't.

SELECT
    name,
    salary,
    -- No tiebreaker here on purpose: we WANT Jane and John to tie.
    RANK()       OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY salary DESC, name;   -- only to make the display stable

Expected Output:

┌────────────────┬────────┬──────┬────────────┐
│      name      │ salary │ rank │ dense_rank │
├────────────────┼────────┼──────┼────────────┤
│ Bob CEO        │  92000 │    1 │          1 │
│ Alice Director │  85000 │    2 │          2 │
│ Jane Manager   │  72000 │    3 │          3 │
│ John Doe       │  72000 │    3 │          3 │
│ Carol White    │  65000 │    5 │          4 │
│ David Green    │  62000 │    6 │          5 │
│ Eve Black      │  58000 │    7 │          6 │
└────────────────┴────────┴──────┴────────────┘

PARTITION BY

Rank employees within each department separately.

SELECT
    dept_name,
    name,
    salary,
    RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dept_rank
FROM employees
JOIN departments USING (dept_id)
ORDER BY dept_name, salary DESC, name;

Expected Output:

┌─────────────┬────────────────┬────────┬───────────┐
│  dept_name  │      name      │ salary │ dept_rank │
├─────────────┼────────────────┼────────┼───────────┤
│ Engineering │ Bob CEO        │  92000 │         1 │
│ Engineering │ Alice Director │  85000 │         2 │
│ Engineering │ Jane Manager   │  72000 │         3 │
│ Engineering │ John Doe       │  72000 │         3 │
│ Product     │ Eve Black      │  58000 │         1 │
│ Sales       │ Carol White    │  65000 │         1 │
│ Sales       │ David Green    │  62000 │         2 │
└─────────────┴────────────────┴────────┴───────────┘

Running Totals with SUM()

Calculate cumulative sales over time.

SELECT
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

Expected Output:

┌────────────┬────────┬───────────────┐
│ order_date │ total  │ running_total │
├────────────┼────────┼───────────────┤
│ 2026-05-19 │  80.00 │         80.00 │
│ 2026-05-29 │ 620.00 │        700.00 │
│ 2026-06-18 │ 100.00 │        800.00 │
│ 2026-06-19 │ 150.00 │        950.00 │
│ 2026-06-20 │ 200.00 │       1150.00 │
│ 2026-06-21 │ 175.00 │       1325.00 │
│ 2026-07-08 │ 900.00 │       2225.00 │
│ 2026-07-13 │ 700.00 │       2925.00 │
│ 2026-07-18 │ 950.00 │       3875.00 │
└────────────┴────────┴───────────────┘

Moving Average

Calculate 3-day moving average of sales.

SELECT
    order_date,
    total,
    AVG(total) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3day
FROM orders
ORDER BY order_date;

Expected Output:

┌────────────┬────────┬──────────────────────┐
│ order_date │ total  │   moving_avg_3day    │
├────────────┼────────┼──────────────────────┤
│ 2026-05-19 │  80.00 │  80.0000000000000000 │
│ 2026-05-29 │ 620.00 │ 350.0000000000000000 │
│ 2026-06-18 │ 100.00 │ 266.6666666666666667 │
│ 2026-06-19 │ 150.00 │ 290.0000000000000000 │
│ 2026-06-20 │ 200.00 │ 150.0000000000000000 │
│ 2026-06-21 │ 175.00 │ 175.0000000000000000 │
│ 2026-07-08 │ 900.00 │ 425.0000000000000000 │
│ 2026-07-13 │ 700.00 │ 591.6666666666666667 │
│ 2026-07-18 │ 950.00 │ 850.0000000000000000 │
└────────────┴────────┴──────────────────────┘

LAG() and LEAD()

Access previous or next row's value.

SELECT
    order_date,
    total,
    LAG(total) OVER (ORDER BY order_date) AS prev_day,
    total - LAG(total) OVER (ORDER BY order_date) AS change
FROM orders
ORDER BY order_date;

Expected Output:

┌────────────┬────────┬──────────┬─────────┐
│ order_date │ total  │ prev_day │ change  │
├────────────┼────────┼──────────┼─────────┤
│ 2026-05-19 │  80.00 │          │         │
│ 2026-05-29 │ 620.00 │    80.00 │  540.00 │
│ 2026-06-18 │ 100.00 │   620.00 │ -520.00 │
│ 2026-06-19 │ 150.00 │   100.00 │   50.00 │
│ 2026-06-20 │ 200.00 │   150.00 │   50.00 │
│ 2026-06-21 │ 175.00 │   200.00 │  -25.00 │
│ 2026-07-08 │ 900.00 │   175.00 │  725.00 │
│ 2026-07-13 │ 700.00 │   900.00 │ -200.00 │
│ 2026-07-18 │ 950.00 │   700.00 │  250.00 │
└────────────┴────────┴──────────┴─────────┘

NTILE()

Divide rows into N equal groups (quartiles, deciles, etc.).

SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC, name) AS quartile
FROM employees
ORDER BY quartile, salary DESC;

Expected Output:

┌────────────────┬────────┬──────────┐
│      name      │ salary │ quartile │
├────────────────┼────────┼──────────┤
│ Bob CEO        │  92000 │        1 │
│ Alice Director │  85000 │        1 │
│ Jane Manager   │  72000 │        2 │
│ John Doe       │  72000 │        2 │
│ Carol White    │  65000 │        3 │
│ David Green    │  62000 │        3 │
│ Eve Black      │  58000 │        4 │
└────────────────┴────────┴──────────┘

Window Frame Clauses

Frame clauses define which rows the window function considers. They let you control the "window" size.

ROWS

Physical rows relative to current

ROWS BETWEEN 
  2 PRECEDING 
  AND CURRENT ROW

RANGE

Logical range based on values

RANGE BETWEEN 
  UNBOUNDED PRECEDING 
  AND CURRENT ROW

Common Frame Specifications

-- All rows from start to current
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

-- Current and next row
ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING

-- Previous 3, current, and next 3 rows
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING

-- All rows in partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Expected Output:

Frame clauses control which rows are included in calculations

Real-World Analytics Examples

Top N per Category

Find the top 3 selling products in each category.

WITH ranked_products AS (
    SELECT
        category_name,
        product_name,
        total_sales,
        ROW_NUMBER() OVER (
            PARTITION BY category_id 
            ORDER BY total_sales DESC
        ) AS rank
    FROM products
    JOIN categories USING (category_id)
)
SELECT category_name, product_name, total_sales
FROM ranked_products
WHERE rank <= 3
ORDER BY category_name, rank;

Expected Output:

┌───────────────┬────────────────┬─────────────┐
│ category_name │  product_name  │ total_sales │
├───────────────┼────────────────┼─────────────┤
│ Electronics   │ Laptop Pro     │      125000 │
│ Electronics   │ Wireless Mouse │       45000 │
│ Electronics   │ USB-C Cable    │       23000 │
│ Furniture     │ Office Chair   │       89000 │
│ Furniture     │ Standing Desk  │       67000 │
│ Furniture     │ Monitor Arm    │       34000 │
└───────────────┴────────────────┴─────────────┘

Year-over-Year Growth

Compare monthly sales to the same month last year.

SELECT
    month,
    year,
    monthly_sales,
    LAG(monthly_sales, 12) OVER w AS prev_year_sales,
    ROUND(
      -- The ::numeric cast is doing real work here. Without it both
      -- operands are INTEGER, so the division truncates to 0 BEFORE
      -- the * 100 and every row reports 0.0% growth. No error, just
      -- a silently wrong report.
      (monthly_sales - LAG(monthly_sales, 12) OVER w)::numeric
      / LAG(monthly_sales, 12) OVER w * 100
    , 1) AS yoy_growth_pct
FROM monthly_sales_summary
WINDOW w AS (ORDER BY year, month)
ORDER BY year, month;

Expected Output:

┌───────┬──────┬───────────────┬─────────────────┬────────────────┐
│ month │ year │ monthly_sales │ prev_year_sales │ yoy_growth_pct │
├───────┼──────┼───────────────┼─────────────────┼────────────────┤
│     1 │ 2023 │         50000 │                 │                │
│     2 │ 2023 │         52000 │                 │                │
│     3 │ 2023 │         54000 │                 │                │
│     4 │ 2023 │         51000 │                 │                │
│     5 │ 2023 │         55000 │                 │                │
│     6 │ 2023 │         58000 │                 │                │
│     7 │ 2023 │         60000 │                 │                │
│     8 │ 2023 │         59000 │                 │                │
│     9 │ 2023 │         57000 │                 │                │
│    10 │ 2023 │         61000 │                 │                │
│    11 │ 2023 │         68000 │                 │                │
│    12 │ 2023 │         75000 │                 │                │
│     1 │ 2024 │         58000 │           50000 │           16.0 │
│     2 │ 2024 │         62000 │           52000 │           19.2 │
│     3 │ 2024 │         57000 │           54000 │            5.6 │
│     4 │ 2024 │         60000 │           51000 │           17.6 │
│     5 │ 2024 │         64000 │           55000 │           16.4 │
│     6 │ 2024 │         66000 │           58000 │           13.8 │
│     7 │ 2024 │         71000 │           60000 │           18.3 │
│     8 │ 2024 │         69000 │           59000 │           16.9 │
│     9 │ 2024 │         65000 │           57000 │           14.0 │
│    10 │ 2024 │         72000 │           61000 │           18.0 │
│    11 │ 2024 │         80000 │           68000 │           17.6 │
│    12 │ 2024 │         91000 │           75000 │           21.3 │
└───────┴──────┴───────────────┴─────────────────┴────────────────┘

Customer Lifetime Value Segments

Segment customers by their total spending.

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(total) AS lifetime_value,
        NTILE(3) OVER (ORDER BY SUM(total) DESC) AS value_segment
    FROM orders
    GROUP BY customer_id
)
SELECT CASE
    value_segment
        WHEN 1 THEN 'High Value'
        WHEN 2 THEN 'Medium Value'
        WHEN 3 THEN 'Low Value'
    END AS segment,
    COUNT(*) AS customer_count,
    ROUND(AVG(lifetime_value), 2) AS avg_ltv,
    ROUND(MIN(lifetime_value), 2) AS min_ltv,
    ROUND(MAX(lifetime_value), 2) AS max_ltv
FROM customer_spending
GROUP BY value_segment
ORDER BY value_segment;

Expected Output:

┌──────────────┬────────────────┬─────────┬─────────┬─────────┐
│   segment    │ customer_count │ avg_ltv │ min_ltv │ max_ltv │
├──────────────┼────────────────┼─────────┼─────────┼─────────┤
│ High Value   │              2 │ 1585.00 │ 1320.00 │ 1850.00 │
│ Medium Value │              1 │  625.00 │  625.00 │  625.00 │
│ Low Value    │              1 │   80.00 │   80.00 │   80.00 │
└──────────────┴────────────────┴─────────┴─────────┴─────────┘

Cohort Analysis

Analyze customer retention by signup cohort.

WITH first_purchase AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY customer_id
),
cohort_activity AS (
    SELECT
        fp.cohort_month,
        DATE_TRUNC('month', o.order_date) AS activity_month,
        COUNT(DISTINCT o.customer_id)     AS active_customers
    FROM first_purchase fp
    JOIN orders o ON o.customer_id = fp.customer_id
    GROUP BY fp.cohort_month, DATE_TRUNC('month', o.order_date)
)
SELECT
    cohort_month,
    activity_month,
    active_customers,
    FIRST_VALUE(active_customers) OVER (
        PARTITION BY cohort_month 
        ORDER BY activity_month
    ) AS cohort_size,
    ROUND(
        active_customers::NUMERIC / 
        FIRST_VALUE(active_customers) OVER (
            PARTITION BY cohort_month 
            ORDER BY activity_month
        ) * 100,
      1
    ) AS retention_pct
FROM cohort_activity
ORDER BY cohort_month, activity_month;

Expected Output:

┌────────────────────────┬────────────────────────┬──────────────────┬─────────────┬───────────────┐
│      cohort_month      │     activity_month     │ active_customers │ cohort_size │ retention_pct │
├────────────────────────┼────────────────────────┼──────────────────┼─────────────┼───────────────┤
│ 2026-05-01 00:00:00+00 │ 2026-05-01 00:00:00+00 │                2 │           2 │         100.0 │
│ 2026-05-01 00:00:00+00 │ 2026-07-01 00:00:00+00 │                1 │           2 │          50.0 │
│ 2026-06-01 00:00:00+00 │ 2026-06-01 00:00:00+00 │                1 │           1 │         100.0 │
│ 2026-07-01 00:00:00+00 │ 2026-07-01 00:00:00+00 │                1 │           1 │         100.0 │
└────────────────────────┴────────────────────────┴──────────────────┴─────────────┴───────────────┘

Performance Tips

✅ Index Key Columns

Add indexes on columns used in subquery WHERE clauses and window function ORDER BY clauses for better performance.

✅ Limit Subquery Results

Filter early in subqueries to reduce the amount of data being processed. Don't fetch everything then filter.

✅ Use CTEs for Readability

CTEs make complex queries maintainable and are often optimized as well as equivalent subqueries by modern databases.

✅ Minimize Window Frames

Smaller window frames (e.g., 3 PRECEDING instead of UNBOUNDED PRECEDING) perform better for large datasets.

❌ Avoid Correlated Subqueries

Subqueries that reference outer query columns run once per row and can be very slow. Use JOINs or window functions instead.

❌ Don't Nest Too Deeply

Multiple levels of nested subqueries become hard to read and debug. Break them into CTEs instead.

Common Query Patterns

Pattern 1: Find Duplicates

SELECT email, COUNT(*) AS duplicate_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Expected Output:

┌───────────────────┬─────────────────┐
│       email       │ duplicate_count │
├───────────────────┼─────────────────┤
│ alice@example.com │               3 │
│ bob@example.com   │               2 │
└───────────────────┴─────────────────┘

Pattern 2: Running Difference

SELECT
    date,
    inventory_count,
    inventory_count - LAG(inventory_count) OVER (ORDER BY date) AS daily_change
FROM inventory_snapshots
ORDER BY date;

Expected Output:

┌────────────┬─────────────────┬──────────────┐
│    date    │ inventory_count │ daily_change │
├────────────┼─────────────────┼──────────────┤
│ 2024-01-01 │             500 │              │
│ 2024-01-02 │             480 │          -20 │
│ 2024-01-03 │             495 │           15 │
│ 2024-01-04 │             450 │          -45 │
│ 2024-01-05 │             430 │          -20 │
└────────────┴─────────────────┴──────────────┘

Pattern 3: Percent of Total

SELECT
    product_name,
    sales,
    ROUND(
            sales * 100.0 / SUM(sales) OVER (),
            2
    ) AS pct_of_total
FROM product_sales
ORDER BY sales DESC;

Expected Output:

┌────────────────┬────────┬──────────────┐
│  product_name  │ sales  │ pct_of_total │
├────────────────┼────────┼──────────────┤
│ Laptop Pro     │ 125000 │        34.72 │
│ Office Chair   │  89000 │        24.72 │
│ Standing Desk  │  67000 │        18.61 │
│ Wireless Mouse │  45000 │        12.50 │
│ Monitor Arm    │  34000 │         9.44 │
└────────────────┴────────┴──────────────┘

Pattern 4: First/Last in Group

WITH ranked_orders AS (
    SELECT
        customer_id,
        order_date,
        total,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id 
            ORDER BY order_date DESC
        ) AS recency_rank
    FROM orders
)
SELECT customer_id, order_date, total
FROM ranked_orders
WHERE recency_rank = 1;

Expected Output:

┌─────────────┬────────────┬────────┐
│ customer_id │ order_date │ total  │
├─────────────┼────────────┼────────┤
│           1 │ 2026-06-21 │ 175.00 │
│           2 │ 2026-07-18 │ 950.00 │
│           3 │ 2026-07-13 │ 700.00 │
│           4 │ 2026-05-19 │  80.00 │
└─────────────┴────────────┴────────┘

When to Use What

Technique	Use When	Example
Subquery in WHERE	Filtering based on aggregated data	Employees above average salary
Subquery in SELECT	Adding calculated column from related data	Show department average with each employee
CTE	Complex queries, multiple references, readability	Multi-step analysis with intermediate results
Recursive CTE	Hierarchical or graph data	Org charts, bill of materials, category trees
ROW_NUMBER()	Unique sequential numbering	Top N per group, pagination
RANK()	Ranking with gaps for ties	Leaderboards, competition rankings
Running Total	Cumulative calculations	Year-to-date sales, cumulative metrics
LAG/LEAD	Compare to previous/next row	Period-over-period growth, changes

Practice Challenge

Challenge: Customer Purchase Analysis

Write a query that shows for each customer:

Customer name and total lifetime value
Number of orders they've placed
Their rank by lifetime value (highest = 1)
Date of their first and most recent order
Average days between orders
Whether they've ordered in the last 30 days (Yes/No)

💡 Click to see solution

WITH order_gaps AS (
    SELECT
        customer_id,
        order_date,
        total,
        order_date - LAG(order_date) OVER (
            PARTITION BY customer_id
            ORDER BY order_date
        ) AS days_since_prev
    FROM orders
),
customer_metrics AS (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(total) AS lifetime_value,
        MIN(order_date) AS first_order,
        MAX(order_date) AS last_order,
        AVG(days_since_prev) AS avg_days_between
    FROM order_gaps
    GROUP BY customer_id
)
SELECT
    c.name,
    cm.lifetime_value,
    cm.order_count,
    RANK() OVER (
        ORDER BY cm.lifetime_value DESC
    ) AS value_rank,
    cm.first_order,
    cm.last_order,
    ROUND(cm.avg_days_between, 1) AS avg_days_between,
    CASE
        WHEN cm.last_order >= CURRENT_DATE - INTERVAL '30 days'
        THEN 'Yes'
        ELSE 'No'
    END AS recent_customer
FROM customer_metrics cm
JOIN customers c ON c.customer_id = cm.customer_id
ORDER BY cm.lifetime_value DESC;

Key Takeaways

Subqueries let you nest queries for complex filtering and calculations
CTEs improve readability and can be recursive for hierarchical data
Window functions analyze rows without grouping them

PARTITION BY creates separate windows for different groups
Frame clauses control which rows are included in calculations
Combine techniques for powerful analytical queries

Back to "Transactions & ACID"

Database FundamentalsLesson 10

Next: "NoSQL Databases"