Geavanceerde SQL-queries voor data-analyse: window functions, CTE’s en optimalisatie

Inleiding

SQL is al lang niet meer alleen een taal om rijen op te halen uit een tabel. In moderne data-analyse gebruik je SQL om complexe berekeningen te doen, trends te detecteren, cohortanalyses te bouwen, data te schonen, en prestaties te optimaliseren zodat dashboards en pipelines betrouwbaar en snel blijven.

In deze tutorial leer je drie pijlers die in de praktijk vaak samenkomen:

Window functions: analytische functies die berekeningen uitvoeren over een “venster” van rijen zonder je resultaatset te reduceren.
CTE’s (Common Table Expressions): leesbare, modulaire query-opbouw, inclusief recursie.
Optimalisatie: indexen, join-strategieën, filter-pushdown, query-plannen, en praktische valkuilen.

De voorbeelden zijn geschreven in generieke SQL met een lichte nadruk op PostgreSQL-syntax waar dat nuttig is. De concepten zijn breed toepasbaar.

Voorbeeldschema en testdata

We gebruiken een klein e-commerce schema: klanten, bestellingen en orderregels. Je kunt dit rechtstreeks uitvoeren in een PostgreSQL-achtige omgeving.

CREATE TABLE klanten (
  klant_id      BIGINT PRIMARY KEY,
  naam          TEXT NOT NULL,
  segment       TEXT NOT NULL,
  aangemaakt_op DATE NOT NULL
);

CREATE TABLE bestellingen (
  bestelling_id BIGINT PRIMARY KEY,
  klant_id      BIGINT NOT NULL REFERENCES klanten(klant_id),
  besteld_op    TIMESTAMP NOT NULL,
  status        TEXT NOT NULL,
  totaal_bedrag NUMERIC(12,2) NOT NULL
);

CREATE TABLE orderregels (
  bestelling_id BIGINT NOT NULL REFERENCES bestellingen(bestelling_id),
  regel_nr      INT NOT NULL,
  product_id    BIGINT NOT NULL,
  categorie     TEXT NOT NULL,
  aantal        INT NOT NULL,
  prijs         NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (bestelling_id, regel_nr)
);

Indexen die in de praktijk vaak zinvol zijn:

CREATE INDEX idx_bestellingen_klant_datum
  ON bestellingen (klant_id, besteld_op);

CREATE INDEX idx_bestellingen_datum
  ON bestellingen (besteld_op);

CREATE INDEX idx_orderregels_bestelling
  ON orderregels (bestelling_id);

CREATE INDEX idx_orderregels_categorie
  ON orderregels (categorie);

Deel 1: Window functions (analytische functies)

Wat is een window function?

Een window function berekent een waarde per rij, maar gebruikt daarbij een set rijen (het “venster”) die je definieert met OVER (...). In tegenstelling tot een GROUP BY behoud je de detailrijen.

Belangrijke bouwstenen:

PARTITION BY: verdeelt de data in groepen (zoals “per klant”).
ORDER BY binnen OVER: bepaalt volgorde voor rangschikking of cumulaties.
ROWS BETWEEN ... / RANGE BETWEEN ...: definieert het frame (welke rijen tellen mee).

Rangschikken: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Use-case: “Geef per klant de meest recente bestelling.”

SELECT *
FROM (
  SELECT
    b.*,
    ROW_NUMBER() OVER (
      PARTITION BY b.klant_id
      ORDER BY b.besteld_op DESC
    ) AS rn
  FROM bestellingen b
  WHERE b.status = 'betaald'
) x
WHERE x.rn = 1;

Verschillen:

ROW_NUMBER(): altijd uniek oplopend, ook bij gelijke waarden.
RANK(): gelijke waarden krijgen dezelfde rank, maar er ontstaan “gaten”.
DENSE_RANK(): gelijke waarden dezelfde rank, zonder gaten.

Voorbeeld: top-3 bestellingen per klant op basis van bedrag (met ties):

