Kalletlamadhav/sql-optimization-env
0
1�
2 ���i� �
�x � d dl mZ eddddg d� ed� � � � � dd d d3d � � � Zd4S )
� )�BaseTask�mgnrega_schema_eu A state auditor needs the annual MGNREGA payment compliance report for FY 2024-25: for each gram panchayat, show total workers, total days worked, total wages due, total wages actually paid, and the payment gap percentage. The current query is an unbounded aggregation — it JOINs three tables with no date filter, aggregates ALL historical rows (including pre-FY rows), and uses two uncorrelated subqueries to compute totals that could be done in a single pass. Additionally, the query performs CAST(worker_id AS TEXT) on an already-TEXT column, triggering an implicit cast that disables index usage. Fix all patterns: add fiscal year filters, collapse subqueries into one GROUP BY pass, remove the unnecessary CAST, and add covering indexes on the attendance and payments tables.a� 5 SELECT6 w.gram_panchayat,7 COUNT(DISTINCT w.worker_id) AS total_workers,8 (9 SELECT SUM(a.days_worked)10 FROM mgnrega_attendance a11 WHERE CAST(a.worker_id AS TEXT) = CAST(w.worker_id AS TEXT)12 ) AS total_days,13 (14 SELECT SUM(p.amount_due)15 FROM mgnrega_payments p16 WHERE CAST(p.worker_id AS TEXT) = CAST(w.worker_id AS TEXT)17 ) AS total_amount_due,18 (19 SELECT SUM(p.amount_paid)20 FROM mgnrega_payments p21 WHERE CAST(p.worker_id AS TEXT) = CAST(w.worker_id AS TEXT)22 ) AS total_amount_paid23 FROM mgnrega_workers w24 GROUP BY w.gram_panchayat25 ORDER BY w.gram_panchayat26 �UNBOUNDED_AGGREGATION)�mgnrega_workers�mgnrega_attendance�mgnrega_paymentsz data/schemas/mgnrega_schema.sql�hard� Nu� 27 -- Step 1: Add covering indexes28 CREATE INDEX idx_att_worker_date29 ON mgnrega_attendance(worker_id, work_date, days_worked);30 31 CREATE INDEX idx_pay_worker_month32 ON mgnrega_payments(worker_id, payment_month, amount_due, amount_paid);33 34 CREATE INDEX idx_worker_gp35 ON mgnrega_workers(gram_panchayat, worker_id);36 37 -- Step 2: Single-pass aggregation with fiscal year filter38 -- FY 2024-25: April 2024 – March 202539 WITH attendance_agg AS (40 SELECT41 worker_id,42 SUM(days_worked) AS total_days43 FROM mgnrega_attendance44 WHERE work_date BETWEEN '2024-04-01' AND '2025-03-31'45 GROUP BY worker_id46 ),47 payment_agg AS (48 SELECT49 worker_id,50 SUM(amount_due) AS total_due,51 SUM(amount_paid) AS total_paid52 FROM mgnrega_payments53 WHERE payment_month BETWEEN '2024-04' AND '2025-03'54 GROUP BY worker_id55 )56 SELECT57 w.gram_panchayat,58 COUNT(DISTINCT w.worker_id) AS total_workers,59 COALESCE(SUM(aa.total_days), 0) AS total_days_worked,60 COALESCE(SUM(pa.total_due), 0) AS total_amount_due,61 COALESCE(SUM(pa.total_paid), 0) AS total_amount_paid,62 CASE63 WHEN COALESCE(SUM(pa.total_due), 0) = 0 THEN 0.064 ELSE ROUND(65 100.0 * (COALESCE(SUM(pa.total_due), 0) - COALESCE(SUM(pa.total_paid), 0))66 / SUM(pa.total_due), 2)67 END AS payment_gap_pct68 FROM mgnrega_workers w69 LEFT JOIN attendance_agg aa ON aa.worker_id = w.worker_id70 LEFT JOIN payment_agg pa ON pa.worker_id = w.worker_id71 GROUP BY w.gram_panchayat72 ORDER BY payment_gap_pct DESC;73 ) �task_id�goal�74slow_query�expected_pattern�tables�75schema_ddl�76difficulty�curriculum_level� max_steps�hint�
reference_fix)�tasks.base_taskr �open�read�TASK� � �GC:\open_env_sql_opt\sql-optimization-env\tasks\hard\mgnrega_schema_e.py�<module>r s| �� $� $� $� $� $� $��x� � g��. -� H� H� H��t�5�6�6�;�;�=�=����
�/�Y\� \� \���r