Analytics Queries · Databases

1. CTE — Common Table Expression

A CTE is a temporary named result set that exists only for the duration of a query.

Syntax

SQL

WITH my_cte AS (
    SELECT ...
)
SELECT *
FROM my_cte;

Backend-dev mental model:

TEXT

CTE = named intermediate query result

Similar to:

const result = someQuery();

and then using result later.

Example

SQL

WITH high_value_orders AS (
    SELECT *
    FROM orders
    WHERE order_value > 1000
)
SELECT *
FROM high_value_orders;

The CTE does not normally create a permanent table.

2. Why Use CTEs?

Main reasons:

Make large SQL queries easier to read
Break complex queries into steps
Avoid deeply nested subqueries
Reuse intermediate query results
Commonly used together with window functions

Without CTE

SQL

SELECT *
FROM (
    SELECT *
    FROM orders
    WHERE order_value > 1000
) x
WHERE customer_id = 10;

With CTE

SQL

WITH high_value_orders AS (
    SELECT *
    FROM orders
    WHERE order_value > 1000
)
SELECT *
FROM high_value_orders
WHERE customer_id = 10;

3. Multiple CTEs

You can create multiple CTEs in sequence.

SQL

WITH
high_value_orders AS (
    SELECT *
    FROM orders
    WHERE order_value > 1000
),
recent_orders AS (
    SELECT *
    FROM high_value_orders
    WHERE created_at >= '2026-01-01'
)
SELECT *
FROM recent_orders;

Think of it as a pipeline:

TEXT

orders
  ↓
high_value_orders
  ↓
recent_orders
  ↓
final query

Window Functions

4. What Is a Window Function?

A window function performs a calculation using other related rows without removing the current row.

This is the most important concept.

TEXT

GROUP BY
→ groups rows
→ collapses rows

WINDOW FUNCTION
→ logically groups rows
→ calculates something
→ keeps every original row

General Syntax

SQL

FUNCTION() OVER (
    PARTITION BY ...
    ORDER BY ...
)

Think of it as:

TEXT

FUNCTION() OVER (
    which rows should I consider?
    in what order?
)

5. `PARTITION BY`

PARTITION BY divides rows into logical groups for the window function.

It is similar to GROUP BY, but it does not collapse rows.

Example:

SQL

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
)

This means:

TEXT

For each customer:
    take their orders
    sort newest → oldest
    assign row numbers

Example data:

TEXT

customer | order | date
---------|-------|------
A        | 1     | Jan 1
A        | 2     | Jan 5
B        | 3     | Jan 3
B        | 4     | Jan 8

Partitions:

TEXT

Customer A:
order 2
order 1

Customer B:
order 4
order 3

Result:

TEXT

A | order 2 | 1
A | order 1 | 2

B | order 4 | 1
B | order 3 | 2

Important mental model:

TEXT

GROUP BY     = group + reduce
PARTITION BY = group + annotate

6. `ROW_NUMBER()`

ROW_NUMBER() gives every row a unique sequential integer.

It always starts from 1 within each partition.

SQL

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
)

Example:

TEXT

value | row_number
------|-----------
300   | 1
300   | 2
200   | 3
100   | 4

Even when values are equal, every row gets a unique number.

Common Use Case: Latest Record Per Customer

SQL

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

Meaning:

TEXT

For every customer:
    newest order → rn = 1

Then:
    return only rn = 1

7. `RANK()`

RANK() gives equal values the same rank.

Example:

TEXT

Values:
300
300
200
100

Result:

TEXT

Rank 2 is skipped because two rows occupied rank 1.

8. `DENSE_RANK()`

DENSE_RANK() also gives equal values the same rank, but does not skip numbers.

TEXT

Comparison

TEXT

Values:       300  300  200  100

ROW_NUMBER:    1    2    3    4
RANK:          1    1    3    4
DENSE_RANK:    1    1    2    3

Use:

TEXT

ROW_NUMBER()
→ when every row needs a unique number

RANK()
→ when ties should have the same rank and gaps are okay

DENSE_RANK()
→ when ties should have the same rank without gaps

9. `SUM()` as a Window Function

Normal aggregation:

SQL

SELECT
    customer_id,
    SUM(amount)
FROM orders
GROUP BY customer_id;

Input:

TEXT

A | 100
A | 200
A | 300

Output:

TEXT

A | 600

The individual rows disappear.

Using a window function:

SQL

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS customer_total
FROM orders;

Result:

TEXT

order | customer | amount | customer_total
------|----------|--------|---------------
1     | A        | 100    | 600
2     | A        | 200    | 600
3     | A        | 300    | 600