SELECT *
FROM (
  SELECT
    b.klant_id,
    b.bestelling_id,
    b.totaal_bedrag,
    DENSE_RANK() OVER (
      PARTITION BY b.klant_id
      ORDER BY b.totaal_bedrag DESC
    ) AS dr
  FROM bestellingen b
  WHERE b.status = 'betaald'
) t
WHERE t.dr <= 3
ORDER BY t.klant_id, t.dr, t.totaal_bedrag DESC;

Aggregaties zonder `GROUP BY`: `SUM() OVER`, `AVG() OVER`, `COUNT() OVER`

Use-case: toon per bestelling ook het totaal aantal bestellingen van die klant.

SELECT
  b.bestelling_id,
  b.klant_id,
  b.besteld_op,
  b.totaal_bedrag,
  COUNT(*) OVER (PARTITION BY b.klant_id) AS aantal_bestellingen_klant
FROM bestellingen b
WHERE b.status = 'betaald';

Belangrijk: dit is geen GROUP BY; je behoudt elke bestelling als rij.

Cumulatieve omzet: running total

Cumulatieve som per dag:

SELECT
  DATE_TRUNC('day', b.besteld_op) AS dag,
  SUM(b.totaal_bedrag) AS omzet_dag,
  SUM(SUM(b.totaal_bedrag)) OVER (
    ORDER BY DATE_TRUNC('day', b.besteld_op)
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS omzet_cumulatief
FROM bestellingen b
WHERE b.status = 'betaald'
GROUP BY 1
ORDER BY 1;

Waarom staat er SUM(SUM(...)) OVER?
Omdat je eerst per dag aggregeert (SUM met GROUP BY), en daarna over die dagresultaten een window-som berekent.

ROWS vs RANGE:

ROWS telt fysieke rijen in de resultaatset.
RANGE groepeert op gelijke sorteersleutels (kan verrassend zijn bij duplicaten).

Voor tijdreeksen is ROWS vaak voorspelbaarder.

Vergelijken met vorige/volgende rij: `LAG` en `LEAD`

Use-case: omzetverschil t.o.v. vorige bestelling per klant.

SELECT
  b.klant_id,
  b.bestelling_id,
  b.besteld_op,
  b.totaal_bedrag,
  LAG(b.totaal_bedrag) OVER (
    PARTITION BY b.klant_id
    ORDER BY b.besteld_op
  ) AS vorig_bedrag,
  b.totaal_bedrag
    - LAG(b.totaal_bedrag) OVER (
        PARTITION BY b.klant_id
        ORDER BY b.besteld_op
      ) AS delta_tov_vorige
FROM bestellingen b
WHERE b.status = 'betaald'
ORDER BY b.klant_id, b.besteld_op;

Praktische tip: als je LAG(...) twee keer gebruikt, kun je dit leesbaarder maken met een CTE of subquery zodat je het één keer berekent.

Met defaultwaarde:

LAG(b.totaal_bedrag, 1, 0) OVER (...)

Percentielen en mediaan: `PERCENTILE_CONT`

Use-case: mediaan orderwaarde per segment.

SELECT
  k.segment,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY b.totaal_bedrag) AS mediaan_orderwaarde
FROM bestellingen b
JOIN klanten k ON k.klant_id = b.klant_id
WHERE b.status = 'betaald'
GROUP BY k.segment
ORDER BY k.segment;

PERCENTILE_CONT is een geavanceerde aggregatiefunctie (geen window function in deze vorm), maar zeer nuttig voor analyse. Sommige systemen hebben alternatieven; controleer je dialect.

Window frames: moving average (voortschrijdend gemiddelde)

Use-case: 7-daags voortschrijdend gemiddelde van dagomzet.

