SyncShift/sql-correction-env
0
1from sql_env.models import SQLTask2from sql_env.grader import grade3 4HARD_TASKS = [5 SQLTask(6 task_id="hard_001",7 difficulty="hard",8 broken_query=(9 "SELCT e.name, d.dept_name, SUM(s.amount) AS total_sales "10 "FORM employees e LFT JOIN departments d ON e.dpt_id = d.id "11 "INNE JOIN sales s ON e.id = s.emp_id "12 "WHER s.sale_date BETWEN '2024-01-01' AND '2024-12-31' "13 "GRUP BY e.name, d.dept_name "14 "HAVNG SUM(s.amount) > 10000 "15 "ORDR BY total_sales DSC"16 ),17 canonical_answer=(18 "SELECT e.name, d.dept_name, SUM(s.amount) AS total_sales "19 "FROM employees e LEFT JOIN departments d ON e.dept_id = d.id "20 "INNER JOIN sales s ON e.id = s.emp_id "21 "WHERE s.sale_date BETWEEN '2024-01-01' AND '2024-12-31' "22 "GROUP BY e.name, d.dept_name "23 "HAVING SUM(s.amount) > 10000 "24 "ORDER BY total_sales DESC"25 ),26 schema_context=(27 "employees(id INT, name VARCHAR, dept_id INT, salary DECIMAL)\n"28 "departments(id INT, dept_name VARCHAR)\n"29 "sales(id INT, emp_id INT, amount DECIMAL, sale_date DATE)"30 ),31 error_hint=None,32 max_steps=4,33 grader=grade,34 ),35 SQLTask(36 task_id="hard_002",37 difficulty="hard",38 broken_query=(39 "SELECT c.name, COUNT(o.id) AS order_count, SUM(oi.qty * p.price) AS revenue "40 "FORM customers c LFT JOIN orders o ON c.id = o.customer_id "41 "LFT JOIN order_items oi ON o.id = oi.order_id "42 "INNE JOIN products p ON oi.product_id = p.id "43 "WHER o.created_at >= '2024-01-01' "44 "GRUP BY c.name "45 "HAVNG revenue > 5000 "46 "ORDR BY revenue DSC LIMT 10"47 ),48 canonical_answer=(49 "SELECT c.name, COUNT(o.id) AS order_count, SUM(oi.qty * p.price) AS revenue "50 "FROM customers c LEFT JOIN orders o ON c.id = o.customer_id "51 "LEFT JOIN order_items oi ON o.id = oi.order_id "52 "INNER JOIN products p ON oi.product_id = p.id "53 "WHERE o.created_at >= '2024-01-01' "54 "GROUP BY c.name "55 "HAVING revenue > 5000 "56 "ORDER BY revenue DESC LIMIT 10"57 ),58 schema_context=(59 "customers(id INT, name VARCHAR, email VARCHAR)\n"60 "orders(id INT, customer_id INT, created_at DATETIME)\n"61 "order_items(id INT, order_id INT, product_id INT, qty INT)\n"62 "products(id INT, name VARCHAR, price DECIMAL)"63 ),64 error_hint=None,65 max_steps=4,66 grader=grade,67 ),68 SQLTask(69 task_id="hard_003",70 difficulty="hard",71 broken_query=(72 "SELECT dept, AVG(salary) AS avg_sal, MAX(salary) AS max_sal "73 "FORM employees "74 "WHER hire_date BETWEN '2020-01-01' AND '2023-12-31' AND status = 'active' "75 "GRUP BY dept "76 "HAVNG AVG(salary) > 60000 "77 "ORDR BY avg_sal DSC"78 ),79 canonical_answer=(80 "SELECT dept, AVG(salary) AS avg_sal, MAX(salary) AS max_sal "81 "FROM employees "82 "WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31' AND status = 'active' "83 "GROUP BY dept "84 "HAVING AVG(salary) > 60000 "85 "ORDER BY avg_sal DESC"86 ),87 schema_context=(88 "employees(id INT, name VARCHAR, dept VARCHAR, salary DECIMAL, "89 "hire_date DATE, status VARCHAR)"90 ),91 error_hint=None,92 max_steps=4,93 grader=grade,94 ),95 SQLTask(96 task_id="hard_004",97 difficulty="hard",98 broken_query=(99 "SELCT p.name, cat.category_name, SUM(oi.quantity) AS total_sold "100 "FORM products p "101 "INNE JOIN categories cat ON p.cat_id = cat.id "102 "INNE JOIN order_items oi ON p.id = oi.prod_id "103 "INNE JOIN orders o ON oi.order_id = o.id "104 "WHER o.status = 'completed' AND o.order_date >= '2024-01-01' "105 "GRUP BY p.name, cat.category_name "106 "ORDR BY total_sold DSC LIMT 20"107 ),108 canonical_answer=(109 "SELECT p.name, cat.category_name, SUM(oi.quantity) AS total_sold "110 "FROM products p "111 "INNER JOIN categories cat ON p.category_id = cat.id "112 "INNER JOIN order_items oi ON p.id = oi.product_id "113 "INNER JOIN orders o ON oi.order_id = o.id "114 "WHERE o.status = 'completed' AND o.order_date >= '2024-01-01' "115 "GROUP BY p.name, cat.category_name "116 "ORDER BY total_sold DESC LIMIT 20"117 ),118 schema_context=(119 "products(id INT, name VARCHAR, category_id INT, price DECIMAL)\n"120 "categories(id INT, category_name VARCHAR)\n"121 "order_items(id INT, order_id INT, product_id INT, quantity INT)\n"122 "orders(id INT, status VARCHAR, order_date DATE)"123 ),124 error_hint=None,125 max_steps=4,126 grader=grade,127 ),128 SQLTask(129 task_id="hard_005",130 difficulty="hard",131 broken_query=(132 "SELECT a.title, u.username, COUNT(c.id) AS comment_count, "133 "AVG(r.score) AS avg_score "134 "FORM articles a "135 "INNE JOIN users u ON a.author_id = u.id "136 "LFT JOIN comments c ON a.id = c.article_id "137 "LFT JOIN ratings r ON a.id = r.article_id "138 "WHER a.published_at BETWEN '2024-01-01' AND '2024-06-30' "139 "GRUP BY a.title, u.username "140 "HAVNG COUNT(c.id) > 5 "141 "ORDR BY avg_score DSC"142 ),143 canonical_answer=(144 "SELECT a.title, u.username, COUNT(c.id) AS comment_count, "145 "AVG(r.score) AS avg_score "146 "FROM articles a "147 "INNER JOIN users u ON a.author_id = u.id "148 "LEFT JOIN comments c ON a.id = c.article_id "149 "LEFT JOIN ratings r ON a.id = r.article_id "150 "WHERE a.published_at BETWEEN '2024-01-01' AND '2024-06-30' "151 "GROUP BY a.title, u.username "152 "HAVING COUNT(c.id) > 5 "153 "ORDER BY avg_score DESC"154 ),155 schema_context=(156 "articles(id INT, title VARCHAR, author_id INT, published_at DATE)\n"157 "users(id INT, username VARCHAR, email VARCHAR)\n"158 "comments(id INT, article_id INT, user_id INT, body TEXT)\n"159 "ratings(id INT, article_id INT, user_id INT, score FLOAT)"160 ),161 error_hint=None,162 max_steps=4,163 grader=grade,164 ),165 SQLTask(166 task_id="hard_006",167 difficulty="hard",168 broken_query=(169 "SELCT w.warehouse_name, p.name, SUM(inv.qty) AS stock_total "170 "FORM warehouses w "171 "INNE JOIN inventory inv ON w.id = inv.wrhs_id "172 "INNE JOIN products p ON inv.product_id = p.id "173 "WHER inv.last_updated >= '2024-01-01' "174 "GRUP BY w.warehouse_name, p.name "175 "HAVNG SUM(inv.qty) < 50 "176 "ORDR BY stock_total ASC"177 ),178 canonical_answer=(179 "SELECT w.warehouse_name, p.name, SUM(inv.qty) AS stock_total "180 "FROM warehouses w "181 "INNER JOIN inventory inv ON w.id = inv.warehouse_id "182 "INNER JOIN products p ON inv.product_id = p.id "183 "WHERE inv.last_updated >= '2024-01-01' "184 "GROUP BY w.warehouse_name, p.name "185 "HAVING SUM(inv.qty) < 50 "186 "ORDER BY stock_total ASC"187 ),188 schema_context=(189 "warehouses(id INT, warehouse_name VARCHAR, location VARCHAR)\n"190 "inventory(id INT, warehouse_id INT, product_id INT, qty INT, "191 "last_updated DATE)\n"192 "products(id INT, name VARCHAR, sku VARCHAR, price DECIMAL)"193 ),194 error_hint=None,195 max_steps=4,196 grader=grade,197 ),198 SQLTask(199 task_id="hard_007",200 difficulty="hard",201 broken_query=(202 "SELECT s.student_name, co.course_name, "203 "AVG(g.grade) AS avg_grade, COUNT(g.id) AS assignments_done "204 "FORM students s "205 "INNE JOIN enrollments en ON s.id = en.student_id "206 "INNE JOIN courses co ON en.course_id = co.id "207 "INNE JOIN grades g ON s.id = g.std_id AND co.id = g.course_id "208 "WHER en.semester = 'Fall2024' "209 "GRUP BY s.student_name, co.course_name "210 "HAVNG AVG(g.grade) >= 70 "211 "ORDR BY avg_grade DSC"212 ),213 canonical_answer=(214 "SELECT s.student_name, co.course_name, "215 "AVG(g.grade) AS avg_grade, COUNT(g.id) AS assignments_done "216 "FROM students s "217 "INNER JOIN enrollments en ON s.id = en.student_id "218 "INNER JOIN courses co ON en.course_id = co.id "219 "INNER JOIN grades g ON s.id = g.student_id AND co.id = g.course_id "220 "WHERE en.semester = 'Fall2024' "221 "GROUP BY s.student_name, co.course_name "222 "HAVING AVG(g.grade) >= 70 "223 "ORDER BY avg_grade DESC"224 ),225 schema_context=(226 "students(id INT, student_name VARCHAR, email VARCHAR)\n"227 "enrollments(id INT, student_id INT, course_id INT, semester VARCHAR)\n"228 "courses(id INT, course_name VARCHAR, credits INT)\n"229 "grades(id INT, student_id INT, course_id INT, grade FLOAT)"230 ),231 error_hint=None,232 max_steps=4,233 grader=grade,234 ),235 SQLTask(236 task_id="hard_008",237 difficulty="hard",238 broken_query=(239 "SELCT e.name, m.name AS manager_name, d.dept_name, "240 "e.salary, AVG(e2.salary) AS dept_avg "241 "FORM employees e "242 "LFT JOIN employees m ON e.manager_id = m.id "243 "INNE JOIN departments d ON e.dept_id = d.id "244 "INNE JOIN employees e2 ON e2.dept_id = e.dept_id "245 "WHER e.salary > 50000 "246 "GRUP BY e.name, m.name, d.dept_name, e.salary "247 "HAVNG e.salary > AVG(e2.salary) "248 "ORDR BY e.salary DSC"249 ),250 canonical_answer=(251 "SELECT e.name, m.name AS manager_name, d.dept_name, "252 "e.salary, AVG(e2.salary) AS dept_avg "253 "FROM employees e "254 "LEFT JOIN employees m ON e.manager_id = m.id "255 "INNER JOIN departments d ON e.dept_id = d.id "256 "INNER JOIN employees e2 ON e2.dept_id = e.dept_id "257 "WHERE e.salary > 50000 "258 "GROUP BY e.name, m.name, d.dept_name, e.salary "259 "HAVING e.salary > AVG(e2.salary) "260 "ORDER BY e.salary DESC"261 ),262 schema_context=(263 "employees(id INT, name VARCHAR, dept_id INT, manager_id INT, "264 "salary DECIMAL)\n"265 "departments(id INT, dept_name VARCHAR, budget DECIMAL)"266 ),267 error_hint=None,268 max_steps=4,269 grader=grade,270 ),271 SQLTask(272 task_id="hard_009",273 difficulty="hard",274 broken_query=(275 "SELECT t.tag_name, COUNT(DISTINCT pt.post_id) AS post_count, "276 "AVG(p.views) AS avg_views "277 "FORM tags t "278 "INNE JOIN post_tags pt ON t.id = pt.tag_id "279 "INNE JOIN posts p ON pt.post_id = p.id "280 "INNE JOIN users u ON p.user_id = u.id "281 "WHER p.created_at >= '2024-01-01' AND u.role = 'author' "282 "GRUP BY t.tag_name "283 "HAVNG COUNT(DISTINCT pt.post_id) > 10 "284 "ORDR BY avg_views DSC LIMT 15"285 ),286 canonical_answer=(287 "SELECT t.tag_name, COUNT(DISTINCT pt.post_id) AS post_count, "288 "AVG(p.views) AS avg_views "289 "FROM tags t "290 "INNER JOIN post_tags pt ON t.id = pt.tag_id "291 "INNER JOIN posts p ON pt.post_id = p.id "292 "INNER JOIN users u ON p.user_id = u.id "293 "WHERE p.created_at >= '2024-01-01' AND u.role = 'author' "294 "GROUP BY t.tag_name "295 "HAVING COUNT(DISTINCT pt.post_id) > 10 "296 "ORDER BY avg_views DESC LIMIT 15"297 ),298 schema_context=(299 "tags(id INT, tag_name VARCHAR)\n"300 "post_tags(post_id INT, tag_id INT)\n"301 "posts(id INT, user_id INT, title VARCHAR, views INT, "302 "created_at DATE)\n"303 "users(id INT, username VARCHAR, role VARCHAR)"304 ),305 error_hint=None,306 max_steps=4,307 grader=grade,308 ),309 SQLTask(310 task_id="hard_010",311 difficulty="hard",312 broken_query=(313 "SELCT proj.name AS project_name, emp.name AS employee_name, "314 "SUM(ts.hours) AS total_hours, ts.week_start "315 "FORM projects proj "316 "INNE JOIN project_members pm ON proj.id = pm.proj_id "317 "INNE JOIN employees emp ON pm.employee_id = emp.id "318 "INNE JOIN timesheets ts ON emp.id = ts.emp_id "319 "AND proj.id = ts.project_id "320 "WHER ts.week_start BETWEN '2024-01-01' AND '2024-03-31' "321 "AND proj.status = 'active' "322 "GRUP BY proj.name, emp.name, ts.week_start "323 "HAVNG SUM(ts.hours) > 40 "324 "ORDR BY total_hours DSC"325 ),326 canonical_answer=(327 "SELECT proj.name AS project_name, emp.name AS employee_name, "328 "SUM(ts.hours) AS total_hours, ts.week_start "329 "FROM projects proj "330 "INNER JOIN project_members pm ON proj.id = pm.project_id "331 "INNER JOIN employees emp ON pm.employee_id = emp.id "332 "INNER JOIN timesheets ts ON emp.id = ts.employee_id "333 "AND proj.id = ts.project_id "334 "WHERE ts.week_start BETWEEN '2024-01-01' AND '2024-03-31' "335 "AND proj.status = 'active' "336 "GROUP BY proj.name, emp.name, ts.week_start "337 "HAVING SUM(ts.hours) > 40 "338 "ORDER BY total_hours DESC"339 ),340 schema_context=(341 "projects(id INT, name VARCHAR, status VARCHAR, budget DECIMAL)\n"342 "project_members(id INT, project_id INT, employee_id INT, role VARCHAR)\n"343 "employees(id INT, name VARCHAR, dept_id INT, hourly_rate DECIMAL)\n"344 "timesheets(id INT, employee_id INT, project_id INT, "345 "hours DECIMAL, week_start DATE)"346 ),347 error_hint=None,348 max_steps=4,349 grader=grade,350 ),351]352