Introduction to databases — Tutorial 4

Advanced SQL

In this tutorial, we will learn two advanced SQL features: common table expressions (CTEs) and window functions. We will use the sales transaction database introduced in the previous SQL tutorial.

1 Common Table Expressions

A common table expression (CTE) defines a temporary result set from a subquery that can be used within the main query. CTEs were introduced in the SQL:1999 standard. They are useful for breaking a complex query into smaller, named building blocks.

The following query returns the properties involved in at least one sale (Vente) or exchange (Echange) transaction, but not both.

The query first uses WITH to define sale_and_exchange as the result of the query following AS. The main query then uses this CTE.

WITH sale_and_exchange  AS (
    SELECT DISTINCT property_id
    FROM sales_transaction
    WHERE transaction_type = 'Vente'
    INTERSECT 
    SELECT DISTINCT property_id
    FROM sales_transaction
    WHERE transaction_type = 'Echange'
)
SELECT DISTINCT property_id
FROM sales_transaction
WHERE transaction_type IN ('Vente', 'Echange')
EXCEPT
SELECT DISTINCT property_id
FROM sale_and_exchange

You can also define multiple CTEs in one WITH clause. The following example defines:

sale, which contains all properties involved in at least one sale transaction;
exchange, which contains all properties involved in at least one exchange transaction.

Write WITH only once, separating the CTE definitions with commas:

WITH sale  AS (
    SELECT DISTINCT property_id
    FROM sales_transaction
    WHERE transaction_type = 'Vente'
),
exchange AS (
    SELECT DISTINCT property_id
    FROM sales_transaction
    WHERE transaction_type = 'Echange'
)
SELECT DISTINCT property_id
FROM sales_transaction
WHERE transaction_type IN ('Vente', 'Echange')
EXCEPT (
SELECT DISTINCT property_id
FROM sale
INTERSECT
SELECT DISTINCT property_id
FROM exchange
)

Use the following exercises to practice building queries from CTEs.

Exercise

Exercise 1.1 Find the 10 cities with the most transactions in December 2025. Use a CTE to make the query easier to read.

Suggested steps:

In a CTE, select the transaction and property identifiers for transactions dated on or after December 1, 2025, and before January 1, 2026.
In the main query, join that CTE to the property, street, and city tables to identify each transaction’s city.
Group by city, count the transactions, then sort by that count in descending order and return the first 10 cities.

Exercise

Exercise 1.2 Write a query that counts, for each region, the transactions whose property value is above that region’s average property value. Use two CTEs.

Suggested steps:

In the first CTE, join transactions through property, street, and city to department and region; keep each transaction’s ID, value, region code, and region name.
In the second CTE, group the first CTE by region code and calculate the average property value for each region.
Join the CTEs on region code, filter to transactions whose value is greater than the regional average, and count them by region.

Exercise

Exercise 1.3 For each department, find the number of main rooms that occurs most often among its properties, and report how many properties have that room count. If there is a tie, return every tied room count. Use two CTEs.

Suggested steps:

Join properties to the department table through street and city.
In the first CTE, group by department and number of main rooms, and count the properties in each group.
In the second CTE, find the largest group count for each department.
Join the CTEs by department and keep the room-count groups whose property count equals that maximum.

Exercise

Exercise 1.4 Modify the previous query so that its selected room count is displayed as one of these labels: 2 rooms, 3 rooms, 4 rooms, or 5 rooms or more. Preserve the department and property count, and keep the previous query’s most-frequent-room-count logic.

Suggested steps:

Keep the two CTEs and the maximum-count filter from the previous query.
In the final SELECT, use CASE to map 2, 3, and 4 rooms to their labels; map all remaining room counts to 5 rooms or more.
Give the CASE expression a clear column alias and retain the property count.

2 Window functions

Window functions, also known as analytic functions, calculate values across related rows (a window), much like GROUP BY. Unlike GROUP BY, a window function does not collapse each group into one row: it returns a value for every input row. This makes window functions useful for running totals, rankings, moving averages, and percentiles.

The following query ranks sales transactions by their value. The RANK() window function returns each row’s rank, leaving gaps when multiple transactions have the same property value. The OVER clause specifies the rows in the window and their order. Here, the window includes every row in sales_transaction, ordered by property value from highest to lowest.

SELECT
    transaction_id,
    property_value,
    RANK() OVER (ORDER BY property_value DESC) AS transaction_rank
FROM sales_transaction
ORDER BY transaction_rank ASC

To rank transactions separately within each transaction type, use PARTITION BY to create a separate window for each type. Each window contains only transactions of that type, so the ranking restarts for every type.

SELECT
    transaction_id,
    property_value,
    transaction_type,
    RANK() OVER (
        PARTITION BY transaction_type
        ORDER BY property_value DESC
    ) AS transaction_rank
FROM sales_transaction
ORDER BY transaction_type, transaction_rank ASC

The general form of a window function is window_function() OVER (...). The OVER clause can include PARTITION BY to create groups and ORDER BY to define row order. Aggregate functions such as AVG, SUM, MIN, and MAX can also be used as window functions. See the PostgreSQL window-function documentation for other available functions.

Exercise

Exercise 2.1 Write a query that computes the running total of transaction values in chronological order. Return the transaction date, transaction value, and running total.

Suggested steps:

Select the transaction date and property value from sales_transaction.
Apply SUM as a window function, ordering the window by transaction date so each row includes values up to that point.
Keep the final result ordered by transaction date. If multiple transactions share a date, consider adding transaction_id as a tie-breaker in both orderings.

Exercise

Exercise 2.2 Modify the previous query so that the running total restarts for each transaction type.

Suggested steps:

Add transaction_type to the selected columns.
Partition the SUM window by transaction type, while keeping its chronological ordering.
Sort the output by transaction type and then transaction date so each running total is easy to inspect.

Exercise

Exercise 2.3 Rank transactions by property value within each calendar month. Return the transaction ID, value, month, and rank.

Suggested steps:

First derive a month number for each transaction; use a CTE if you also want to calculate a readable month name.
In the outer query, apply RANK() ordered by property value from highest to lowest.
Partition the rank window by month number so the ranking restarts each month; sort the final output by month and value.

Exercise

Exercise 2.4 Write a query to compute the median price per square meter for each department. The median is the middle value after prices are sorted; when a department has an even number of transactions, it is the average of the two middle values.

Suggested steps:

Join transactions to properties and their departments, then calculate each transaction’s price per square meter as property_value / built_area.
In a second CTE, use ROW_NUMBER() to number prices from lowest to highest within each department, and use COUNT() as a window function to get the number of prices in that department.
Select the middle row for odd-sized groups, or the two middle rows for even-sized groups. Use AVG to combine the selected value(s) for each department.
Consider how to handle missing or zero built_area values if they occur in the data.