Spaces:
Sleeping
Sleeping
| """ | |
| 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"), | |
| ], | |
| }, | |
| ] | |