Every row remains.

10. `COUNT()` as a Window Function

SQL

COUNT(*) OVER (
    PARTITION BY customer_id
)

Example:

TEXT

order | customer | order_count
------|----------|------------
1     | A        | 3
2     | A        | 3
3     | A        | 3
4     | B        | 2
5     | B        | 2

Useful for:

Orders per customer
Events per session
Transactions per account
Items per order

without losing individual rows.

11. `LAG()`

LAG() lets you access a value from a previous row.

SQL

LAG(amount) OVER (
    ORDER BY created_at
)

Example:

TEXT

amount | previous_amount
-------|----------------
100    | NULL
150    | 100
200    | 150

Useful for:

Previous status
Previous transaction amount
Previous login
Previous sensor reading
Comparing current vs previous values

12. `LAG()` Offset

You can specify how many rows backward to look.

Syntax:

SQL

LAG(column, offset)

Example:

SQL

LAG(amount, 2) OVER (
    ORDER BY created_at
)

means:

TEXT

Give me the amount from 2 rows before.

Example:

TEXT

amount | LAG(amount, 2)
-------|---------------
100    | NULL
200    | NULL
300    | 100
400    | 200

If you don't specify an offset:

SQL

LAG(amount)

it defaults to:

SQL

LAG(amount, 1)

13. `LEAD()`

LEAD() does the opposite of LAG().

It accesses a value from a future row.

SQL

LEAD(amount) OVER (
    ORDER BY created_at
)

Example:

TEXT

amount | next_amount
-------|------------
100    | 150
150    | 200
200    | NULL

14. `LEAD()` Offset

You can specify how many rows forward to look.

SQL

LEAD(amount, 3) OVER (
    ORDER BY created_at
)

means:

TEXT

Give me the amount from 3 rows ahead.

General syntax:

SQL

LAG(value, offset)
LEAD(value, offset)

15. Default Value for `LAG()` and `LEAD()`

You can also specify what should be returned when the requested row doesn't exist.

Syntax:

SQL

LAG(value, offset, default_value)

LEAD(value, offset, default_value)

Example:

SQL

LAG(amount, 2, 0) OVER (
    ORDER BY created_at
)

Instead of returning NULL:

TEXT

So the arguments are:

TEXT

LAG(column, how_far_back, fallback_value)

LEAD(column, how_far_forward, fallback_value)

Important:

The meaning of "previous" and "next" is determined by:

SQL

ORDER BY

inside the OVER(...).

It is not based on physical table order.

16. Running Total

Window functions are useful for cumulative calculations.

SQL

SUM(amount) OVER (
    ORDER BY created_at
)

Given:

TEXT

100
200
50

Result:

TEXT

amount | running_total
-------|--------------
100    | 100
200    | 300
50     | 350

You can combine this with partitions:

SQL

SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
)

Now each customer gets their own running total.

17. `GROUP BY` vs `PARTITION BY`

`GROUP BY`

SQL

SELECT
    customer_id,
    SUM(amount)
FROM orders
GROUP BY customer_id;

Input:

TEXT

A | 100
A | 200
A | 300

Output:

TEXT

A | 600

Three rows became one.

`PARTITION BY`

SQL

SELECT
    customer_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
    ) AS total
FROM orders;

Output:

TEXT

A | 100 | 600
A | 200 | 600
A | 300 | 600

All three rows remain.

Memorize

TEXT

GROUP BY
= group + collapse

PARTITION BY
= group + calculate + preserve rows

18. CTE + Window Function Together

This is one of the most common patterns.

Problem:

Get the highest-value order from every customer.

SQL

WITH ranked_orders AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY order_value DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM ranked_orders
WHERE rn = 1;

Conceptually:

TEXT

Step 1:
orders
   ↓
window function calculates rn

Step 2:
CTE stores that intermediate result

Step 3:
outer query filters WHERE rn = 1

19. Why CTE Is Often Needed with `ROW_NUMBER()`

You generally cannot do:

SQL

SELECT
    *,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY created_at DESC
    ) AS rn
FROM orders
WHERE rn = 1;

because WHERE is evaluated before the SELECT alias/window result is available.

Instead:

SQL

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
)
SELECT *
FROM ranked
WHERE rn = 1;

Think:

TEXT

CTE
 ↓
calculate window function
 ↓
outer query
 ↓
filter calculated result

20. Useful Backend Patterns

Latest Row Per User

SQL

ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY created_at DESC
)

Then:

SQL

WHERE rn = 1

Latest Status Per Order

SQL