WITH dagomzet AS (
  SELECT
    DATE_TRUNC('day', besteld_op) AS dag,
    SUM(totaal_bedrag) AS omzet_dag
  FROM bestellingen
  WHERE status = 'betaald'
  GROUP BY 1
)
SELECT
  dag,
  omzet_dag,
  AVG(omzet_dag) OVER (
    ORDER BY dag
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS omzet_7d_gem
FROM dagomzet
ORDER BY dag;

Waarom 6 PRECEDING?
Omdat je de huidige dag plus 6 dagen ervoor = 7 rijen. Let op: dit is rij-gebaseerd. Als er dagen ontbreken, is het niet exact “7 kalenderdagen”. Dan moet je eerst een kalender-tabel gebruiken of ontbrekende dagen opvullen.

Veelgemaakte fouten bij window functions

Vergeten PARTITION BY: je berekent dan over de volledige dataset.
Onbedoelde sortering: ORDER BY in de query is niet hetzelfde als ORDER BY in OVER.
Frame-verwarring: standaardframe kan dialect-afhankelijk zijn. Wees expliciet bij cumulaties.
Dubbele berekeningen: herhaal LAG/SUM OVER niet onnodig; gebruik CTE’s.

Deel 2: CTE’s (Common Table Expressions)

Waarom CTE’s?

CTE’s maken complexe queries:

leesbaar (stap-voor-stap),
herbruikbaar (logische blokken),
testbaar (je kunt elke stap apart draaien),
geschikt voor recursie.

Basisvorm:

WITH stap1 AS (...),
     stap2 AS (...)
SELECT ...
FROM stap2;

Voorbeeld: omzet per klant met cumulatieve omzet en segment

We bouwen dit modulair op.

WITH betaalde_bestellingen AS (
  SELECT
    bestelling_id,
    klant_id,
    besteld_op,
    totaal_bedrag
  FROM bestellingen
  WHERE status = 'betaald'
),
verrijking AS (
  SELECT
    b.*,
    k.segment
  FROM betaalde_bestellingen b
  JOIN klanten k ON k.klant_id = b.klant_id
),
met_cumulatief AS (
  SELECT
    v.*,
    SUM(v.totaal_bedrag) OVER (
      PARTITION BY v.klant_id
      ORDER BY v.besteld_op
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulatief_klant
  FROM verrijking v
)
SELECT
  klant_id,
  segment,
  bestelling_id,
  besteld_op,
  totaal_bedrag,
  cumulatief_klant
FROM met_cumulatief
ORDER BY klant_id, besteld_op;

Waarom deze opbouw helpt:

In betaalde_bestellingen filter je vroeg (minder data).
In verrijking doe je de join pas nadat je gefilterd hebt.
In met_cumulatief voeg je de window-berekening toe op de juiste granulariteit.

CTE’s en materialisatie: prestatie-implicaties

Afhankelijk van je database kan een CTE:

worden geïnlined (alsof het een subquery is), of
gematerialiseerd worden (tijdelijk resultaat, soms trager maar soms nuttig).

In PostgreSQL kun je (in recente versies) sturen met:

WITH verrijking AS MATERIALIZED (
  SELECT ...
)
SELECT ...

of:

WITH verrijking AS NOT MATERIALIZED (
  SELECT ...
)
SELECT ...

Wanneer materialisatie nuttig kan zijn:

Als een CTE meerdere keren wordt gebruikt in dezelfde query.
Als het resultaat klein is en je dure joins/filters maar één keer wilt uitvoeren.

Wanneer het nadelig kan zijn:

Als de optimizer filters niet meer “naar beneden” kan duwen (minder filter-pushdown).

Praktische aanpak: meet met EXPLAIN (ANALYZE, BUFFERS).

Recursieve CTE: hiërarchieën (categorieboom, organisatiestructuur)

Stel je hebt een tabel met categorieën:

CREATE TABLE categorieen (
  categorie_id BIGINT PRIMARY KEY,
  parent_id    BIGINT NULL REFERENCES categorieen(categorie_id),
  naam         TEXT NOT NULL
);

Je wilt alle subcategorieën van een startcategorie ophalen:

WITH RECURSIVE boom AS (
  SELECT
    c.categorie_id,
    c.parent_id,
    c.naam,
    0 AS diepte,
    CAST(c.categorie_id AS TEXT) AS pad
  FROM categorieen c
  WHERE c.categorie_id = 10

  UNION ALL

  SELECT
    c.categorie_id,
    c.parent_id,
    c.naam,
    b.diepte + 1 AS diepte,
    b.pad || '>' || c.categorie_id AS pad
  FROM categorieen c
  JOIN boom b ON c.parent_id = b.categorie_id
)
SELECT *
FROM boom
ORDER BY pad;

Belangrijke punten:

WITH RECURSIVE heeft een anker (eerste SELECT) en een recursief deel (tweede SELECT).
Gebruik UNION ALL voor prestaties, tenzij je expliciet duplicaten wilt verwijderen.
Denk aan bescherming tegen cycli. In sommige systemen kun je een “visited set” bijhouden via pad.

Deel 3: Combineren van window functions en CTE’s voor echte analyses

Cohortanalyse: retentie per maand van eerste aankoop

Doel: groepeer klanten op basis van maand van eerste aankoop en meet in welke maanden ze terugkomen.

Stap-voor-stap:

Bepaal eerste aankoopmaand per klant.
Koppel elke bestelling aan cohort.
Bereken “maand-offset” t.o.v. cohort.
Tel unieke klanten per cohort en offset.

WITH betaalde AS (
  SELECT
    klant_id,
    DATE_TRUNC('month', besteld_op) AS maand
  FROM bestellingen
  WHERE status = 'betaald'
),
eerste AS (
  SELECT
    klant_id,
    MIN(maand) AS cohort_maand
  FROM betaalde
  GROUP BY klant_id
),
cohort_events AS (
  SELECT
    b.klant_id,
    e.cohort_maand,
    b.maand AS event_maand,
    (EXTRACT(YEAR FROM b.maand) - EXTRACT(YEAR FROM e.cohort_maand)) * 12
      + (EXTRACT(MONTH FROM b.maand) - EXTRACT(MONTH FROM e.cohort_maand)) AS maand_offset
  FROM betaalde b
  JOIN eerste e ON e.klant_id = b.klant_id
),
retentie AS (
  SELECT
    cohort_maand,
    maand_offset,
    COUNT(DISTINCT klant_id) AS klanten
  FROM cohort_events
  GROUP BY cohort_maand, maand_offset
),
cohort_grootte AS (
  SELECT
    cohort_maand,
    MAX(klanten) FILTER (WHERE maand_offset = 0) AS cohort_klanten
  FROM retentie
  GROUP BY cohort_maand
)
SELECT
  r.cohort_maand,
  r.maand_offset,
  r.klanten,
  cg.cohort_klanten,
  (r.klanten::NUMERIC / NULLIF(cg.cohort_klanten, 0)) AS retentie_ratio
FROM retentie r
JOIN cohort_grootte cg USING (cohort_maand)
ORDER BY r.cohort_maand, r.maand_offset;

Uitleg:

maand_offset = 0 is de cohortmaand zelf.
FILTER (WHERE ...) is een nette manier om conditioneel te aggregeren.
NULLIF voorkomt deling door nul.

Optimalisatietip:

Zorg voor index op (status, besteld_op) of op besteld_op plus een selectieve status-filter, afhankelijk van je data.

“Top-N per groep” met tie-breakers (klassieke analysevraag)

Vraag: “Geef per categorie de top 5 producten op omzet, met stabiele tie-breaker.”

We berekenen eerst omzet per product per categorie, dan rangschikken we.

WITH omzet_per_product AS (
  SELECT
    o.categorie,
    o.product_id,
    SUM(o.aantal * o.prijs) AS omzet
  FROM orderregels o
  JOIN bestellingen b ON b.bestelling_id = o.bestelling_id
  WHERE b.status = 'betaald'
  GROUP BY o.categorie, o.product_id
),
gerankt AS (
  SELECT
    opp.*,
    ROW_NUMBER() OVER (
      PARTITION BY opp.categorie
      ORDER BY opp.omzet DESC, opp.product_id ASC
    ) AS rn
  FROM omzet_per_product opp
)
SELECT
  categorie,
  product_id,
  omzet
FROM gerankt
WHERE rn <= 5
ORDER BY categorie, omzet DESC, product_id;

Waarom ROW_NUMBER en niet RANK?

Met ROW_NUMBER krijg je altijd exact 5 rijen per categorie.
Met RANK kunnen ties ervoor zorgen dat je meer dan 5 rijen krijgt.

Deel 4: Query-optimalisatie in de praktijk

Optimalisatie is context-afhankelijk: datavolume, verdeling, hardware, configuratie, en database-engine. Toch zijn er patronen die bijna altijd helpen.

1) Filter zo vroeg mogelijk (maar correct)

Slechte aanpak: eerst grote join, daarna filteren.

Betere aanpak: filter in een CTE of subquery vóór de join:

WITH betaalde_bestellingen AS (
  SELECT bestelling_id, klant_id, besteld_op, totaal_bedrag
  FROM bestellingen
  WHERE status = 'betaald'
    AND besteld_op >= NOW() - INTERVAL '90 days'
)
SELECT
  k.segment,
  SUM(b.totaal_bedrag) AS omzet
FROM betaalde_bestellingen b
JOIN klanten k ON k.klant_id = b.klant_id
GROUP BY k.segment;

Let op: “zo vroeg mogelijk” mag de logica niet veranderen. Bijvoorbeeld: bij LEFT JOIN kan een filter in WHERE de join effectief veranderen naar een INNER JOIN.

2) Begrijp `INNER JOIN` vs `LEFT JOIN` en filterplaatsing

