-- Anti-Pattern (PostgreSQL)
CREATE TABLE fct_orders AS
SELECT *
FROM raw.orders
JOIN raw.customers USING (customer_id);

-- Fix: explizite Spaltenliste
CREATE TABLE fct_orders AS
SELECT
    o.order_id,
    o.customer_id,
    o.order_total,
    o.placed_at,
    c.email,
    c.country
FROM raw.orders o
JOIN raw.customers c USING (customer_id);

-- Anti-Pattern (PostgreSQL) — korrelierte Subquery, O(n) Subquery-Executions
SELECT
    customer_id,
    total_spend,
    (SELECT AVG(total_spend) FROM orders) AS avg_spend  -- OK, unkorreliert
    (SELECT MAX(placed_at)                              -- KORRELIERT — läuft pro Zeile
     FROM orders o2
     WHERE o2.customer_id = o1.customer_id) AS last_order_date
FROM orders o1;

-- Fix: Window-Function
SELECT
    customer_id,
    total_spend,
    AVG(total_spend) OVER () AS avg_spend,
    MAX(placed_at) OVER (PARTITION BY customer_id) AS last_order_date
FROM orders;

-- Anti-Pattern (PostgreSQL) — implizite Konvertierung, Index auf status_code nicht genutzt
SELECT * FROM orders WHERE status_code = 1;
-- status_code ist VARCHAR — DB konvertiert 1 zu '1' und scannt die Tabelle

-- Fix: Typen matchen
SELECT * FROM orders WHERE status_code = '1';

-- Anti-Pattern — Date als String, falsche Ergebnisse in manchen Dialekten
SELECT * FROM events WHERE event_date > '2026-01-01';
-- Funktioniert in PostgreSQL, schlägt in manchen MySQL-Configs still fehl

-- Fix: expliziter Cast
SELECT * FROM events WHERE event_date > DATE '2026-01-01';  -- PostgreSQL/BigQuery
-- oder
SELECT * FROM events WHERE event_date > CAST('2026-01-01' AS DATE);  -- universell

-- Anti-Pattern (PostgreSQL) — 0 Zeilen, wenn cancelled_orders ein NULL enthält
SELECT order_id
FROM orders
WHERE order_id NOT IN (SELECT order_id FROM cancelled_orders);

-- Wenn cancelled_orders auch nur ein NULL hat, liefert das nichts.
-- SQL-Logic: NOT IN expandiert zu AND order_id != val1 AND order_id != val2 ...
-- NULL-Vergleich ist UNKNOWN, also ist der Ausdruck UNKNOWN (nicht TRUE).

-- Fix: NOT EXISTS
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM cancelled_orders co WHERE co.order_id = o.order_id
);

-- Alternative: LEFT JOIN / IS NULL Pattern
SELECT o.order_id
FROM orders o
LEFT JOIN cancelled_orders co ON o.order_id = co.order_id
WHERE co.order_id IS NULL;

-- Anti-Pattern (Spark SQL) — volle Tabellen joinen, dann aggregieren (riesiger Shuffle)
SELECT
    c.country,
    SUM(o.total_cents) AS revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY c.country;

-- Fix: Orders zuerst aggregieren, dann joinen
WITH order_agg AS (
    SELECT customer_id, SUM(total_cents) AS total_revenue
    FROM orders
    GROUP BY customer_id
)
SELECT
    c.country,
    SUM(oa.total_revenue) AS revenue
FROM order_agg oa
JOIN customers c ON oa.customer_id = c.customer_id
GROUP BY c.country;

-- Anti-Pattern (PostgreSQL) — country-Filter NACH Aggregation
SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
HAVING country = 'DE';  -- aggregiert alle Länder, dann Filter auf DE

-- Fix: vor Aggregation filtern
SELECT country, COUNT(*) AS customer_count
FROM customers
WHERE country = 'DE'
GROUP BY country;

-- Anti-Pattern — drei Level Subquery-Nesting
SELECT *
FROM (
    SELECT customer_id, AVG(order_total) AS avg_order
    FROM (
        SELECT customer_id, order_id, SUM(line_total) AS order_total
        FROM (
            SELECT * FROM order_lines WHERE is_cancelled = FALSE
        ) active_lines
        GROUP BY customer_id, order_id
    ) order_totals
    GROUP BY customer_id
) customer_avgs
WHERE avg_order > 100;

-- Fix: CTEs für Lesbarkeit
-- DuckDB SQL
WITH active_lines AS (
    SELECT customer_id, order_id, line_total
    FROM order_lines
    WHERE is_cancelled = FALSE
),
order_totals AS (
    SELECT customer_id, order_id, SUM(line_total) AS order_total
    FROM active_lines
    GROUP BY customer_id, order_id
),
customer_avgs AS (
    SELECT customer_id, AVG(order_total) AS avg_order
    FROM order_totals
    GROUP BY customer_id
)
SELECT *
FROM customer_avgs
WHERE avg_order > 100;

-- Anti-Pattern (PostgreSQL) — Funktion auf Spalte, Index auf placed_at nicht genutzt
SELECT * FROM orders WHERE YEAR(placed_at) = 2026;
SELECT * FROM orders WHERE DATE_TRUNC('month', placed_at) = '2026-01-01';
SELECT * FROM orders WHERE UPPER(email) = 'ALICE@EXAMPLE.COM';

-- Fix: Transform aufs Literal pushen, nicht auf die Spalte
SELECT * FROM orders
WHERE placed_at >= '2026-01-01' AND placed_at < '2027-01-01';

SELECT * FROM orders
WHERE placed_at >= '2026-01-01' AND placed_at < '2026-02-01';

SELECT * FROM orders WHERE email = LOWER('ALICE@EXAMPLE.COM');
-- oder besser: zur Write-Zeit lowercasen, lowercase-Spalte abfragen

-- Misleading: COUNT(*) vs COUNT(column) Confusion (PostgreSQL)
SELECT
    COUNT(*) AS total_rows,           -- 1000 (inkl. NULLs)
    COUNT(email) AS with_email,       -- 800 (NULLs raus)
    AVG(order_total) AS avg_total     -- average IGNORIERT null order_total Zeilen
FROM orders;

-- Fix: explizit zum NULL-Handling
SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS rows_with_email,
    COUNT(*) FILTER (WHERE email IS NULL) AS rows_without_email,  -- PostgreSQL-Syntax
    AVG(COALESCE(order_total, 0)) AS avg_total_incl_nulls_as_zero
FROM orders;

-- Anti-Pattern — orders.customer_id ist unique, aber promotions.customer_id NICHT
-- Das multipliziert Zeilen still
SELECT o.order_id, o.total, p.promo_code
FROM orders o
JOIN promotions p ON o.customer_id = p.customer_id;
-- Hat ein Kunde 3 Promotions, erscheint jede Order 3x

-- Fix: Uniqueness vor Join validieren
SELECT customer_id, COUNT(*) FROM promotions GROUP BY 1 HAVING COUNT(*) > 1;

-- Dann richtige Join-Form nutzen (z.B. nur neueste Promo):
WITH latest_promo AS (
    SELECT customer_id, promo_code,
           ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM promotions
)
SELECT o.order_id, o.total, lp.promo_code
FROM orders o
LEFT JOIN latest_promo lp ON o.customer_id = lp.customer_id AND lp.rn = 1;