Spaces:
Sleeping
Sleeping
File size: 5,682 Bytes
5716a3a 95707b2 5716a3a 95707b2 5716a3a 95707b2 5716a3a 95707b2 5716a3a 95707b2 5716a3a 95707b2 5716a3a 2194233 | 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 | 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,
),
]
|