Gemini 2.5 Flash × sql

8.7

CTE + window function fluency on a real schema

correctness 9.0quality 8.0documentation 9.012797ms

$ 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.012797ms

# judge: Logic is correct and uses ROW_NUMBER + window function for revenue_pct appropriately, but recomputing country aggregates via a second orders/customers join (instead of deriving from customer_spend) is slightly redundant.

$ cat gemini-2.5-flash.sql.r1.response

```sql
-- Computes the total spend for each individual customer, along with their country.
WITH customer_spend AS (
  SELECT
    c.id AS customer_id,
    c.name AS customer_name,
    c.country,
    SUM(o.amount) AS total_customer_spend
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  GROUP BY c.id, c.name, c.country
),
-- Ranks customers within each country by their total spend to identify the top customer.
-- Ties are broken by customer name in ascending order.
ranked_customer_spend AS (
  SELECT
    customer_id,
    customer_name,
    country,
    total_customer_spend,
    ROW_NUMBER() OVER (PARTITION BY country ORDER BY total_customer_spend DESC, customer_name ASC) AS rn
  FROM customer_spend
),
-- Aggregates orders data by country to compute total revenue, order count, and average order value.
country_metrics AS (
  SELECT
    c.country,
    SUM(o.amount) AS total_revenue,
    COUNT(o.id) AS order_count,
    ROUND(AVG(o.amount), 2) AS avg_order_value
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  GROUP BY c.country
)
-- Final selection, joining country metrics with the identified top customer and
-- calculating revenue percentage using a window function.
SELECT
  cm.country,
  cm.total_revenue,
  cm.order_count,
  cm.avg_order_value,
  rcs.customer_name AS top_customer,
  -- Calculate this country's share of global revenue as a percentage, rounded to 1 decimal place.
  ROUND(cm.total_revenue * 100.0 / SUM(cm.total_revenue) OVER (), 1) AS revenue_pct
FROM country_metrics cm
JOIN ranked_customer_spend rcs
  ON cm.country = rcs.country
  AND rcs.rn = 1 -- Select only the top-ranked customer for each country
ORDER BY cm.total_revenue DESC;
```