Problem Statement
You are given 4 JSON files named products, reviews, users, transactions that contains information about various products sold by an e-commerce company, user information, reviews written by users and all transactions made on the products.
Your task is to develop a SQL query engine that can execute SQL queries on JSON data. The engine is expected to handle the following types of queries -
	•	SELECT
	•	WHERE
	•	JOIN (INNER type only)
	•	GROUP BY
	•	ORDER BY
	•	LIMIT
	•	Aggregate functions (COUNT(*), AVG, SUM, MIN, MAX)
	•	
The SQL query will be provided as input will always follow the syntax below. The way to read the syntax definition is - square brackets [ ] indicate optional presence of that element in the query and curly braces { } indicates mandatory where applicable. Pipe | separated values indicates any one of the options. ... indicates one or many such elements can exist.

`SELECT 
    {table_alias | join_table_alias}.col_name AS column_alias, 
    … 
FROM table table_alias 
[ JOIN table join_table_alias on joinexpr [AND joinexpr ... ] ] … 
[WHERE expr [AND expr ... ] ] 
[GROUP BY {table_alias.col_name, … }] 
ORDER BY {column_alias, … } { ASC | DESC } 
[LIMIT n]`

The joinexpr in JOIN can have multiple conditions, however it will be always AND clauses. One condition will always be of form below,

`{table_alias | join_table_alias}.col_name = {value | {table_alias | join_table_alias}.col_name}}`
The expr in WHERE can have multiple conditions, however it will be always AND clauses. One condition will always be of form below,

`{table_alias | join_table_alias}.col_name {operator} {value}`
The supported operators in WHERE expr for the engine are the ones below only:
	•	= (equals)
	•	<> (not equals)
	•	> (greater than)
	•	>= (greater than or equal to )
	•	< (less than)
	•	<= (less than or equal to)

Supported aggregations:
	1	COUNT(*)
	◦	provides the count of rows in the result set.
	◦	Result must be integer type
	2	SUM( {table_alias|join_table_alias}.col_name )
	◦	Only one column supported
	◦	For Double column, result must be rounded to 2 decimal places and always have 2 decimal places
	3	AVG( {table_alias|join_table_alias}.col_name )
	◦	Only one column supported
	◦	For Double column, result must be rounded to 2 decimal places and always have 2 decimal places
	4	MIN( {table_alias|join_table_alias}.col_name )
	◦	Only one column supported
	◦	For Double column, result must be rounded to 2 decimal places and always have 2 decimal places
	5	MAX( {table_alias|join_table_alias}.col_name )
	◦	Only one column supported
	◦	For Double column, result must be rounded to 2 decimal places and always have 2 decimal places
The engine will be tested with four JSON files: products, reviews, users, transactions with each file representing a table name.

Input Format

Data Files
Data Files are available in your program current directory :
	•	products_public.json
	•	users_public.json
	•	reviews_public.json
	•	transactions_public.json

Sample Input
The input will be the below string available in standard input:
SELECT p.id AS product_id, p.name AS product_name FROM products p WHERE p.price > 10.00 ORDER BY product_id ASC

Reading the Input
You can read the input SQL query as standard input.
To read the data files (e.g. products_public.json, user_public.json ... etc) , you can use some of the below code snippets:

Python
` //read from standard input line = input()
// Get the current directory  current_directory = os.getcwd()  // Get all files in the directory  files = os.listdir(current_directory)  `

Constraints
Product attributes:
	•	id: Unique product id, type: int32, constraint: unique in file
	•	name: Product name, type: string, constraint: none
	•	category: Product category, type: string, constraint: none
	•	price: Price of the product, type: double, constraint: positive with 2 decimal precision
User attributes:
	•	user_id: Unique user id, type: int32, constraint: unique in file
	•	name: User name, type: string, constraint: none
	•	age: Age of the user, type: int32, constraint: positive number
	•	location: City of the user, type: string, constraint: none