Voorbeeld: je wilt alle klanten tonen, ook zonder bestellingen, met omzet 0.

Correct:

SELECT
  k.klant_id,
  k.segment,
  COALESCE(SUM(b.totaal_bedrag), 0) AS omzet
FROM klanten k
LEFT JOIN bestellingen b
  ON b.klant_id = k.klant_id
 AND b.status = 'betaald'
GROUP BY k.klant_id, k.segment;

Als je b.status = 'betaald' in WHERE zet, vallen klanten zonder bestellingen weg.

3) Indexen: kies op basis van filters en joins

Richtlijnen:

Indexeer kolommen die vaak in WHERE, JOIN ON, ORDER BY voorkomen.
Samengestelde indexen werken het best als de linkerprefix overeenkomt met je filter/sort.

Voorbeeld: vaak “per klant, meest recente bestelling”:

CREATE INDEX idx_bestellingen_klant_besteldop_desc
  ON bestellingen (klant_id, besteld_op DESC);

Daarna kan deze query sterk versnellen:

SELECT
  klant_id,
  bestelling_id,
  besteld_op
FROM bestellingen
WHERE klant_id = 123
ORDER BY besteld_op DESC
LIMIT 1;

Let op: te veel indexen maken writes trager. Indexeer gericht.

4) Vermijd functies op geïndexeerde kolommen in filters

