Kalletlamadhav/sql-optimization-env
0
1�
2 ��i@ � � � d dl mZ d dlmZ ed� � Ze� � � re� � � ndZ eddddd ged3d d d
��4 �5 ZdS )� )�Path)�BaseTaskzdata/schemas/gst_schema.sqla� -- Schema file not found. Run from project root.6CREATE TABLE gst_invoice_records (invoice_id TEXT PRIMARY KEY, gstin_supplier TEXT NOT NULL, gstin_buyer TEXT NOT NULL, invoice_date DATE NOT NULL, invoice_type TEXT DEFAULT 'B2B', taxable_value DECIMAL(15,2), cgst_amount DECIMAL(10,2), sgst_amount DECIMAL(10,2), igst_amount DECIMAL(10,2), cess_amount DECIMAL(10,2) DEFAULT 0.0, state_code CHAR(2) NOT NULL, hsn_code TEXT, filing_status TEXT DEFAULT 'FILED', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);�gst_missing_indexz�A supplier needs all their invoices for the last quarter. The current query does a full table scan across 100,000 rows. Optimize it so it uses an index on gstin_supplier and runs in <50ms.z�7 SELECT invoice_id, invoice_date, taxable_value, igst_amount, state_code8 FROM gst_invoice_records9 WHERE gstin_supplier = '27AABCU9603R1ZX'10 AND invoice_date >= '2025-01-01'11 ORDER BY invoice_date DESC12 �
MISSING_INDEX�gst_invoice_records�easy� uO The WHERE clause filters on gstin_supplier — check if that column is indexed.ab 13 CREATE INDEX idx_gst_supplier_date14 ON gst_invoice_records(gstin_supplier, invoice_date DESC);15 16 SELECT invoice_id, invoice_date, taxable_value, igst_amount, state_code17 FROM gst_invoice_records18 WHERE gstin_supplier = '27AABCU9603R1ZX'19 AND invoice_date >= '2025-01-01'20 ORDER BY invoice_date DESC21 )22�task_id�goal�23slow_query�expected_pattern�tables�24schema_ddl�25difficulty�curriculum_level�hint�
reference_fixN) �pathlibr �tasks.base_taskr � _schema_file�exists� read_text� _schema_ddl�TASK� � �HC:\open_env_sql_opt\sql-optimization-env\tasks\easy\gst_missing_index.py�<module>r s� �� � � � � � � $� $� $� $� $� $� �t�1�2�2� �*6�*=�*=�*?�*?� 26�l�$�$�&�&�&�7� � �x� � O�� %� !� "���� Z� �) � � ���r