Review attributes:
	•	product_id: Id of product, type: int32, constraint: part of unique key in file
	•	user_id: Id of user, type: int32, constraint: part of unique key in file
	•	rating: Rating by user, type: int32, constraint: 0 - 5 both inclusive
	•	comment: Comment by user, type: string, constraint: none
Transaction attributes:
	•	transaction_id: Unique id of transaction, type: int32, constraint: unique in file
	•	product_id: Id of product, type: int32, constraint: none
	•	user_id: Id of user, type: int32, constraint: none
	•	price: Price at which 1 unit of product was sold, type: double, constraint: positive with 2 decimal precision
	•	quantity: Total units bought, type: int32, constraint: positive number
	•	date: Date when transaction happened, type: string, constraint: format YYYY-MM-DD
	•	total_amount: Quantity * price, type: double, constraint: always greater than 0

Output Format
Guidelines
	•	The output must be a string
	•	Since we expect several rows and columns, essentially a tabular output, please use comma separator with no spaces before and after to separate out columns and line separator \n to separate out rows.
	•	the first line of the output must be the column names expected in the result, separated by comma with no spaces before and after. The order of the column names also matters and must match with what is provided in SQL query input.

Sample Input 0

SELECT p.id AS product_id, p.name AS product_name FROM products p WHERE p.price > 95.00 ORDER BY product_id ASC

Sample Output 0

product_id,product_name\n6,product6\n11,product11\n62,product62\n67,product67\n78,product78

Explanation 0

    The column names appear as the first line in output. They are in the order in what appears in SELECT clause in SQL query input, separated by comma with no spaces before or after.
    Rest follows the result from the products table

Sample Input 1

SELECT u.user_id AS user_id, u.name AS user_name FROM users u WHERE u.age > 48 AND u.location = 'Delhi' ORDER BY user_id DESC

Sample Output 1

user_id,user_name\n9943,user9943\n9493,user9493\n9364,user9364\n8650,user8650\n8515,user8515\n8495,user8495\n6849,user6849\n6387,user6387\n6090,user6090\n4100,user4100\n3660,user3660\n3390,user3390\n2908,user2908\n2773,user2773\n2272,user2272\n373,user373

Sample Input 2

SELECT p.category AS product_category, COUNT(*) AS product_counts FROM products p GROUP BY p.category ORDER BY product_counts DESC LIMIT 1

Sample Output 2

product_category,product_counts\nBOOKS,32

For the above problem the below code fails fix it(runtime error)
import json
import os
import sys
import re
from collections import defaultdict

# Helper Functions
def load_json_file(file_name):
    data = []
    with open(file_name, 'r') as f:
        for line in f:
            data.append(json.loads(line))
    return data

