GPT-5.6 Sol × sql

10.0

CTE + window function fluency on a real schema

correctness 10.0quality 10.0documentation 10.011124ms

$ cat sql.prompt — what the model was asked

Write a single SQL query (PostgreSQL dialect) against the following schema:

```sql
CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  amount      NUMERIC(10,2) NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL
);

CREATE TABLE customers (
  id      SERIAL PRIMARY KEY,
  name    TEXT NOT NULL,
  country TEXT NOT NULL
);
```

The query must return, for each country:
1. `country` — the country name
2. `total_revenue` — sum of all order amounts
3. `order_count` — total number of orders
4. `avg_order_value` — average order amount, rounded to 2 decimal places
5. `top_customer` — the name of the customer with the highest total spend in that country
   (break ties by customer name ascending)
6. `revenue_pct` — this country's share of global revenue, as a percentage rounded to 1 decimal place

Requirements:
- Use at least one CTE
- Use a window function for `revenue_pct`
- Order results by `total_revenue` descending
- Add a comment above each CTE explaining what it computes

run 1 of 1

10.0

correctness 10.0quality 10.0documentation 10.011124ms

# judge: The query fully satisfies the requirements with correct aggregation, deterministic top-customer ranking, window-based revenue percentage, appropriate rounding, clear structure, and comments for every CTE.

$ cat codex-cli/gpt-5.6-sol.sql.r1.response

```sql
WITH
-- Computes total spend per customer within each country.
customer_spend AS (
    SELECT
        c.country,
        c.name,
        SUM(o.amount) AS customer_total
    FROM customers AS c
    JOIN orders AS o
      ON o.customer_id = c.id
    GROUP BY c.country, c.id, c.name
),
-- Ranks customers by spend in each country, breaking ties by name.
ranked_customers AS (
    SELECT
        country,
        name,
        ROW_NUMBER() OVER (
            PARTITION BY country
            ORDER BY customer_total DESC, name ASC
        ) AS customer_rank
    FROM customer_spend
),
-- Computes order-level revenue statistics for each country.
country_totals AS (
    SELECT
        c.country,
        SUM(o.amount) AS total_revenue,
        COUNT(*) AS order_count,
        ROUND(AVG(o.amount), 2) AS avg_order_value
    FROM customers AS c
    JOIN orders AS o
      ON o.customer_id = c.id
    GROUP BY c.country
)
SELECT
    ct.country,
    ct.total_revenue,
    ct.order_count,
    ct.avg_order_value,
    rc.name AS top_customer,
    ROUND(
        100 * ct.total_revenue
        / NULLIF(SUM(ct.total_revenue) OVER (), 0),
        1
    ) AS revenue_pct
FROM country_totals AS ct
JOIN ranked_customers AS rc
  ON rc.country = ct.country
 AND rc.customer_rank = 1
ORDER BY ct.total_revenue DESC;
```