Dit kan indexgebruik blokkeren.

Minder goed:

WHERE DATE_TRUNC('day', besteld_op) = DATE '2026-02-01'

Beter (range-filter):

WHERE besteld_op >= TIMESTAMP '2026-02-01 00:00:00'
  AND besteld_op <  TIMESTAMP '2026-02-02 00:00:00'

Dit is vaak een groot verschil op grote tabellen.

5) `EXPLAIN (ANALYZE, BUFFERS)` lezen

In PostgreSQL:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  k.segment,
  SUM(b.totaal_bedrag)
FROM bestellingen b
JOIN klanten k ON k.klant_id = b.klant_id
WHERE b.status = 'betaald'
  AND b.besteld_op >= NOW() - INTERVAL '30 days'
GROUP BY k.segment;

Waar je op let:

Seq Scan: volledige tabelscan; soms oké, vaak duur.
Index Scan / Index Only Scan: gebruikt index; index-only kan snel zijn als de benodigde kolommen in index/visibility map zitten.
Hash Join / Merge Join / Nested Loop: join-strategieën; welke het best is hangt af van volumes en sortering.
Buffers: hoeveel pagina’s uit cache vs disk; veel disk reads betekent vaak I/O-bottleneck.

Optimalisatie is iteratief: verander één ding, meet opnieuw.

6) Vermijd onnodige `SELECT *`

SELECT * haalt vaak meer kolommen op dan nodig, wat:

meer I/O,
meer netwerk,
grotere intermediate resultaten (sort/hash),
minder kans op index-only scans kan betekenen.

Kies expliciet kolommen, zeker in CTE’s die je later nog verwerkt.

7) Aggregaties: let op cardinaliteit en join-volgorde

