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

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

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

Today's Deep Dive covers:
✅ SQL Ranking Functions (RANK vs. DENSE_RANK)
✅ Python Set Operations (Handling unique values efficiently)
✅ ETL Data Lineage (Tracking data movement in ETL pipelines)
✅ Data Modeling - Star Schema vs. Snowflake Schema (Choosing the right schema for performance)

🧠 Understand, Don’t Just Memorize:
✅ Clear explanations & reasoning
✅ Why this solution works
✅ Key optimizations & best practices

---

📌 SQL Challenge - RANK vs. DENSE_RANK

Understanding SQL Ranking Functions

RANK() and DENSE_RANK() are window functions used to assign ranks to rows based on a specified ordering.

Where It’s Used in Real-World Applications?

✅ Leaderboard Systems – Ranking users based on scores.
✅ Sales Reports – Ranking products by revenue.
✅ Data Deduplication – Identifying duplicate records while keeping the first occurrence.

Run & Test on onecompiler.com

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

-- Create Sales Table
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    product VARCHAR(50),
    amount DECIMAL(10,2)
);

-- Insert Sample Data
INSERT INTO sales VALUES
(1, 'Laptop', 1200.00),
(2, 'Phone', 800.00),
(3, 'Tablet', 1200.00),
(4, 'Monitor', 900.00);

-- Apply RANK() and DENSE_RANK()
SELECT 
    product, amount,
    RANK() OVER (ORDER BY amount DESC) AS rank_value,
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank_value
FROM sales;

3️⃣ Click Run and analyze the results.

What’s Happening in the Output?

✔ RANK() skips numbers when ties occur.
✔ DENSE_RANK() assigns consecutive numbers without gaps.

🔹 Optimizations & Best Practices
✅ Use RANK() when absolute ranking is important (e.g., competition rankings).
✅ Use DENSE_RANK() when you want consecutive ranking without gaps.
✅ Prefer PARTITION BY when ranking within specific categories.