Kalletlamadhav/sql-optimization-env

1# tasks/easy/gst_missing_index.py2from pathlib import Path3from tasks.base_task import BaseTask4 5# FIX: Use Path + lazy fallback instead of bare open() which crashes at import if CWD is wrong6_schema_file = Path('data/schemas/gst_schema.sql')7_schema_ddl = _schema_file.read_text() if _schema_file.exists() else (8    "-- Schema file not found. Run from project root.\n"9    "CREATE TABLE gst_invoice_records (invoice_id TEXT PRIMARY KEY, "10    "gstin_supplier TEXT NOT NULL, gstin_buyer TEXT NOT NULL, "11    "invoice_date DATE NOT NULL, invoice_type TEXT DEFAULT 'B2B', "12    "taxable_value DECIMAL(15,2), cgst_amount DECIMAL(10,2), "13    "sgst_amount DECIMAL(10,2), igst_amount DECIMAL(10,2), "14    "cess_amount DECIMAL(10,2) DEFAULT 0.0, state_code CHAR(2) NOT NULL, "15    "hsn_code TEXT, filing_status TEXT DEFAULT 'FILED', "16    "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);"17)18 19TASK = BaseTask(20    task_id='gst_missing_index',21    goal=(22        'A supplier needs all their invoices for the last quarter. '23        'The current query does a full table scan across 100,000 rows. '24        'Optimize it so it uses an index on gstin_supplier and runs in <50ms.'25    ),26    slow_query="""27        SELECT invoice_id, invoice_date, taxable_value, igst_amount, state_code28        FROM gst_invoice_records29        WHERE gstin_supplier = '27AABCU9603R1ZX'30        AND invoice_date >= '2025-01-01'31        ORDER BY invoice_date DESC32    """,33    expected_pattern='MISSING_INDEX',34    tables=['gst_invoice_records'],35    schema_ddl=_schema_ddl,36    difficulty='easy',37    curriculum_level=2,38    hint='The WHERE clause filters on gstin_supplier — check if that column is indexed.',39    reference_fix="""40        CREATE INDEX idx_gst_supplier_date41        ON gst_invoice_records(gstin_supplier, invoice_date DESC);42 43        SELECT invoice_id, invoice_date, taxable_value, igst_amount, state_code44        FROM gst_invoice_records45        WHERE gstin_supplier = '27AABCU9603R1ZX'46        AND invoice_date >= '2025-01-01'47        ORDER BY invoice_date DESC48    """49)