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

Solutions for April 4th, 2025 Challenge – Final Day Breakdown + Live Runnable Code

Apr 05, 2025

∙ Paid

Hello Data Engineers,

You've made it to the FINAL DAY of our 30-Day Data Engineering Challenge!
We’re closing strong with advanced-level questions on:

SQL Window Functions (RANK vs. DENSE_RANK)
Python Filtering with List Comprehensions
Incremental Loads in ETL
OLAP vs. OLTP System Design

Let’s break them down in deep-dive detail.

SQL Deep Dive: RANK() vs. DENSE_RANK()

Challenge Recap:
❓ Which statement is TRUE about RANK() and DENSE_RANK()?

🔘 A) Both assign the same rank to tied rows and skip the next rank
🔘 B) RANK() skips ranks after ties, but DENSE_RANK() does not
🔘 C) DENSE_RANK() skips ranks after ties
🔘 D) RANK() resets for each row

✅ Answer: B - RANK() skips ranks after ties, but DENSE_RANK() does not

Why This Happens:

RANK() assigns the same rank to tied rows, but skips the next rank(s).
DENSE_RANK() assigns the same rank to tied rows but does not skip—it continues sequentially.

Where It’s Used in Real-World Applications:

RANK() is used when gaps in rank are important (e.g., competition-style scores).
DENSE_RANK() is used when you want continuous ranking with no skipped numbers.

Run & Test on OneCompiler:

1️⃣ Open OneCompiler SQL → Choose PostgreSQL
2️⃣ Paste this:

CREATE TABLE employees (
    name TEXT,
    department TEXT,
    salary INT
);

INSERT INTO employees VALUES
('Alice', 'Sales', 80000),
('Bob', 'Sales', 80000),
('Charlie', 'Sales', 75000),
('Dave', 'Sales', 70000);

SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) AS rank_val,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM employees;

3️⃣ Click Run → Observe how RANK vs DENSE_RANK behaves with ties.

Zero2Dataengineer