🔓 📢 Day 13/30 - DEEP DIVE SOLUTIONS: SQL, PYTHON, ETL, DATA MODELING

Solutions for March 12th, 2025 CHALLENGE – Unlock the Full Breakdown + Live Runnable Code! 🚀

👋 Hey Data Engineers!
Welcome to Day 13 of the 30-Day Data Engineering Challenge! 🚀

Today’s Deep Dive covers:

✅ SQL Recursive Queries (Building Hierarchical Queries with CTEs)
✅ Python Generators vs. List Comprehensions (Efficient Memory Usage)
✅ ETL Job Scheduling with Apache Airflow (Automating ETL Pipelines)
✅ Data Warehouse Performance Optimization (Partitioning for Faster Queries)

🧠 Master these concepts with:
✅ Clear explanations & reasoning
✅ Live runnable code & breakdowns
✅ Real-world best practices

🔐 Unlock the full deep dive & runnable code by upgrading to the Annual Plan!

---

📌 SQL Challenge - Recursive CTEs

Understanding Recursive Queries in SQL

A recursive common table expression (CTE) allows a query to reference itself, enabling hierarchical data traversal (e.g., organizational structures, category trees, dependency graphs).

🔹 Where It’s Used in Real-World Applications?
✅ Employee Hierarchies – Finding reporting relationships in companies.
✅ Bill of Materials (BOM) – Identifying dependencies in manufacturing.
✅ Graph Queries – Traversing connected data like social networks.

Run & Test on onecompiler.com

1️⃣ Open onecompiler.com, select SQL (PostgreSQL or MySQL).
2️⃣ Copy and paste the SQL query below:

-- Create Employee Table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT
);

-- Insert Sample Data
INSERT INTO employees VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

-- Recursive Query to Find Employee Hierarchy
WITH RECURSIVE employee_hierarchy AS (
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN employee_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM employee_hierarchy;

3️⃣ Click Run and analyze the output.

What’s Happening in the Output?

✔ Recursively retrieves employee hierarchy levels from the CEO down to employees.
✔ Uses UNION ALL to iteratively build relationships between managers and employees.

🔹 Optimizations & Best Practices
✅ Always include a base case (WHERE manager_id IS NULL) to prevent infinite loops.
✅ Use indexing on hierarchical keys (e.g., manager_id) for efficient lookups.
✅ Limit depth with OPTION (MAXRECURSION 5) in SQL Server to avoid excessive recursion.

---