Als je orderregels koppelt aan bestellingen, kan de rijcount exploderen. Vaak is het slim om eerst te aggregeren op het laagste niveau en pas daarna te joinen.

Voorbeeld: omzet per categorie per maand:

WITH betaalde AS (
  SELECT bestelling_id, DATE_TRUNC('month', besteld_op) AS maand
  FROM bestellingen
  WHERE status = 'betaald'
),
omzet_regels AS (
  SELECT
    o.bestelling_id,
    o.categorie,
    SUM(o.aantal * o.prijs) AS omzet
  FROM orderregels o
  GROUP BY o.bestelling_id, o.categorie
)
SELECT
  b.maand,
  r.categorie,
  SUM(r.omzet) AS omzet
FROM betaalde b
JOIN omzet_regels r ON r.bestelling_id = b.bestelling_id
GROUP BY b.maand, r.categorie
ORDER BY b.maand, r.categorie;

Waarom dit helpt:

Je reduceert orderregels vroeg naar één rij per (bestelling, categorie).
Daarna pas join je met gefilterde bestellingen.

8) Distinct: wees voorzichtig

COUNT(DISTINCT ...) is duur op grote datasets. Soms kun je het vervangen door:

pre-aggregatie,
semantische sleutels,
of window-based deduplicatie.

Deduplicatie met ROW_NUMBER:

WITH gededupliceerd AS (
  SELECT *
  FROM (
    SELECT
      b.*,
      ROW_NUMBER() OVER (
        PARTITION BY b.klant_id, DATE_TRUNC('day', b.besteld_op), b.totaal_bedrag
        ORDER BY b.bestelling_id
      ) AS rn
    FROM bestellingen b
    WHERE b.status = 'betaald'
  ) x
  WHERE x.rn = 1
)
SELECT COUNT(*) FROM gededupliceerd;

Dit is alleen correct als je deduplicatie-regel klopt met je businessdefinitie.

Deel 5: Praktische patronen voor data-analyse

A) Segmentatie met percentielen (klanten in kwintielen op omzet)

WITH omzet_per_klant AS (
  SELECT
    klant_id,
    SUM(totaal_bedrag) AS omzet
  FROM bestellingen
  WHERE status = 'betaald'
  GROUP BY klant_id
),
met_bucket AS (
  SELECT
    o.*,
    NTILE(5) OVER (ORDER BY o.omzet DESC) AS kwintiel
  FROM omzet_per_klant o
)
SELECT
  kwintiel,
  COUNT(*) AS klanten,
  MIN(omzet) AS min_omzet,
  AVG(omzet) AS gem_omzet,
  MAX(omzet) AS max_omzet
FROM met_bucket
GROUP BY kwintiel
ORDER BY kwintiel;

NTILE(n) verdeelt rijen in n ongeveer gelijke groepen. Let op: bij veel gelijke waarden rond grenzen kan interpretatie lastig zijn.

B) Funnel-achtige analyse met window functions (volgorde van events)

Stel je hebt een tabel events(klant_id, event_tijd, event_type) en je wilt de eerste keer dat iemand “checkout” doet na “product_view”.

Een patroon is: rangschik events per klant en gebruik LEAD of self-joins op rijnummers. Conceptueel:

-- Voorbeeldstructuur
CREATE TABLE events (
  klant_id   BIGINT NOT NULL,
  event_tijd TIMESTAMP NOT NULL,
  event_type TEXT NOT NULL
);

WITH gerankt AS (
  SELECT
    e.*,
    ROW_NUMBER() OVER (
      PARTITION BY klant_id
      ORDER BY event_tijd
    ) AS rn
  FROM events e
),
views AS (
  SELECT klant_id, rn, event_tijd
  FROM gerankt
  WHERE event_type = 'product_view'
),
checkouts AS (
  SELECT klant_id, rn, event_tijd
  FROM gerankt
  WHERE event_type = 'checkout'
)
SELECT
  v.klant_id,
  v.event_tijd AS view_tijd,
  MIN(c.event_tijd) AS eerste_checkout_na_view
FROM views v
JOIN checkouts c
  ON c.klant_id = v.klant_id
 AND c.rn > v.rn
