Write a complex SQL query with CTEs, window functions, and aggregations
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
Is revenue per country summed correctly via JOIN + GROUP BY? Is `top_customer` correctly found using DISTINCT ON, ROW_NUMBER(), or a correlated subquery? Is `revenue_pct` computed with a window function (SUM(...) OVER ()) rather than a scalar subquery? Is rounding applied correctly (2dp for avg, 1dp for pct)?
Is the query readable — CTEs named clearly, columns aliased cleanly? Is there a single final SELECT rather than redundant nesting? Are aggregate functions used correctly?
Does each CTE have a comment explaining what it computes? Are column aliases self-explanatory? Is the overall query structure easy to follow top-to-bottom?
# 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.
inspect full trace →# Logic for revenue, avg, and top_customer (via DISTINCT ON) is correct, but revenue_pct is computed with a CROSS JOIN CTE rather than an actual window function (SUM(...) OVER()), directly violating an explicit requirement despite the model's rationalization; comments and explanations are clear and well-organized otherwise.
inspect full trace →# patched after judge fix
inspect full trace →# 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.
inspect full trace →