🧠Abfragemuster

-- Inseln
SELECT MIN(nr) AS von, MAX(nr) AS bis, COUNT(*) AS anzahl
  FROM (SELECT nr, nr - ROW_NUMBER() OVER (ORDER BY nr) AS insel
          FROM rechnungen) AS t
 GROUP BY insel
 ORDER BY von;

-- Lücken
SELECT nr + 1 AS luecke_von, naechste - 1 AS luecke_bis
  FROM (SELECT nr, LEAD(nr) OVER (ORDER BY nr) AS naechste FROM rechnungen) AS t
 WHERE naechste > nr + 1;

SELECT kunde_id, bestell_id, datum, betrag
  FROM (SELECT b.*,
               ROW_NUMBER() OVER (PARTITION BY kunde_id ORDER BY betrag DESC, bestell_id) AS nr
          FROM bestellungen b
         WHERE status <> 'storniert') AS t
 WHERE nr <= 2
 ORDER BY kunde_id, betrag DESC;

SELECT bestell_id, datum, betrag,
       SUM(betrag) OVER (ORDER BY datum)                    AS summe_range,
       SUM(betrag) OVER (ORDER BY datum, bestell_id
                         ROWS BETWEEN UNBOUNDED PRECEDING
                                  AND CURRENT ROW)          AS summe_rows,
       SUM(betrag) OVER (PARTITION BY strftime('%Y-%m', datum)
                         ORDER BY datum, bestell_id)        AS summe_im_monat
  FROM bestellungen
 WHERE status <> 'storniert'
 ORDER BY datum, bestell_id;

-- Pivot: Monate als Spalten
SELECT k.name,
       SUM(CASE WHEN strftime('%m', b.datum) = '01' THEN b.betrag END) AS jan,
       SUM(CASE WHEN strftime('%m', b.datum) = '02' THEN b.betrag END) AS feb,
       SUM(b.betrag) FILTER (WHERE strftime('%m', b.datum) = '03')     AS mrz,
       SUM(b.betrag)                                                   AS gesamt
  FROM kunden k
  JOIN bestellungen b ON b.kunde_id = k.kunde_id
 WHERE b.status <> 'storniert'
 GROUP BY k.name
 ORDER BY gesamt DESC;

-- Unpivot: Quartalsspalten → Zeilen
SELECT region, 'Q1' AS quartal, q1 AS wert FROM plan UNION ALL
SELECT region, 'Q2', q2 FROM plan UNION ALL
SELECT region, 'Q3', q3 FROM plan UNION ALL
SELECT region, 'Q4', q4 FROM plan
 ORDER BY region, quartal;

WITH RECURSIVE org (ma_id, name, ebene, pfad) AS (
  SELECT ma_id, name, 0, name
    FROM mitarbeiter
   WHERE chef_id IS NULL                            -- Anker
  UNION ALL
  SELECT m.ma_id, m.name, o.ebene + 1, o.pfad || ' › ' || m.name
    FROM mitarbeiter m
    JOIN org o ON m.chef_id = o.ma_id               -- rekursiver Teil
)
SELECT ebene, substr('            ', 1, ebene * 3) || name AS organigramm, pfad
  FROM org
 ORDER BY pfad;

WITH RECURSIVE aufloesung (komponente, menge, ebene) AS (
  SELECT komponente, menge * 3, 1            -- 3 Regale
    FROM stueckliste WHERE teil = 'Regal'
  UNION ALL
  SELECT s.komponente, a.menge * s.menge, a.ebene + 1
    FROM stueckliste s
    JOIN aufloesung a ON s.teil = a.komponente
)
SELECT komponente, SUM(menge) AS gesamt
  FROM aufloesung
 WHERE komponente NOT IN (SELECT teil FROM stueckliste)   -- nur Blätter
 GROUP BY komponente
 ORDER BY komponente;

WITH RECURSIVE tage (tag) AS (
  SELECT DATE('2026-03-01')
  UNION ALL
  SELECT DATE(tag, '+1 day') FROM tage WHERE tag < '2026-03-10'
)
SELECT t.tag,
       COUNT(b.bestell_id)          AS bestellungen,
       COALESCE(SUM(b.betrag), 0)   AS umsatz
  FROM tage t
  LEFT JOIN bestellungen b ON b.datum = t.tag AND b.status <> 'storniert'
 GROUP BY t.tag
 ORDER BY t.tag;

-- Falle: liefert 0 Zeilen
SELECT name, email FROM kunden
 WHERE email NOT IN (SELECT email FROM sperrliste);

-- richtig: NOT EXISTS
SELECT k.name, k.email FROM kunden k
 WHERE NOT EXISTS (SELECT 1 FROM sperrliste s WHERE s.email = k.email)
 ORDER BY k.kunde_id;

SELECT name, klicks, kaeufe,
       ROUND(100.0 * kaeufe / NULLIF(klicks, 0), 1)              AS quote_prozent,
       COALESCE(ROUND(100.0 * kaeufe / NULLIF(klicks, 0), 1), 0) AS quote_oder_0
  FROM kampagnen
 ORDER BY klicks DESC;

SELECT name, COALESCE(email, '(keine)') AS email FROM kunden ORDER BY kunde_id;

SELECT
  (SELECT COUNT(*) FROM logins
    WHERE zeit BETWEEN '2026-02-01' AND '2026-02-28')           AS between_falsch,
  (SELECT COUNT(*) FROM logins
    WHERE zeit >= '2026-02-01' AND zeit < '2026-03-01')          AS halboffen_richtig,
  (SELECT COUNT(*) FROM logins
    WHERE strftime('%Y-%m', zeit) = '2026-02')                   AS funktion_richtig_aber_langsam;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM logins WHERE zeit >= '2026-02-01' AND zeit < '2026-03-01';