Day 26/30 - DEEP DIVE SOLUTIONS: SQL, PYTHON, ETL, DATA MODELING

Solutions for March 31st, 2025 Challenge – Full Breakdown + Live Runnable Code

Hello Data Engineers,

It’s Day 26 and we’re stepping into recursive queries, Python decorators, incremental pipelines, and SCD tracking. These concepts help you move from intermediate to advanced in both interview and real-world scenarios.

SQL Deep Dive: Recursive CTEs to Traverse Hierarchies

Challenge Recap:
❓ What does the following recursive query return?

WITH RECURSIVE employee_path AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT COUNT(*) FROM employee_path;

🔘 A) Total number of employees
🔘 B) Depth of the org chart
🔘 C) Number of employees under employee 1
🔘 D) SQL Error

✅ Answer: Option C - Number of employees under employee 1

---

Why This Happens:

This recursive CTE starts from employee id = 1 and recursively fetches all employees who report (directly or indirectly) to that employee. The final COUNT(*) tells how many such employees exist under that hierarchy.

---

Where It’s Used in Real-World Applications:

• Organizational structure lookups

• Customer referral trees

• Category/subcategory relationships

---

Run & Test on OneCompiler:

1️⃣ Open OneCompiler → Select PostgreSQL
2️⃣ Paste the following code:

CREATE TABLE employees (
    id INT,
    name TEXT,
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, 'VP', 1),
(3, 'Director', 2),
(4, 'Engineer', 3);

WITH RECURSIVE employee_path AS (
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT COUNT(*) FROM employee_path WHERE id != 1;

3️⃣ Click Run to view the count of employees under ID 1.

---

Best Practices:

✔️ Use recursive CTEs for trees and graphs
✔️ Always define a base case + recursive case
✔️ Add safeguards (e.g., depth or level column) to avoid infinite loops

---

Python Deep Dive: Understanding Decorators

Challenge Recap:
❓ What will this code output?

def decorator(func):
    def wrapper():
        print("Before")
        func()
        print("After")
    return wrapper

@decorator
def greet():
    print("Hello")

greet()

🔘 A) Hello
🔘 B) Before Hello After
🔘 C) Syntax Error
🔘 D) After Hello Before

✅ Answer: Option B - Before Hello After

---

Why This Happens:

The @decorator syntax wraps greet() with the wrapper() function. When greet() is called, it actually runs the wrapper, printing "Before", calling the original function, then "After".