SQL 查询生成器

输入

查询应返回什么？

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

表和列（可选）

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

SQL 方言

PostgreSQL

查询风格

可读（CTE，每行一个子句）

说明

简要

输出（节选）

```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;
```

**使用给定的模式。**

CTE 找出 2025 年订单超过三笔的客户；主查询仅保留这些客户的订单，将每笔订单截取到月份并按月度和国家汇总收入。日期范围为左闭右开，因此 12 月 31 日只计入一次…

输入

查询应返回什么？

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

表和列（可选）

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

SQL 方言

MySQL / MariaDB

查询风格

分析型（在适用时使用窗口函数）

说明

详细（逐子句）

输出（节选）

```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;
```

**使用给定的模式。**

| 子句 | 作用 |
|---|---|
| `ROW_NUMBER() OVER (PARTITION BY … ORDER BY created_at DESC, id DESC)` | 为每个客户的订单按最新顺序编号；使用 id 作为平局分割，使结果确定性 |
| `JOIN ranked … AND r.rn = 1` | 每位客户仅保留一行 |
| …