Claude Haiku 4.5 × sql

8.7

CTE + window function fluency on a real schema

correctness 9.0quality 8.0documentation 9.03378ms

$ 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

8.7

correctness 9.0quality 8.0documentation 9.03378ms

# judge: Correct results with proper GROUP BY, DISTINCT-via-ROW_NUMBER for top_customer, and window-function-derived revenue_pct, though the extra grouping by window-computed columns and duplicate orders-customers joins add unnecessary complexity.

$ cat claude-haiku-4-5.sql.r1.response

```sql
-- CTE 1: Aggregate order data by country with customer details
WITH country_orders AS (
  SELECT
    c.country,
    c.id AS customer_id,
    c.name AS customer_name,
    o.amount,
    SUM(o.amount) OVER (PARTITION BY c.country) AS country_total,
    SUM(o.amount) OVER () AS global_total
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
),

-- CTE 2: Calculate summary statistics per country
country_summary AS (
  SELECT
    country,
    SUM(amount) AS total_revenue,
    COUNT(*) AS order_count,
    ROUND(AVG(amount)::NUMERIC, 2) AS avg_order_value,
    country_total,
    global_total
  FROM country_orders
  GROUP BY country, country_total, global_total
),

-- CTE 3: Identify the top customer per country (highest spend, ties broken by name)
top_customers AS (
  SELECT
    c.country,
    c.name,
    SUM(o.amount) AS customer_total,
    ROW_NUMBER() OVER (PARTITION BY c.country ORDER BY SUM(o.amount) DESC, c.name ASC) AS rn
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  GROUP BY c.country, c.name
)

SELECT
  cs.country,
  cs.total_revenue,
  cs.order_count,
  cs.avg_order_value,
  tc.name AS top_customer,
  ROUND((cs.total_revenue / cs.global_total * 100)::NUMERIC, 1) AS revenue_pct
FROM country_summary cs
JOIN top_customers tc ON cs.country = tc.country AND tc.rn = 1
ORDER BY cs.total_revenue DESC;
```