sql_env/tasks/medium.py

from sql_env.models import SQLTask
from sql_env.grader import grade

MEDIUM_TASKS = [
    SQLTask(
        task_id="medium_001",
        difficulty="medium",
        broken_query="SELECT name, SUM(salary) FORM employees GRUP BY department",
        canonical_answer="SELECT name, SUM(salary) FROM employees GROUP BY department",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_002",
        difficulty="medium",
        broken_query="SELECT * FROM orders WHER total > 500 AND status = 'active' ORDR BY total DESC",
        canonical_answer="SELECT * FROM orders WHERE total > 500 AND status = 'active' ORDER BY total DESC",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_003",
        difficulty="medium",
        broken_query="SELECT department, COUNT(*) AS emp_count FORM employees GROUP BY department HAVNG COUNT(*) > 5",
        canonical_answer="SELECT department, COUNT(*) AS emp_count FROM employees GROUP BY department HAVING COUNT(*) > 5",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_004",
        difficulty="medium",
        broken_query="SELCT product_id, SUM(quantity) FROM sales WEHRE year = 2024 GROUP BY product_id",
        canonical_answer="SELECT product_id, SUM(quantity) FROM sales WHERE year = 2024 GROUP BY product_id",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_005",
        difficulty="medium",
        broken_query="SELECT e.name, d.dept_name FORM employees e INNE JOIN departments d ON e.dept_id = d.id WHER e.salary > 50000",
        canonical_answer="SELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.id WHERE e.salary > 50000",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_006",
        difficulty="medium",
        broken_query="SELECT category, AVG(price) AS avg_price FORM products GRUP BY category ORDR BY avg_price DESC",
        canonical_answer="SELECT category, AVG(price) AS avg_price FROM products GROUP BY category ORDER BY avg_price DESC",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_007",
        difficulty="medium",
        broken_query="SELECT u.email, COUNT(o.id) AS order_count FORM users u LFT JOIN orders o ON u.id = o.user_id GRUP BY u.email",
        canonical_answer="SELECT u.email, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.email",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_008",
        difficulty="medium",
        broken_query="SELECT id, title FORM articles WEHRE published = 1 ORDR BY created_at DESC LIMT 10",
        canonical_answer="SELECT id, title FROM articles WHERE published = 1 ORDER BY created_at DESC LIMIT 10",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_009",
        difficulty="medium",
        broken_query="SELECT region, SUM(revenue) AS total FORM sales GRUP BY region HAVNG SUM(revenue) > 100000",
        canonical_answer="SELECT region, SUM(revenue) AS total FROM sales GROUP BY region HAVING SUM(revenue) > 100000",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_010",
        difficulty="medium",
        broken_query="SELCT p.name, s.quantity FORM products p INNE JOIN stock s ON p.id = s.product_id WEHRE s.quantity < 10",
        canonical_answer="SELECT p.name, s.quantity FROM products p INNER JOIN stock s ON p.id = s.product_id WHERE s.quantity < 10",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_011",
        difficulty="medium",
        broken_query="SELECT customer_id, MAX(amount) AS max_order FORM orders GRUP BY customer_id ORDR BY max_order DESC",
        canonical_answer="SELECT customer_id, MAX(amount) AS max_order FROM orders GROUP BY customer_id ORDER BY max_order DESC",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_012",
        difficulty="medium",
        broken_query="SELECT t.name, COUNT(e.id) AS member_count FORM teams t LFT JOIN employees e ON t.id = e.team_id GRUP BY t.name",
        canonical_answer="SELECT t.name, COUNT(e.id) AS member_count FROM teams t LEFT JOIN employees e ON t.id = e.team_id GROUP BY t.name",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_013",
        difficulty="medium",
        broken_query="SELCT month, SUM(sales) FORM monthly_revenue WEHRE year = 2024 GRUP BY month ORDR BY month ASC",
        canonical_answer="SELECT month, SUM(sales) FROM monthly_revenue WHERE year = 2024 GROUP BY month ORDER BY month ASC",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_014",
        difficulty="medium",
        broken_query="SELECT c.name, AVG(r.rating) AS avg_rating FORM courses c INNE JOIN reviews r ON c.id = r.course_id GRUP BY c.name HAVNG AVG(r.rating) >= 4.0",
        canonical_answer="SELECT c.name, AVG(r.rating) AS avg_rating FROM courses c INNER JOIN reviews r ON c.id = r.course_id GROUP BY c.name HAVING AVG(r.rating) >= 4.0",
        error_hint=None,
        grader=grade,
    ),
    SQLTask(
        task_id="medium_015",
        difficulty="medium",
        broken_query="SELECT DISTINCT country FORM customers WEHRE signup_date > '2023-01-01' ORDR BY country ASC",
        canonical_answer="SELECT DISTINCT country FROM customers WHERE signup_date > '2023-01-01' ORDER BY country ASC",
        error_hint=None,
        grader=grade,
    ),
]