GROUP BY v.klant_id, v.event_tijd;

Dit kan zwaar zijn op grote eventtabellen; optimaliseer met:

index op (klant_id, event_tijd),
restrictie op periode,
of een andere modellering (bijvoorbeeld sessies).

Checklist: wanneer gebruik je wat?

Window functions:
- running totals, moving averages
- top-N per groep
- verschillen t.o.v. vorige periode (LAG)
- percentielen/kwantielen (NTILE, percentielfuncties)
CTE’s:
- query’s opdelen in logische stappen
- hergebruik van tussenresultaten
- recursieve hiërarchieën
- complexere analyses zoals cohortretentie
Optimalisatie:
- filter vroeg en correct
- juiste indexen op join- en filterkolommen
- vermijd functies op filterkolommen
- meet met EXPLAIN (ANALYZE, BUFFERS)
- minimaliseer data die door joins/sorts/hashes gaat

Afsluiting

Geavanceerde SQL voor data-analyse draait om twee dingen: expressiviteit (window functions en CTE’s) en efficiëntie (optimalisatie en begrip van query-plannen). Als je deze technieken combineert, kun je analyses bouwen die zowel rijk zijn in inzichten als robuust in productie.

Als je wilt, kan ik op basis van jouw eigen tabellen en vragen:

een cohortanalyse op maat schrijven,
een trage query optimaliseren met concrete index-adviezen,
of een set herbruikbare CTE-patronen opstellen voor jullie datamodel.

Geavanceerde SQL-queries voor data-analyse: window functions, CTE’s en optimalisatie

Geavanceerde SQL-queries voor data-analyse: window functions, CTE’s en optimalisatie

Inleiding

Voorbeeldschema en testdata

Deel 1: Window functions (analytische functies)

Wat is een window function?

Rangschikken: ROW_NUMBER, RANK, DENSE_RANK

Aggregaties zonder GROUP BY: SUM() OVER, AVG() OVER, COUNT() OVER

Cumulatieve omzet: running total

Vergelijken met vorige/volgende rij: LAG en LEAD

Percentielen en mediaan: PERCENTILE_CONT

Window frames: moving average (voortschrijdend gemiddelde)

Veelgemaakte fouten bij window functions

Deel 2: CTE’s (Common Table Expressions)

Waarom CTE’s?

Voorbeeld: omzet per klant met cumulatieve omzet en segment

CTE’s en materialisatie: prestatie-implicaties

Recursieve CTE: hiërarchieën (categorieboom, organisatiestructuur)

Deel 3: Combineren van window functions en CTE’s voor echte analyses

Cohortanalyse: retentie per maand van eerste aankoop

“Top-N per groep” met tie-breakers (klassieke analysevraag)

Deel 4: Query-optimalisatie in de praktijk

1) Filter zo vroeg mogelijk (maar correct)

2) Begrijp INNER JOIN vs LEFT JOIN en filterplaatsing

3) Indexen: kies op basis van filters en joins

4) Vermijd functies op geïndexeerde kolommen in filters

5) EXPLAIN (ANALYZE, BUFFERS) lezen

6) Vermijd onnodige SELECT *

7) Aggregaties: let op cardinaliteit en join-volgorde

8) Distinct: wees voorzichtig

Deel 5: Praktische patronen voor data-analyse

A) Segmentatie met percentielen (klanten in kwintielen op omzet)

B) Funnel-achtige analyse met window functions (volgorde van events)

Checklist: wanneer gebruik je wat?

Afsluiting

Gerelateerde tutorials

Rangschikken: `ROW_NUMBER`, `RANK`, `DENSE_RANK`

Aggregaties zonder `GROUP BY`: `SUM() OVER`, `AVG() OVER`, `COUNT() OVER`

Vergelijken met vorige/volgende rij: `LAG` en `LEAD`

Percentielen en mediaan: `PERCENTILE_CONT`

2) Begrijp `INNER JOIN` vs `LEFT JOIN` en filterplaatsing

5) `EXPLAIN (ANALYZE, BUFFERS)` lezen

6) Vermijd onnodige `SELECT *`