ROW_NUMBER() OVER (
    PARTITION BY order_id
    ORDER BY created_at DESC
)

Deduplicate Records

SQL

ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY created_at DESC
)

Keep:

SQL

rn = 1

Top 3 Orders Per Customer

SQL

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY order_value DESC
)

Then:

SQL

WHERE rn <= 3

Previous Status

SQL

LAG(status) OVER (
    PARTITION BY order_id
    ORDER BY created_at
)

Status Two Events Ago

SQL

LAG(status, 2) OVER (
    PARTITION BY order_id
    ORDER BY created_at
)

Next Scheduled Event

SQL

LEAD(created_at) OVER (
    PARTITION BY customer_id
    ORDER BY created_at
)

Total Without Losing Rows

SQL

SUM(amount) OVER (
    PARTITION BY customer_id
)

21. Window Function Structure

When you see:

SQL

ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC
)

read it from inside out:

TEXT

PARTITION BY customer_id
→ divide data by customer

ORDER BY created_at DESC
→ newest first within each customer

ROW_NUMBER()
→ assign 1, 2, 3... to those rows

General mental model:

TEXT

FUNCTION() OVER (
    PARTITION BY grouping
    ORDER BY ordering
)

22. Cheat Sheet

TEXT

CTE
WITH x AS (...)
→ Named intermediate query result

TEXT

OVER(...)
→ Defines the window for a window function

TEXT

PARTITION BY
→ Which rows belong together

TEXT

ORDER BY inside OVER()
→ Order of rows inside the partition

TEXT

ROW_NUMBER()
→ 1, 2, 3, 4
→ always unique

TEXT

RANK()
→ 1, 1, 3, 4
→ ties share rank
→ gaps possible

TEXT

DENSE_RANK()
→ 1, 1, 2, 3
→ ties share rank
→ no gaps

TEXT

LAG(column)
→ previous row

TEXT

LAG(column, 3)
→ 3 rows before

TEXT

LEAD(column)
→ next row

TEXT

LEAD(column, 3)
→ 3 rows ahead

TEXT

LAG(column, 2, 0)
→ 2 rows before
→ return 0 if it doesn't exist

TEXT

SUM() OVER(...)
→ sum while keeping rows

TEXT

COUNT() OVER(...)
→ count while keeping rows

23. Final Mental Models

The most important things to remember:

TEXT

CTE
= name an intermediate query result

TEXT

GROUP BY
= group rows and reduce them

TEXT

PARTITION BY
= logically group rows without removing them

TEXT

Window Function
= calculate something using related rows
  while preserving the current row

TEXT

ROW_NUMBER
= unique position of a row

TEXT

LAG / LEAD
= navigate backward / forward within ordered rows

For backend development, the most useful window-function concepts to master first are:

ROW_NUMBER()
PARTITION BY
SUM() OVER(...)
COUNT() OVER(...)
LAG()
LEAD()
CTE + ROW_NUMBER() together

These cover a large percentage of real-world backend SQL use cases.

1. CTE — Common Table Expression

Syntax

Example

2. Why Use CTEs?

Without CTE

With CTE

3. Multiple CTEs

Window Functions

4. What Is a Window Function?

General Syntax

5. PARTITION BY

6. ROW_NUMBER()

Common Use Case: Latest Record Per Customer

7. RANK()

8. DENSE_RANK()

Comparison

9. SUM() as a Window Function

10. COUNT() as a Window Function

11. LAG()

12. LAG() Offset

13. LEAD()

14. LEAD() Offset

15. Default Value for LAG() and LEAD()

16. Running Total

17. GROUP BY vs PARTITION BY

GROUP BY

PARTITION BY

Memorize

18. CTE + Window Function Together

19. Why CTE Is Often Needed with ROW_NUMBER()

20. Useful Backend Patterns

Latest Row Per User

Latest Status Per Order

Deduplicate Records

Top 3 Orders Per Customer

Previous Status

Status Two Events Ago

Next Scheduled Event

Total Without Losing Rows

21. Window Function Structure

22. Cheat Sheet

23. Final Mental Models

RELATED

5. `PARTITION BY`

6. `ROW_NUMBER()`

7. `RANK()`

8. `DENSE_RANK()`

9. `SUM()` as a Window Function

10. `COUNT()` as a Window Function

11. `LAG()`

12. `LAG()` Offset

13. `LEAD()`

14. `LEAD()` Offset

15. Default Value for `LAG()` and `LEAD()`

17. `GROUP BY` vs `PARTITION BY`

`GROUP BY`

`PARTITION BY`

19. Why CTE Is Often Needed with `ROW_NUMBER()`