GPT-OSS 120B × sql

9.0

CTE + window function fluency on a real schema

correctness 9.0quality 9.0documentation 9.02445ms

$ 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.0

correctness 9.0quality 9.0documentation 9.02445ms

# judge: Correct use of CTEs, ROW_NUMBER for top_customer, and window function for revenue_pct with proper rounding, though the 4th comment is placed inside the final SELECT rather than above a CTE as strictly required.

$ cat gpt-oss-120b.sql.r1.response

```sql
/* 1️⃣  country_stats – aggregates revenue, order count and average order value per country */
WITH country_stats AS (
    SELECT
        c.country,
        SUM(o.amount)                         AS total_revenue,
        COUNT(*)                              AS order_count,
        ROUND(AVG(o.amount)::numeric, 2)      AS avg_order_value
    FROM orders     o
    JOIN customers  c ON o.customer_id = c.id
    GROUP BY c.country
),

/* 2️⃣  customer_spend – total spend of every customer, grouped by the customer’s country */
customer_spend AS (
    SELECT
        c.country,
        c.name      AS customer_name,
        SUM(o.amount) AS spend
    FROM orders    o
    JOIN customers c ON o.customer_id = c.id
    GROUP BY c.country, c.name
),

/* 3️⃣  top_customer_per_country – the customer with the highest spend per country
        (ties are resolved by alphabetical order of the customer name) */
top_customer_per_country AS (
    SELECT
        country,
        customer_name AS top_customer
    FROM (
        SELECT
            country,
            customer_name,
            spend,
            ROW_NUMBER() OVER (
                PARTITION BY country
                ORDER BY spend DESC, customer_name ASC
            ) AS rn
        FROM customer_spend
    ) ranked
    WHERE rn = 1
)

SELECT
    cs.country,
    cs.total_revenue,
    cs.order_count,
    cs.avg_order_value,
    t.top_customer,
    /* 4️⃣  revenue_pct – country’s share of global revenue, using a window function */
    ROUND( (cs.total_revenue / SUM(cs.total_revenue) OVER()) * 100, 1 ) AS revenue_pct
FROM country_stats          cs
JOIN top_customer_per_country t USING (country)
ORDER BY cs.total_revenue DESC;
```