def parse_query(query):
    # Initialize parts
    select_columns = []
    from_table = None
    from_alias = None
    joins = []
    where_conditions = []
    group_by = []
    order_by = []
    order = 'ASC'
    limit = None

    # Normalize query by removing extra spaces
    query = ' '.join(query.strip().split())

    # Regex patterns with corrected logic
    join_pattern = re.compile(
        r'JOIN\s+(\w+)\s+(\w+)\s+ON\s+(.*?)(?=\s+WHERE|\s+GROUP\s+BY|\s+ORDER\s+BY|\s+LIMIT|$)',
        re.IGNORECASE
    )
    where_pattern = re.compile(
        r'WHERE\s+(.*?)(?=\s+GROUP\s+BY|\s+ORDER\s+BY|\s+LIMIT|$)',
        re.IGNORECASE
    )
    group_by_pattern = re.compile(
        r'GROUP\s+BY\s+(.*?)(?=\s+ORDER\s+BY|\s+LIMIT|$)',
        re.IGNORECASE
    )
    order_by_pattern = re.compile(
        r'ORDER\s+BY\s+(.*?)(?=\s+LIMIT|$)',
        re.IGNORECASE
    )
    limit_pattern = re.compile(
        r'LIMIT\s+(\d+)', re.IGNORECASE
    )

    # Extract SELECT and FROM parts
    select_from_split = re.split(r'\s+FROM\s+', query, flags=re.IGNORECASE)
    if len(select_from_split) != 2:
        raise ValueError("Invalid SQL query: Missing FROM clause.")

    select_part = select_from_split[0][len('SELECT '):].strip()
    from_rest = select_from_split[1].strip()

    # Parse SELECT columns
    select_columns = [col.strip() for col in select_part.split(',')]

    # Extract FROM table and alias
    from_split = from_rest.split(None, 2)
    from_table = from_split[0]
    if len(from_split) >= 2 and from_split[1].upper() != 'JOIN':
        from_alias = from_split[1]
        remaining = from_split[2] if len(from_split) > 2 else ''
    else:
        from_alias = from_table
        remaining = from_rest[len(from_table):].strip()

    # Handle JOINS
    for match in join_pattern.finditer(from_rest):
        join_table, join_alias, join_expr = match.groups()
        conditions = [cond.strip() for cond in join_expr.split('AND')]
        joins.append({
            'table': join_table,
            'alias': join_alias,
            'conditions': conditions
        })

    # Handle WHERE
    where_match = where_pattern.search(from_rest)
    if where_match:
        where_expr = where_match.group(1).strip()
        conditions = [cond.strip() for cond in where_expr.split('AND')]
        where_conditions = conditions

    # Handle GROUP BY
    group_by_match = group_by_pattern.search(from_rest)
    if group_by_match:
        group_by_expr = group_by_match.group(1).strip()
        group_by = [col.strip() for col in group_by_expr.split(',')]

    # Handle ORDER BY
    order_by_match = order_by_pattern.search(from_rest)
    if order_by_match:
        order_by_expr = order_by_match.group(1).strip()
        # Check if ASC or DESC is specified
        order_match = re.search(r'\b(ASC|DESC)\b', order_by_expr, re.IGNORECASE)
        if order_match:
            order = order_match.group(1).upper()
            # Remove the ASC/DESC part from the order_by_expr
            order_by_cols = order_by_expr[:order_match.start()].strip()
            order_by = [col.strip() for col in order_by_cols.split(',')]
        else:
            order_by = [col.strip() for col in order_by_expr.split(',')]

    # Handle LIMIT
    limit_match = limit_pattern.search(from_rest)
    if limit_match:
        limit = int(limit_match.group(1))

    return {
        'select': select_columns,
        'from': {'table': from_table, 'alias': from_alias},
        'joins': joins,
        'where': where_conditions,
        'group_by': group_by,
        'order_by': order_by,
        'order': order,
        'limit': limit
    }

def apply_where(row, where_conditions):
    def evaluate_condition(row, condition):
        # Supported operators: =, <>, >, >=, <, <=
        match = re.match(r'(\w+\.\w+)\s*(=|<>|>|>=|<|<=)\s*(.+)', condition)
        if not match:
            return False
        left, op, right = match.groups()
        left_val = row.get(left)

        # Determine if right is a column or a value
        # Check for string literals
        if (right.startswith("'") and right.endswith("'")) or (right.startswith('"') and right.endswith('"')):
            right_val = right[1:-1]
        else:
            # Attempt to parse as number
            try:
                if '.' in right:
                    right_val = float(right)
                else:
                    right_val = int(right)
            except ValueError:
                # It's a column reference
                right_val = row.get(right)

        # Compare based on operator
        if op == '=':
            return left_val == right_val
        elif op == '<>':
            return left_val != right_val
        elif op == '>':
            return left_val > right_val
        elif op == '>=':
            return left_val >= right_val
        elif op == '<':
            return left_val < right_val
        elif op == '<=':
            return left_val <= right_val
        return False

    return all(evaluate_condition(row, cond) for cond in where_conditions)

