SQL‑querygenerator

Invoer

Wat moet de query retourneren?

Monthly revenue per country for 2025, counting only customers who placed more than 3 orders in 2025. Sort by month, then revenue descending.

Tabellen en kolommen (optioneel)

orders(id, customer_id, total_cents integer, created_at timestamp) customers(id, country text, signup_at timestamp)

SQL‑dialect

PostgreSQL

Query‑stijl

Leesbaar (CTE's, één clausule per regel)

Uitleg

Kort

Uitvoer (fragment)

```sql
WITH active_customers AS (
  SELECT o.customer_id
  FROM orders AS o
  WHERE o.created_at >= DATE '2025-01-01'
    AND o.created_at <  DATE '2026-01-01'
  GROUP BY o.customer_id
  HAVING COUNT(*) > 3
)
SELECT
  DATE_TRUNC('month', o.created_at)::date AS month,
  c.country,
  SUM(o.total_cents) / 100.0            AS revenue
FROM orders AS o
JOIN active_customers AS a ON a.customer_id = o.customer_id
JOIN customers        AS c ON c.id = o.customer_id
WHERE o.created_at >= DATE '2025-01-01'
  AND o.created_at <  DATE '2026-01-01'
GROUP BY 1, 2
ORDER BY month, revenue DESC;
```

**Schema gebruikt zoals gegeven.**

De CTE vindt klanten met meer dan drie bestellingen in 2025; de hoofdquery houdt alleen hun bestellingen, truncates elke bestelling naar de maand en somt de omzet per maand en land. Het datumbereik is half‑open zodat 31 december één keer wordt geteld…

Invoer

Wat moet de query retourneren?

For every customer, their most recent order with its total and date.

Tabellen en kolommen (optioneel)

orders(id, customer_id, total_cents integer, created_at timestamp) customers(id, email text)

SQL‑dialect

MySQL / MariaDB

Query‑stijl

Analytisch (window‑functies waar nuttig)

Uitleg

Gedetailleerd (clausule voor clausule)

Uitvoer (fragment)

```sql
WITH ranked AS (
  SELECT
    o.customer_id,
    o.id          AS order_id,
    o.total_cents,
    o.created_at,
    ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.created_at DESC, o.id DESC) AS rn
  FROM orders AS o
)
SELECT c.id AS customer_id, c.email, r.order_id, r.total_cents / 100.0 AS total, r.created_at
FROM customers AS c
JOIN ranked AS r ON r.customer_id = c.id AND r.rn = 1
ORDER BY r.created_at DESC;
```

**Schema gebruikt zoals gegeven.**

| Clause | Wat het doet |
|---|---|
| `ROW_NUMBER() OVER (PARTITION BY … ORDER BY created_at DESC, id DESC)` | nummert elke bestelling van een klant, nieuwste eerst; de id‑tiebreak maakt het resultaat deterministisch |
| `JOIN ranked … AND r.rn = 1` | houdt precies één rij per klant |…