Spaces:
Sleeping
Sleeping
File size: 12,622 Bytes
a676d61 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 | """
Task bank for the SQL Debugger environment.
Each task is a dict with:
- id: unique identifier
- difficulty: "easy", "medium", "hard"
- description: what's wrong (shown to agent as a hint)
- schema: list of CREATE TABLE statements
- seed_data: list of INSERT statements
- broken_query: the buggy SQL the agent must fix
- correct_query: the reference solution
- expected_output: list of tuples — the correct result rows
"""
TASKS = [
# =========================================================================
# EASY — Syntax errors: typos, missing keywords, wrong punctuation
# =========================================================================
{
"id": "easy_1",
"difficulty": "easy",
"description": "Fix the syntax errors in this SELECT query on the users table.",
"schema": [
"CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER, city TEXT);"
],
"seed_data": [
"INSERT INTO users VALUES (1, 'Alice', 30, 'Mumbai');",
"INSERT INTO users VALUES (2, 'Bob', 22, 'Delhi');",
"INSERT INTO users VALUES (3, 'Charlie', 35, 'Mumbai');",
"INSERT INTO users VALUES (4, 'Diana', 28, 'Bangalore');",
"INSERT INTO users VALUES (5, 'Eve', 40, 'Delhi');",
],
"broken_query": "SELCT name, age FORM users WERE age > 25;",
"correct_query": "SELECT name, age FROM users WHERE age > 25;",
"expected_output": [
("Alice", 30),
("Charlie", 35),
("Diana", 28),
("Eve", 40),
],
},
{
"id": "easy_2",
"difficulty": "easy",
"description": "Fix the syntax errors in this ORDER BY query.",
"schema": [
"CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);"
],
"seed_data": [
"INSERT INTO products VALUES (1, 'Keyboard', 1500.0);",
"INSERT INTO products VALUES (2, 'Mouse', 800.0);",
"INSERT INTO products VALUES (3, 'Monitor', 12000.0);",
"INSERT INTO products VALUES (4, 'Headset', 2500.0);",
],
"broken_query": "SELECT name, price FROM products ORDRE BY price DSEC;",
"correct_query": "SELECT name, price FROM products ORDER BY price DESC;",
"expected_output": [
("Monitor", 12000.0),
("Headset", 2500.0),
("Keyboard", 1500.0),
("Mouse", 800.0),
],
},
{
"id": "easy_3",
"difficulty": "easy",
"description": "Fix the syntax error in this aggregation query.",
"schema": [
"CREATE TABLE sales (id INTEGER PRIMARY KEY, product TEXT, amount REAL, region TEXT);"
],
"seed_data": [
"INSERT INTO sales VALUES (1, 'Widget', 100.0, 'North');",
"INSERT INTO sales VALUES (2, 'Widget', 200.0, 'South');",
"INSERT INTO sales VALUES (3, 'Gadget', 150.0, 'North');",
"INSERT INTO sales VALUES (4, 'Gadget', 300.0, 'South');",
"INSERT INTO sales VALUES (5, 'Widget', 50.0, 'North');",
],
"broken_query": "SELECT product, SUM(amount) AS total FROM sales GROPU BY product HAVNG total > 200;",
"correct_query": "SELECT product, SUM(amount) AS total FROM sales GROUP BY product HAVING total > 200;",
"expected_output": [
("Gadget", 450.0),
("Widget", 350.0),
],
},
# =========================================================================
# MEDIUM — Logic errors: wrong JOIN, missing clause, incorrect grouping
# =========================================================================
{
"id": "medium_1",
"difficulty": "medium",
"description": "This query should count only customers who have placed orders, but it's returning a wrong count. Fix the JOIN logic.",
"schema": [
"CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);",
"CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL);",
],
"seed_data": [
"INSERT INTO customers VALUES (1, 'Alice');",
"INSERT INTO customers VALUES (2, 'Bob');",
"INSERT INTO customers VALUES (3, 'Charlie');",
"INSERT INTO orders VALUES (1, 1, 500.0);",
"INSERT INTO orders VALUES (2, 1, 300.0);",
"INSERT INTO orders VALUES (3, 3, 700.0);",
],
"broken_query": "SELECT COUNT(DISTINCT customers.id) AS active_customers FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;",
"correct_query": "SELECT COUNT(DISTINCT customers.id) AS active_customers FROM customers INNER JOIN orders ON customers.id = orders.customer_id;",
"expected_output": [
(2,),
],
},
{
"id": "medium_2",
"difficulty": "medium",
"description": "This query should find employees in the Engineering department, but the WHERE clause is wrong.",
"schema": [
"CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);",
"CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER, salary REAL);",
],
"seed_data": [
"INSERT INTO departments VALUES (1, 'Engineering');",
"INSERT INTO departments VALUES (2, 'Marketing');",
"INSERT INTO departments VALUES (3, 'Sales');",
"INSERT INTO employees VALUES (1, 'Alice', 1, 90000);",
"INSERT INTO employees VALUES (2, 'Bob', 2, 70000);",
"INSERT INTO employees VALUES (3, 'Charlie', 1, 85000);",
"INSERT INTO employees VALUES (4, 'Diana', 3, 60000);",
],
"broken_query": "SELECT e.name, e.salary FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.name = 'Marketing' ORDER BY e.salary DESC;",
"correct_query": "SELECT e.name, e.salary FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.name = 'Engineering' ORDER BY e.salary DESC;",
"expected_output": [
("Alice", 90000.0),
("Charlie", 85000.0),
],
},
{
"id": "medium_3",
"difficulty": "medium",
"description": "This query should return the total revenue per category, but it's grouping by the wrong column.",
"schema": [
"CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT);",
"CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL, quantity_sold INTEGER);",
],
"seed_data": [
"INSERT INTO categories VALUES (1, 'Electronics');",
"INSERT INTO categories VALUES (2, 'Books');",
"INSERT INTO items VALUES (1, 'Phone', 1, 500.0, 10);",
"INSERT INTO items VALUES (2, 'Laptop', 1, 1000.0, 5);",
"INSERT INTO items VALUES (3, 'Novel', 2, 15.0, 100);",
"INSERT INTO items VALUES (4, 'Textbook', 2, 50.0, 30);",
],
"broken_query": "SELECT c.name AS category, SUM(i.price * i.quantity_sold) AS revenue FROM items i JOIN categories c ON i.category_id = c.id GROUP BY i.name ORDER BY revenue DESC;",
"correct_query": "SELECT c.name AS category, SUM(i.price * i.quantity_sold) AS revenue FROM items i JOIN categories c ON i.category_id = c.id GROUP BY c.name ORDER BY revenue DESC;",
"expected_output": [
("Electronics", 10000.0),
("Books", 3000.0),
],
},
# =========================================================================
# HARD — Multi-table / subtle bugs: wrong threshold, missing DISTINCT, etc.
# =========================================================================
{
"id": "hard_1",
"difficulty": "hard",
"description": "This query finds customers who ordered more than a threshold number of times, but the threshold is wrong.",
"schema": [
"CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT);",
"CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount REAL, order_date TEXT);",
],
"seed_data": [
"INSERT INTO customers VALUES (1, 'Alice', 'Mumbai');",
"INSERT INTO customers VALUES (2, 'Bob', 'Delhi');",
"INSERT INTO customers VALUES (3, 'Charlie', 'Mumbai');",
"INSERT INTO orders VALUES (1, 1, 500, '2025-01-10');",
"INSERT INTO orders VALUES (2, 1, 300, '2025-02-15');",
"INSERT INTO orders VALUES (3, 1, 200, '2025-03-20');",
"INSERT INTO orders VALUES (4, 2, 1000, '2025-01-05');",
"INSERT INTO orders VALUES (5, 2, 400, '2025-04-01');",
"INSERT INTO orders VALUES (6, 3, 600, '2025-06-01');",
],
"broken_query": "SELECT c.name, COUNT(o.id) AS order_count FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id HAVING order_count > 2 ORDER BY order_count DESC;",
"correct_query": "SELECT c.name, COUNT(o.id) AS order_count FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.id HAVING order_count > 1 ORDER BY order_count DESC;",
"expected_output": [
("Alice", 3),
("Bob", 2),
],
},
{
"id": "hard_2",
"difficulty": "hard",
"description": "This query should list unique students enrolled in courses, but duplicates are appearing due to a missing DISTINCT.",
"schema": [
"CREATE TABLE students (id INTEGER PRIMARY KEY, name TEXT);",
"CREATE TABLE courses (id INTEGER PRIMARY KEY, title TEXT);",
"CREATE TABLE enrollments (id INTEGER PRIMARY KEY, student_id INTEGER, course_id INTEGER);",
],
"seed_data": [
"INSERT INTO students VALUES (1, 'Alice');",
"INSERT INTO students VALUES (2, 'Bob');",
"INSERT INTO students VALUES (3, 'Charlie');",
"INSERT INTO courses VALUES (1, 'Math');",
"INSERT INTO courses VALUES (2, 'Science');",
"INSERT INTO courses VALUES (3, 'History');",
"INSERT INTO enrollments VALUES (1, 1, 1);",
"INSERT INTO enrollments VALUES (2, 1, 2);",
"INSERT INTO enrollments VALUES (3, 2, 1);",
"INSERT INTO enrollments VALUES (4, 2, 2);",
"INSERT INTO enrollments VALUES (5, 2, 3);",
"INSERT INTO enrollments VALUES (6, 3, 1);",
],
"broken_query": "SELECT s.name FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.id ORDER BY s.name;",
"correct_query": "SELECT DISTINCT s.name FROM students s JOIN enrollments e ON s.id = e.student_id JOIN courses c ON e.course_id = c.id ORDER BY s.name;",
"expected_output": [
("Alice",),
("Bob",),
("Charlie",),
],
},
{
"id": "hard_3",
"difficulty": "hard",
"description": "This query should find products priced above their category average, but it compares against the global average instead of per-category average.",
"schema": [
"CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT);",
"CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category_id INTEGER, price REAL);",
],
"seed_data": [
"INSERT INTO categories VALUES (1, 'Electronics');",
"INSERT INTO categories VALUES (2, 'Clothing');",
"INSERT INTO products VALUES (1, 'Phone', 1, 800.0);",
"INSERT INTO products VALUES (2, 'Laptop', 1, 1500.0);",
"INSERT INTO products VALUES (3, 'Tablet', 1, 400.0);",
"INSERT INTO products VALUES (4, 'Jacket', 2, 200.0);",
"INSERT INTO products VALUES (5, 'Shirt', 2, 50.0);",
"INSERT INTO products VALUES (6, 'Shoes', 2, 150.0);",
],
"broken_query": "SELECT p.name, p.price, c.name AS category FROM products p JOIN categories c ON p.category_id = c.id WHERE p.price > (SELECT AVG(price) FROM products) ORDER BY p.price DESC;",
"correct_query": "SELECT p.name, p.price, c.name AS category FROM products p JOIN categories c ON p.category_id = c.id WHERE p.price > (SELECT AVG(p2.price) FROM products p2 WHERE p2.category_id = p.category_id) ORDER BY p.price DESC;",
"expected_output": [
("Laptop", 1500.0, "Electronics"),
("Jacket", 200.0, "Clothing"),
("Shoes", 150.0, "Clothing"),
],
},
]
|