SQL Query Generator

Input

What should the query return?

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

Tables and columns (optional)

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

SQL dialect

PostgreSQL

Query style

Readable (CTEs, one clause per line)

Explanation

Brief

Output (excerpt)

```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 used as given.**

The CTE finds customers with more than three orders in 2025; the main query keeps only their orders, truncates each order to its month and sums revenue per month and country. The date range is half-open so 31 December is counted once…

Input

What should the query return?

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

Tables and columns (optional)

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

SQL dialect

MySQL / MariaDB

Query style

Analytical (window functions where useful)

Explanation

Detailed (clause by clause)

Output (excerpt)

```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 used as given.**

| Clause | What it does |
|---|---|
| `ROW_NUMBER() OVER (PARTITION BY … ORDER BY created_at DESC, id DESC)` | numbers each customer's orders newest first; the id tiebreak makes the result deterministic |
| `JOIN ranked … AND r.rn = 1` | keeps exactly one row per customer |…