ritvik360/nl2sql-bench
0
1"""2nl2sql-bench/server/tasks/hard.py3===================================4Task 3 — Analytics & Window (difficulty: hard)5 6Questions require CTEs, window functions (RANK, ROW_NUMBER, running totals),7or non-trivial subqueries. Even strong frontier models often need 3–5 steps.8"""9 10from __future__ import annotations11 12from .base import BaseTask, TaskExample, register13 14 15@register16class AnalyticsWindowTask(BaseTask):17 name = "analytics-window"18 difficulty = "hard"19 20 examples = [21 TaskExample(22 question=(23 "Rank customers by their total spending on delivered orders "24 "using DENSE_RANK (rank 1 = highest spender). "25 "Return columns: customer_name, total_spent, spending_rank. "26 "Round total_spent to 2 decimal places. "27 "Sort by spending_rank ascending."28 ),29 sql=(30 "SELECT customer_name, total_spent, spending_rank "31 "FROM ( "32 " SELECT c.name AS customer_name, "33 " ROUND(SUM(o.total_amount), 2) AS total_spent, "34 " DENSE_RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS spending_rank "35 " FROM customers c "36 " JOIN orders o ON o.customer_id = c.id "37 " WHERE o.status = 'delivered' "38 " GROUP BY c.id, c.name "39 ") sub "40 "ORDER BY spending_rank ASC"41 ),42 notes="Window function DENSE_RANK inside a subquery wrapping a GROUP BY.",43 ),44 TaskExample(45 question=(46 "For each product that has been reviewed, show its name, its own "47 "average rating, and the average rating of all products in its category. "48 "Return columns: product_name, product_avg_rating, category_avg_rating. "49 "Round both averages to 2 decimal places. "50 "Sort by product_avg_rating descending."51 ),52 sql=(53 "SELECT p.name AS product_name, "54 " ROUND(AVG(r.rating), 2) AS product_avg_rating, "55 " ROUND(AVG(AVG(r.rating)) OVER (PARTITION BY p.category_id), 2) "56 " AS category_avg_rating "57 "FROM products p "58 "JOIN reviews r ON r.product_id = p.id "59 "GROUP BY p.id, p.name, p.category_id "60 "ORDER BY product_avg_rating DESC"61 ),62 notes="AVG of AVG via window PARTITION BY — requires nested aggregate understanding.",63 ),64 TaskExample(65 question=(66 "Find all customers whose most recent order has status 'cancelled'. "67 "Use a CTE with ROW_NUMBER to identify the latest order per customer. "68 "Return columns: customer_name, last_order_status, last_order_date. "69 "Sort by customer_name ascending."70 ),71 sql=(72 "WITH ranked_orders AS ( "73 " SELECT customer_id, status, created_at, "74 " ROW_NUMBER() OVER (PARTITION BY customer_id "75 " ORDER BY created_at DESC) AS rn "76 " FROM orders "77 ") "78 "SELECT c.name AS customer_name, "79 " ro.status AS last_order_status, "80 " ro.created_at AS last_order_date "81 "FROM customers c "82 "JOIN ranked_orders ro ON ro.customer_id = c.id "83 "WHERE ro.rn = 1 "84 " AND ro.status = 'cancelled' "85 "ORDER BY customer_name ASC"86 ),87 notes="CTE + ROW_NUMBER window partitioned by customer_id.",88 ),89 TaskExample(90 question=(91 "Show the monthly revenue from delivered orders and its running total, "92 "for all months in 2024. "93 "Return columns: month (format YYYY-MM), monthly_revenue, running_total. "94 "Round both revenue columns to 2 decimal places. "95 "Sort by month ascending."96 ),97 sql=(98 "WITH monthly AS ( "99 " SELECT strftime('%Y-%m', created_at) AS month, "100 " ROUND(SUM(total_amount), 2) AS monthly_revenue "101 " FROM orders "102 " WHERE status = 'delivered' "103 " AND created_at >= '2024-01-01' "104 " AND created_at < '2025-01-01' "105 " GROUP BY strftime('%Y-%m', created_at) "106 ") "107 "SELECT month, "108 " monthly_revenue, "109 " ROUND(SUM(monthly_revenue) OVER (ORDER BY month), 2) AS running_total "110 "FROM monthly "111 "ORDER BY month ASC"112 ),113 notes="CTE + cumulative SUM window ordered by month string.",114 ),115 TaskExample(116 question=(117 "Find products whose average rating is strictly above the average "118 "rating of all products in their category. "119 "Return columns: product_name, category_name, "120 "product_avg_rating, category_avg_rating. "121 "Round both averages to 2 decimal places. "122 "Sort by product_avg_rating descending, then product_name ascending."123 ),124 sql=(125 "WITH product_ratings AS ( "126 " SELECT p.id AS product_id, p.name AS product_name, "127 " p.category_id, c.name AS category_name, "128 " ROUND(AVG(r.rating), 2) AS product_avg_rating "129 " FROM products p "130 " JOIN reviews r ON r.product_id = p.id "131 " JOIN categories c ON c.id = p.category_id "132 " GROUP BY p.id, p.name, p.category_id, c.name "133 "), "134 "category_ratings AS ( "135 " SELECT category_id, "136 " ROUND(AVG(product_avg_rating), 2) AS category_avg_rating "137 " FROM product_ratings "138 " GROUP BY category_id "139 ") "140 "SELECT pr.product_name, pr.category_name, "141 " pr.product_avg_rating, cr.category_avg_rating "142 "FROM product_ratings pr "143 "JOIN category_ratings cr ON cr.category_id = pr.category_id "144 "WHERE pr.product_avg_rating > cr.category_avg_rating "145 "ORDER BY pr.product_avg_rating DESC, pr.product_name ASC"146 ),147 notes="Two CTEs, correlated comparison between product and category averages.",148 ),149 ]150 151 def description(self) -> str:152 return (153 "Advanced analytics queries using CTEs, window functions "154 "(DENSE_RANK, ROW_NUMBER, running SUM), and nested subqueries. "155 "Tests multi-step reasoning and SQLite-specific syntax."156 )157