def hash_join(left_data, right_data, join_conditions, left_alias, right_alias):
    """
    Perform a hash-based inner join between left_data and right_data using the join_conditions.
    Assumes all join conditions are equality conditions.
    """
    # For simplicity, handle single join condition
    # Extendable for multiple conditions by intersecting the join keys

    # Extract join keys
    join_keys = []
    for cond in join_conditions:
        match = re.match(r'(\w+\.\w+)\s*=\s*(\w+\.\w+)', cond)
        if match:
            left_key, right_key = match.groups()
            join_keys.append((left_key, right_key))

    if not join_keys:
        # No valid join conditions found
        return []

    # For multi-key joins, we can combine keys into a tuple
    # Here, we assume all keys are equality conditions between two columns

    # Build hash map for the right table
    hash_map = defaultdict(list)
    for row in right_data:
        key = tuple(row[join_key] for _, join_key in join_keys)
        hash_map[key].append(row)

    # Iterate over left data and find matching rows in hash_map
    joined_data = []
    for left_row in left_data:
        key = tuple(left_row[join_key] for join_key, _ in join_keys)
        if key in hash_map:
            for right_row in hash_map[key]:
                # Merge rows
                merged_row = {}
                merged_row.update(left_row)
                merged_row.update(right_row)
                joined_data.append(merged_row)

    return joined_data

def apply_select(data, select_columns, group_by):
    result = []
    headers = []
    select_parser = []

    # Parse select columns
    for col in select_columns:
        # Check for aggregation functions
        agg_match = re.match(r'(COUNT\(\s*\*\s*\)|SUM\(\s*\w+\.\w+\s*\)|AVG\(\s*\w+\.\w+\s*\)|MIN\(\s*\w+\.\w+\s*\)|MAX\(\s*\w+\.\w+\s*\))\s+AS\s+(\w+)', col, re.IGNORECASE)
        if agg_match:
            agg_func = agg_match.group(1).upper()
            agg_alias = agg_match.group(2)
            select_parser.append({'type': 'aggregation', 'func': agg_func, 'alias': agg_alias})
            headers.append(agg_alias)
        else:
            # Assume format: alias.col AS alias
            select_match = re.match(r'(\w+\.\w+)\s+AS\s+(\w+)', col, re.IGNORECASE)
            if select_match:
                expr = select_match.group(1)
                alias = select_match.group(2)
                select_parser.append({'type': 'select', 'expr': expr, 'alias': alias})
                headers.append(alias)
            else:
                raise ValueError(f"Invalid SELECT column format: {col}")

    # Append headers in order
    result.append(','.join(headers))

    # If there are aggregations, perform grouping and aggregation
    if any(col['type'] == 'aggregation' for col in select_parser):
        # Grouping
        grouped = defaultdict(list)
        if group_by:
            for row in data:
                key = tuple(row[col] for col in group_by)
                grouped[key].append(row)
        else:
            grouped[None] = data  # Single group

        # Perform aggregations
        for key, rows in grouped.items():
            output_row = []
            for sel in select_parser:
                if sel['type'] == 'select':
                    if group_by:
                        # Fetch the group key value
                        idx = group_by.index(sel['expr']) if sel['expr'] in group_by else -1
                        if idx != -1 and key is not None:
                            val = key[idx]
                        else:
                            val = rows[0].get(sel['expr'])
                    else:
                        val = rows[0].get(sel['expr'])
                    if isinstance(val, float):
                        val = f"{val:.2f}"
                    output_row.append(str(val))
                elif sel['type'] == 'aggregation':
                    func = sel['func']
                    if func.startswith('COUNT'):
                        val = len(rows)
                    else:
                        field = re.match(r'\w+\.(\w+)', func.split('(')[1]).group(1)
                        values = [row.get(f"{field}") for row in rows]
                        if func.startswith('SUM'):
                            val = sum(values)
                            val = f"{val:.2f}" if isinstance(val, float) else val
                        elif func.startswith('AVG'):
                            val = sum(values) / len(values) if len(values) > 0 else 0
                            val = f"{val:.2f}" if isinstance(val, float) else val
                        elif func.startswith('MIN'):
                            val = min(values)
                            val = f"{val:.2f}" if isinstance(val, float) else val
                        elif func.startswith('MAX'):
                            val = max(values)
                            val = f"{val:.2f}" if isinstance(val, float) else val
                        else:
                            val = '0'  # Default fallback
                    output_row.append(str(val))
            result.append(','.join(output_row))
    else:
        # No aggregations, just select the required columns
        for row in data:
            selected_row = []
            for sel in select_parser:
                val = row.get(sel['expr'])
                if isinstance(val, float):
                    val = f"{val:.2f}"
                selected_row.append(str(val))
            result.append(','.join(selected_row))

    return result

