Kalletlamadhav/sql-optimization-env
0
1�
2 ���i�3 � � � d dl mZ d dlmZ ed� � Ze� � � re� � � ndZ eddddd d4ged d d
d��5 �6 ZdS )� )�Path)�BaseTaskzdata/schemas/gst_schema.sqla� CREATE 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);CREATE TABLE gst_invoice_items (item_id INTEGER PRIMARY KEY AUTOINCREMENT, invoice_id TEXT NOT NULL REFERENCES gst_invoice_records(invoice_id), item_description TEXT, hsn_code TEXT, quantity DECIMAL(10,3), unit_value DECIMAL(12,2), taxable_value DECIMAL(15,2), gst_rate DECIMAL(5,2));�gst_n_plus_onea An accountant wants each GST invoice with its total item count and taxable value. The current query uses a correlated subquery that executes once per invoice row. With 50,000 invoices, this means 50,000 separate database calls. Rewrite using a single JOIN with GROUP BY.a� 7 SELECT8 i.invoice_id,9 i.gstin_supplier,10 i.invoice_date,11 (SELECT COUNT(*) FROM gst_invoice_items it12 WHERE it.invoice_id = i.invoice_id) AS item_count,13 (SELECT SUM(it2.taxable_value) FROM gst_invoice_items it214 WHERE it2.invoice_id = i.invoice_id) AS total_taxable15 FROM gst_invoice_records i16 WHERE i.state_code = '27'17 AND i.invoice_date >= '2025-01-01'18 �19N_PLUS_ONE�gst_invoice_records�gst_invoice_items�medium� ue Each correlated subquery runs once per outer row — replace both with a single LEFT JOIN + GROUP BY.a� 20 SELECT21 i.invoice_id, i.gstin_supplier, i.invoice_date,22 COUNT(it.item_id) AS item_count,23 SUM(it.taxable_value) AS total_taxable24 FROM gst_invoice_records i25 LEFT JOIN gst_invoice_items it ON it.invoice_id = i.invoice_id26 WHERE i.state_code = '27'27 AND i.invoice_date >= '2025-01-01'28 GROUP BY i.invoice_id, i.gstin_supplier, i.invoice_date29 )30�task_id�goal�31slow_query�expected_pattern�tables�32schema_ddl�33difficulty�curriculum_level�hint�
reference_fixN) �pathlibr �tasks.base_taskr � _schema_file�exists� read_text� _schema_ddl�TASK� � �GC:\open_env_sql_opt\sql-optimization-env\tasks\medium\gst_n_plus_one.py�<module>r s� �� � � � � � � $� $� $� $� $� $� �t�1�2�2� �*6�*=�*=�*?�*?� �l�$�$�&�&�&�
� �" �x� � 5�34 � "� !�#6� 7���� p�35�7&� &� &���r