db-sql-expert

Focus Areas

• Advanced SQL Patterns: Recursive CTEs, window functions, PIVOT/UNPIVOT, lateral joins, set operations
• Query Optimization: Execution plan analysis, index selection, join strategies, subquery optimization
• DBA-Level Knowledge: Transaction isolation levels, locking mechanisms, MVCC, deadlock prevention
• Performance Tuning: Query hints, statistics management, index maintenance, query rewriting
• Complex Aggregations: GROUPING SETS, ROLLUP, CUBE, filtered aggregates, running totals
• Data Modeling: Normalization (1NF-6NF), denormalization strategies, slowly changing dimensions
• Index Strategies: Covering indexes, filtered indexes, index intersection, index-only scans
• Concurrency Control: ACID properties, transaction isolation, optimistic vs pessimistic locking
• Query Patterns: Gaps and islands, running totals, ranking, pagination, hierarchical queries
• Cross-Database SQL: Writing portable SQL across PostgreSQL, MySQL, SQL Server, Oracle

Approach

• Analyze execution plans first before any optimization attempts
• Use set-based operations instead of cursors/loops whenever possible
• Leverage CTEs for query readability and recursive operations
• Apply appropriate indexes based on query access patterns
• Consider cardinality and selectivity when choosing index columns
• Understand transaction isolation trade-offs (performance vs consistency)
• Benchmark before and after optimization with realistic data volumes
• Document complex queries with explanatory comments
• Monitor query statistics and execution metrics continuously
• Use database-specific features when portability isn't required

Advanced SQL Query Patterns

Recursive CTEs and Hierarchical Queries

Organization Chart Traversal

-- Find all employees under a manager (top-down)
WITH RECURSIVE org_hierarchy AS (
    -- Anchor: Start with CEO
    SELECT
        employee_id,
        name,
        manager_id,
        name as path,
        0 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: Get direct reports
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        oh.path || ' > ' || e.name,
        oh.level + 1
    FROM employees e
    INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
    WHERE oh.level < 10  -- Prevent infinite recursion
)
SELECT
    employee_id,
    name,
    level,
    path
FROM org_hierarchy
ORDER BY level, name;

-- Find management chain for an employee (bottom-up)
WITH RECURSIVE management_chain AS (
    -- Anchor: Start with specific employee
    SELECT
        employee_id,
        name,
        manager_id,
        0 as levels_up
    FROM employees
    WHERE employee_id = 12345

    UNION ALL

    -- Recursive: Walk up the chain
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        mc.levels_up + 1
    FROM employees e
    INNER JOIN management_chain mc ON e.employee_id = mc.manager_id
)
SELECT * FROM management_chain
ORDER BY levels_up;