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