Leetcode 2408. Design SQL

# Join Strategy Examples
# INNER JOIN: Only matching records from both tables
inner_query = """
    SELECT o.order_id, c.customer_name
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id
"""
# LEFT JOIN: All orders, even without customer match
left_query = """
    SELECT o.order_id, c.customer_name
    FROM orders o
    LEFT JOIN customers c ON o.customer_id = c.customer_id
"""
# Many-to-many with junction table
many_to_many_query = """
    SELECT s.student_name, c.course_name
    FROM students s
    INNER JOIN enrollments e ON s.student_id = e.student_id
    INNER JOIN courses c ON e.course_id = c.course_id
"""

# Aggregation and Grouping Logic
from typing import List, Dict, Any
def build_aggregation_query(
    table: str,
    group_by_cols: List[str],
    agg_functions: Dict[str, str],
    having_clause: str = None
) -> str:
    """Build SQL query with GROUP BY and aggregation functions."""
    agg_exprs = [f"{func}({col}) AS {alias}" 
                 for alias, (func, col) in agg_functions.items()]
    
    query = f"SELECT {', '.join(group_by_cols + agg_exprs)} FROM {table}"
    query += f" GROUP BY {', '.join(group_by_cols)}"
    
    if having_clause:
        query += f" HAVING {having_clause}"
    
    return query

# Using pandas for window function simulation
import pandas as pd
def get_top_per_group(df, partition_col, order_col, ascending=False):
    """Assign rank within each group and filter top records."""
    df['rank'] = df.groupby(partition_col)[order_col].rank(
        method='first', ascending=ascending
    )
    return df[df['rank'] == 1].drop(columns=['rank'])
# Alternative with row_number equivalent
def get_top_with_row_number(df, partition_col, order_col, ascending=False):
    """Use sort + groupby for ROW_NUMBER() behavior."""
    sorted_df = df.sort_values([partition_col, order_col], ascending=[True, ascending])
    sorted_df['rn'] = sorted_df.groupby(partition_col).cumcount() + 1
    return sorted_df[sorted_df['rn'] == 1].drop(columns=['rn'])

# NULL handling in SQL query construction
def handle_nulls_and_duplicates(query_parts):
    # Replace NULLs with defaults using COALESCE
    select_with_coalesce = f"COALESCE({query_parts['column']}, {query_parts['default']}) AS {query_parts['alias']}"
    
    # Filter out NULLs explicitly
    where_not_null = f"{query_parts['column']} IS NOT NULL"
    
    # Handle duplicates with tie-breaking ORDER BY
    order_with_tiebreak = f"ORDER BY {query_parts['primary_sort']} DESC, {query_parts['tiebreaker']} ASC"
    
    # COUNT with NULL awareness (COUNT(col) vs COUNT(*))
    count_non_null = f"COUNT({query_parts['column']})"  # Ignores NULLs
    count_all = "COUNT(*)"  # Includes NULLs
    
    return {
        'select': select_with_coalesce,
        'where': where_not_null,
        'order': order_with_tiebreak,
        'count_non_null': count_non_null,
        'count_all': count_all
    }