🔓 📢 Day 3/30 - CHALLENGE DEEP DIVE SOLUTIONS : SQL, PYTHON, ETL, DATA MODELLING 🔥

Solutions for Feb 26th, 2025 CHALLENGE – Unlock Full Breakdown + Live Runnable Code!

🚀 Today, we’re covering:
🔹 SQL Indexing – Optimize queries & reduce scan times.
🔹 Python Lambda & Map – Transform data efficiently.
🔹 ETL Task Dependencies – Run Airflow DAGs in the right order.
🔹 SCDs in Data Modeling – Track historical data changes.

🧠 Don’t just memorize—understand. Every challenge solution includes:
✅ Clear explanation & reasoning
✅ Why this solution works
✅ Key optimizations & best practices

If you want deep dives + runnable code to test these solutions, upgrade to the annual plan and master these concepts like a pro!

💡 Want to understand beyond just answers? Follow along & test each concept LIVE!

👉 Instructions to Run Code:
1️⃣ Go to onecompiler.com
2️⃣ Select the appropriate language (SQL/PostgreSQL for SQL queries, Python for Python code).
3️⃣ Paste the provided code and click ‘Run’ to see the output.

---

🚀 SQL Challenge - Indexing & Query Performance (Deep Dive & Optimizations)

🔹 What Are Indexes in SQL?

Indexes are database structures that speed up queries by creating an organized, searchable reference for specific columns. Think of an index like a table of contents in a book—it helps you quickly find information instead of scanning every page.

Without an index, the database performs a full table scan, meaning it checks every row one by one. With an index, it uses a search tree (B-Tree or Hash) to locate the data much faster.

🔹 When Are Indexes Useful?

✅ For fast lookups in WHERE conditions (O(log n) complexity)
✅ For speeding up ORDER BY queries when indexed in the correct order
✅ For JOIN operations on large tables where the indexed column is used as a foreign key

🚨 When Not to Use Indexes:
❌ On columns with very few unique values (low cardinality), like gender (M/F).
❌ When frequent updates occur (indexes slow down INSERT/UPDATE operations).

🔹 Problem Statement

Which SQL query benefits the most from an index on the email column in the users table?

✅ Solution Explanation

• Indexes create a sorted structure, allowing the database to search efficiently.

• Queries using WHERE email = 'test@email.com' are optimized by skipping full scans.

• Indexes are NOT useful for SELECT * without filtering.

🔹 SQL Solution (Run on onecompiler.com)

-- Create users table with an index on email
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

-- Insert sample users
INSERT INTO users (user_id, email) VALUES
(1, 'alice@email.com'),
(2, 'bob@email.com'),
(3, 'charlie@email.com');

-- Optimized query using the index
SELECT * FROM users WHERE email = 'bob@email.com';

🔹 Performance Optimizations

✅ Indexes reduce lookup time from O(n) to O(log n) in large datasets.
✅ Always index columns frequently used in WHERE conditions.
✅ Avoid indexing low-cardinality columns (e.g., gender) as they don’t improve performance.