← SQL & Databases theorytheory 0/3 · 0%

level 1

Curious · 0 XP

streak

perfect

badges

100 XP to Apprentice

Advanced Queries · hard

3. Window Functions & CTEs

Analytical calculations and recursive queries.

1 min read1 questionsup to 50 XPtopic 3/3

Read in another language

AI translates the explanation and quiz text while preserving code and answer scoring.

Window functions compute across a set of table rows related to the current row without collapsing rows like `GROUP BY`.

sql
SELECT 
    account_id, 
    amount,
    SUM(amount) OVER (PARTITION BY account_id ORDER BY created_at) AS running_balance,
    RANK() OVER (ORDER BY amount DESC) as rank_pos
FROM transactions;

How to study this topic

1. Read the explanation once end to end, skipping the code.
2. Re-read the code snippets and say out loud what each line does.
3. Answer the quiz without scrolling back — that is the real check.
4. Read every explanation, even for the answers you got right.

Scoring

· Up to 30 XP from quiz accuracy.
· +15 XP for a perfect first attempt.
· +5 XP while a 3-day streak is running.
· 75% or higher marks the topic complete.

Check your understanding

1. What distinguishes a Window Function (OVER clause) from a GROUP BY aggregation?
Window functions retain individual row identities while computing rolling/partitioned aggregatesWindow functions cannot calculate sumsWindow functions are for text onlyGROUP BY is faster in all scenarios

Badges

First light

Pass your first topic

Sharp shooter

3 perfect first-try quizzes

On a roll

Study 3 days in a row

Week warrior

Study 7 days in a row

Grinder

Earn 500 XP

Deep diver

Earn 1500 XP