GPT-6 Astra × sql

9.3

CTE + window function fluency on a real schema

correctness 9.0quality 9.0documentation 10.017068ms

$ 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

9.3

correctness 9.0quality 9.0documentation 10.017068ms

# judge: The query correctly aggregates revenue and orders, ranks top customers with deterministic tie-breaking, uses a window function for global revenue share, and documents every CTE; the only minor ambiguity is that its LEFT JOIN includes countries and top customers with no orders.

$ cat codex-cli/gpt-6-astra.sql.r1.response

```sql
WITH
-- Compute each customer's total spend and order count, including customers without orders.
customer_totals AS (
    SELECT
        c.id,
        c.name,
        c.country,
        COALESCE(SUM(o.amount), 0) AS total_spend,
        COUNT(o.id) AS order_count
    FROM customers AS c
    LEFT JOIN orders AS o ON o.customer_id = c.id
    GROUP BY c.id, c.name, c.country
),
-- Rank customers within each country by spend, breaking ties by name.
ranked_customers AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY country
            ORDER BY total_spend DESC, name ASC, id ASC
        ) AS customer_rank
    FROM customer_totals
),
-- Aggregate country revenue and order counts and identify each country's top customer.
country_totals AS (
    SELECT
        country,
        SUM(total_spend) AS total_revenue,
        SUM(order_count) AS order_count,
        MAX(name) FILTER (WHERE customer_rank = 1) AS top_customer
    FROM ranked_customers
    GROUP BY country
)
SELECT
    country,
    total_revenue,
    order_count,
    ROUND(total_revenue / NULLIF(order_count, 0), 2) AS avg_order_value,
    top_customer,
    ROUND(
        100.0 * total_revenue / NULLIF(SUM(total_revenue) OVER (), 0),
        1
    ) AS revenue_pct
FROM country_totals
ORDER BY total_revenue DESC;
```