Day 25/30 SQL, Python, ETL, Data Modelling Challenge FREE Solutions 🚀

March 27th, 2025 CHALLENGE – unlock solutions + reasoning

👋 Hey Data Engineers!

Difficulty Level: Intermediate → Advanced

You’re officially at the 25-day mark! 🔥 Let’s sharpen your understanding of HAVING clauses, string manipulation, data validation, and OLTP system design.

💡 Understand, Don’t Memorize:
✅ Real-world clarity
✅ Best practices
✅ Interview relevance

💡 Want full solutions + runnable code? Upgrade to the Annual Plan now and own your DE journey.

---

📌 SQL Challenge – Filtering with HAVING

❓ Which clause is used to filter after an aggregation has been applied?

🔘 A) WHERE
🔘 B) FILTER
🔘 C) HAVING
🔘 D) JOIN

✅ Answer: C - HAVING

Explanation:
HAVING filters aggregated results (e.g., GROUP BY dept HAVING COUNT(*) > 10), while WHERE filters rows before aggregation.

Best Practices:
✔️ Use WHERE before aggregation, HAVING after
✔️ Avoid HAVING without aggregation – it's a misuse
✔️ Use descriptive aliases for clarity

---

🐍 Python Challenge – Title Case Conversion

sentence = "data engineering is fun"  
print(sentence.title())

❓ What will be printed?

🔘 A) Data Engineering Is Fun
🔘 B) data Engineering Is Fun
🔘 C) Data engineering is fun
🔘 D) DATA ENGINEERING IS FUN

✅ Answer: A - Data Engineering Is Fun

Explanation:
.title() capitalizes the first letter of every word. Great for formatting labels, names, and headings.

Best Practices:
✔️ Use .title() for display, not for storage
✔️ Use .capitalize() if you want just the first word formatted
✔️ Be aware it doesn’t handle acronyms properly (e.g., “API” becomes “Api”)

---

⚡ ETL Challenge – Null Value Checks

❓ Which check would help catch null values before loading data into the warehouse?

🔘 A) Type casting
🔘 B) Primary key constraint
🔘 C) NULL check in staging
🔘 D) Denormalization

✅ Answer: C - NULL check in staging

Explanation:
Pre-load checks in staging tables help catch nulls early and prevent bad data from polluting downstream systems.

Best Practices:
✔️ Use assertions or conditional filters on NULLs
✔️ Enforce NOT NULL where required
✔️ Log and quarantine invalid records

---

🧱 Data Modeling Challenge – OLTP System Focus

❓ OLTP systems are optimized for which type of operation?

🔘 A) Large analytical queries
🔘 B) Real-time reporting
🔘 C) Frequent inserts and updates
🔘 D) Batch processing

✅ Answer: C - Frequent inserts and updates

Explanation:
OLTP = Online Transaction Processing → fast, atomic operations (e.g., banking, e-commerce orders).

Best Practices:
✔️ Use normalized schemas (3NF) for OLTP
✔️ Prioritize low latency over analytics
✔️ Offload reporting to OLAP systems

---

🚀 Final 5 Days Incoming…

You're almost at the finish line. Here's what you unlock with the full subscription:

✅ Advanced deep dives & mock interview sets
✅ Runnable SQL/Python code for every challenge
✅ DE system design playbooks

👉 Subscribe here: zero2dataengineer.substack.com

💬 Comment your answers. Tomorrow’s leaderboard awaits! 🔥