Kalletlamadhav/sql-optimization-env

1# tasks/easy/pds_select_star.py2from pathlib import Path3from tasks.base_task import BaseTask4 5# FIX: Added missing import + schema_ddl field (was entirely absent in PDF)6_schema_file = Path('data/schemas/pds_schema.sql')7_schema_ddl = _schema_file.read_text() if _schema_file.exists() else (8    "CREATE TABLE ration_card_beneficiaries ("9    "card_id TEXT PRIMARY KEY, household_head TEXT NOT NULL, "10    "state_code CHAR(2) NOT NULL, district_code CHAR(3) NOT NULL, "11    "block_code CHAR(5), village_code CHAR(7), "12    "card_type TEXT NOT NULL, members_count INTEGER DEFAULT 1, "13    "aadhar_linked INTEGER DEFAULT 0, mobile_number TEXT, "14    "created_at DATE, last_updated DATE);"15)16 17TASK = BaseTask(18    task_id='pds_select_star',19    goal=(20        'A state government officer wants a count of BPL card holders by district. '21        'The query fetches all columns from 80,000 rows but only needs card_id and district_code. '22        'Rewrite it to select only required columns and add appropriate filter.'23    ),24    slow_query="""25        SELECT * FROM ration_card_beneficiaries26        WHERE card_type = 'BPL'27    """,28    expected_pattern='SELECT_STAR',29    tables=['ration_card_beneficiaries'],30    schema_ddl=_schema_ddl,31    difficulty='easy',32    curriculum_level=1,33    hint='SELECT * fetches all 12 columns. You only need card_id and district_code.',34    reference_fix="""35        SELECT card_id, district_code36        FROM ration_card_beneficiaries37        WHERE card_type = 'BPL'38    """39)