Claude Sonnet 4.6 × sql

6.7

CTE + window function fluency on a real schema

correctness 8.0quality 6.0documentation 6.016174ms

$ 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

6.7

correctness 8.0quality 6.0documentation 6.016174ms

# judge: patched after judge fix

$ cat claude-sonnet-4-6.sql.r1.response

```sql
-- Aggregate order totals per customer, used to compute country-level metrics
-- and to identify the top spender within each country.
WITH customer_totals AS (
    SELECT
        c.id                    AS customer_id,
        c.name                  AS customer_name,
        c.country,
        SUM(o.amount)           AS customer_spend,
        COUNT(o.id)             AS customer_order_count
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name, c.country
),

-- Roll customer-level totals up to country level, and rank customers within
-- each country so we can later pick the single top spender per country.
country_stats AS (
    SELECT
        country,
        SUM(customer_spend)                                         AS total_revenue,
        SUM(customer_order_count)                                   AS order_count,
        -- Rank customers within their country: highest spender first,
        -- ties broken alphabetically by name.
        RANK() OVER (
            PARTITION BY country
            ORDER BY customer_spend DESC, customer_name ASC
        )                                                           AS customer_rank,
        customer_name,
        -- Global revenue across every row in this CTE, enabling the
        -- percentage calculation without a separate subquery.
        SUM(SUM(customer_spend)) OVER ()                            AS global_revenue
    FROM customer_totals
    GROUP BY country, customer_name, customer_spend
)

SELECT
    country,
    total_revenue,
    order_count,
    ROUND(total_revenue / order_count, 2)               AS avg_order_value,
    customer_name                                        AS top_customer,
    ROUND((total_revenue / global_revenue * 100)::NUMERIC, 1) AS revenue_pct
FROM country_stats
-- Keep only the highest-ranked customer per country (the top spender).
WHERE customer_rank = 1
ORDER BY total_revenue DESC;
```

### How it works

| CTE / clause | Purpose |
|---|---|
| `customer_totals` | Joins `customers` → `orders` and produces one row per customer with their total spend and order count. |
| `country_stats` | Groups by country **and** customer, computes country-level totals with `SUM(…)`, and adds two window functions: `RANK()` to find the top spender per country, and `SUM(SUM(…)) OVER ()` to materialise the global revenue in every row so `revenue_pct` needs no extra subquery. |
| `WHERE customer_rank = 1` | Filters to the single top-spending customer per country (ties broken by name ascending, as specified). |
| Final `SELECT` | Derives `avg_order_value` by dividing country revenue by order count, and `revenue_pct` by dividing country revenue by the pre-computed global total. |