def apply_order_by(data, order_by, order):
    if not order_by or len(data) <= 1:
        return data

    headers = data[0].split(',')
    indices = []
    for col in order_by:
        if col not in headers:
            raise ValueError(f"'{col}' is not in list")
        indices.append(headers.index(col))

    # Sort the rows based on the specified columns
    sorted_rows = sorted(
        data[1:],
        key=lambda row: tuple(
            try_parse(row.split(',')[idx]) for idx in indices
        ),
        reverse=(order == 'DESC')
    )
    return [data[0]] + sorted_rows

def try_parse(value):
    # Attempt to convert to int or float
    try:
        if '.' in value:
            return float(value)
        return int(value)
    except ValueError:
        return value

def apply_limit(data, limit):
    if limit is None:
        return data
    return data[:limit+1]  # +1 to include header

def process_select(query_components, tables):
    # Extract components
    select_cols = query_components['select']
    from_table = query_components['from']['table']
    from_alias = query_components['from']['alias']
    joins = query_components['joins']
    where = query_components['where']
    group_by = query_components['group_by']
    order_by = query_components['order_by']
    order = query_components['order']
    limit = query_components['limit']

    # Initialize aliases
    aliases = {from_alias: from_table}

    # Start with FROM table
    data = []
    for row in tables[from_table]:
        prefixed_row = {}
        for k, v in row.items():
            prefixed_row[f"{from_alias}.{k}"] = v
        data.append(prefixed_row)

    # Apply JOINS using hash-based join
    for join in joins:
        join_table = join['table']
        join_alias = join['alias']
        join_conditions = join['conditions']
        aliases[join_alias] = join_table

        # Prepare right table with prefixed columns
        right_data = []
        for row in tables[join_table]:
            prefixed_row = {}
            for k, v in row.items():
                prefixed_row[f"{join_alias}.{k}"] = v
            right_data.append(prefixed_row)

        # Perform hash-based join
        data = hash_join(data, right_data, join_conditions, from_alias, join_alias)

    # Apply WHERE filters
    if where:
        data = [row for row in data if apply_where(row, where)]

    # Handle SELECT and Aggregations
    selected_data = apply_select(data, select_cols, group_by)

    # Apply ORDER BY
    if order_by:
        selected_data = apply_order_by(selected_data, order_by, order)

    # Apply LIMIT
    selected_data = apply_limit(selected_data, limit)

    return '\n'.join(selected_data)

def main():
    # Read SQL query from standard input
    query = sys.stdin.read().strip()

    # Parse the query
    try:
        query_components = parse_query(query)
    except ValueError as ve:
        print(f"Error parsing query: {ve}")
        sys.exit(1)
        
    # Load JSON data
    current_directory = os.getcwd()
    files = os.listdir(current_directory)
    tables = {}
    table_names = {
        'products': 'products_public.json',
        'users': 'users_public.json',
        'reviews': 'reviews_public.json',
        'transactions': 'transactions_public.json'
    }
    for table, file_name in table_names.items():
        if file_name in files:
            tables[table] = load_json_file(file_name)
        else:
            tables[table] = []

    # Process SELECT query
    try:
        result = process_select(query_components, tables)
    except ValueError as ve:
        print(f"Error processing query: {ve}")
        sys.exit(1)

    # Output the result
    print(result.replace("\n","\\n"))

if __name__ == "__main__":
    main()