Llama 3.3 70B × sql

4.7

CTE + window function fluency on a real schema

correctness 2.0quality 5.0documentation 7.0977ms

$ 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

4.7

correctness 2.0quality 5.0documentation 7.0977ms

# judge: The country_sales CTE groups by (country, name), so downstream MAX() aggregation in country_data yields the top customer's revenue/order_count/avg instead of the country's true totals (correct only when a country has a single customer), and revenue_pct is computed via a CROSS JOIN scalar CTE rather than a window function as required; despite clear comments and a single final SELECT, the core aggregation logic is fundamentally broken.

$ cat llama-3.3-70b.sql.r1.response

```sql
-- Compute country-level sales data and top customers
WITH country_sales AS (
  SELECT 
    c.country,
    SUM(o.amount) AS total_revenue,
    COUNT(o.id) AS order_count,
    ROUND(SUM(o.amount) / COUNT(o.id), 2) AS avg_order_value,
    ROW_NUMBER() OVER (
      PARTITION BY c.country 
      ORDER BY SUM(o.amount) DESC, c.name ASC
    ) AS customer_rank,
    c.name AS top_customer
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  GROUP BY c.country, c.name
),
-- Compute total revenue by country, including top customer info
country_data AS (
  SELECT 
    country,
    MAX(total_revenue) AS total_revenue,
    MAX(order_count) AS order_count,
    MAX(avg_order_value) AS avg_order_value,
    MIN(CASE WHEN customer_rank = 1 THEN top_customer END) AS top_customer
  FROM country_sales
  GROUP BY country
),
-- Compute global revenue
global_revenue AS (
  SELECT SUM(amount) AS total FROM orders
)
-- Compute final results, including country revenue percentages
SELECT 
  cd.country,
  cd.total_revenue,
  cd.order_count,
  cd.avg_order_value,
  cd.top_customer,
  ROUND(100.0 * cd.total_revenue / gr.total, 1) AS revenue_pct
FROM country_data cd
CROSS JOIN global_revenue gr
ORDER BY cd.total_revenue DESC;
```