Module 6: FAANG / Big Tech Interview Masterclass

IMPORTANT

This module contains 15 real-world SQL interview problems commonly asked at top tech companies. Each problem is self-contained with schema, sample data, brute-force and optimal solutions, and interviewer follow-ups.

🎯 Who Is This For?

Data Engineers preparing for FAANG / Big Tech interviews
Analytics Engineers brushing up on advanced SQL patterns
Anyone who wants to go beyond LeetCode-style SQL into production-realistic scenarios

📋 Problem Index

#	Problem	Difficulty	Core Pattern	Key Companies
01	Active User Retention & Cohort Churn	🔴 Hard	Self-joins, date arithmetic, cohort matrices	Meta, Netflix, Spotify, Airbnb
02	Consecutive Days Active / Streak Analysis	🟡 Medium	Gaps & Islands, ROW_NUMBER differencing	Snapchat, Meta, Spotify, LinkedIn
03	Top N Products per Category	🟡 Medium	Window functions, RANK vs DENSE_RANK	Amazon, Walmart, Instacart, DoorDash
04	Overlapping Bookings / Resource Contention	🔴 Hard	Interval overlap detection, running max	Airbnb, Google, Uber, Booking.com
05	Marketing Attribution Modeling	🔴 Hard	FIRST_VALUE, LAST_VALUE, fractional allocation	Google, Meta, Pinterest, Snap
06	Circular / Ring Transactions (Fraud)	🔴 Hard	Recursive CTEs, graph traversal in SQL	PayPal, Stripe, Square, Goldman Sachs
07	Unmatched Records Across Massive Tables	🟡 Medium	Anti-joins, NOT EXISTS vs LEFT JOIN	Amazon, Apple, Microsoft, Google
08	Running Total with Budget Cap	🟡 Medium	Window SUM, conditional cumulation	Netflix, Uber, Lyft, DoorDash
09	Dynamic Pivot / Unpivot	🟡 Medium	CASE WHEN pivot, CROSS JOIN UNNEST	Microsoft, Amazon, Salesforce, Oracle
10	Multi-Step Event Funnel Analysis	🔴 Hard	Conditional aggregation, sessionization	Meta, Google, Airbnb, Uber, Spotify
11	Median & Percentiles Without Built-ins	🟡 Medium	ROW_NUMBER, COUNT, self-join	Google, Meta, Amazon, Microsoft
12	Recursive Hierarchy Rollup	🔴 Hard	Recursive CTE, tree aggregation	Google, Amazon, Salesforce, SAP
13	Deduplication with Priority Rules & MERGE	🟡 Medium	ROW_NUMBER partitioning, MERGE upsert	Snowflake, Databricks, Netflix, Airbnb
14	Time-Weighted Averages (IoT/Sensor)	🔴 Hard	LEAD/LAG, EXTRACT(EPOCH), weighted sum	Tesla, Honeywell, Samsara, Uptake
15	Point-in-Time / Temporal Joins (SCD2)	🔴 Hard	Range joins, SCD Type 2 lookups	Netflix, Airbnb, Stripe, Spotify

🗂️ Difficulty Breakdown

Difficulty	Count	Problems
🟡 Medium	7	#02, #03, #07, #08, #09, #11, #13
🔴 Hard	8	#01, #04, #05, #06, #10, #12, #14, #15

🧠 How to Use This Module

Read the Problem Statement — understand the business scenario before writing any SQL
Try the Brute Force first — get a working solution, even if slow
Study the Optimal Solution — understand why it performs better
Answer the Follow-Ups — interviewers always extend the problem
Review Key Takeaways — internalize the reusable pattern

TIP

In real interviews, communication matters as much as the code. Practice explaining your approach out loud before writing SQL. Start with edge cases and clarifying questions.

🔗 Pattern Cross-Reference

Pattern	Problems
Window Functions (RANK, ROW_NUMBER, LEAD/LAG)	#02, #03, #08, #11, #13, #14
Recursive CTEs	#06, #12
Self-Joins	#01, #04, #06
Gaps & Islands	#02, #04
Anti-Joins / Set Difference	#07
Conditional Aggregation	#05, #09, #10
Date/Time Arithmetic	#01, #02, #14, #15
MERGE / Upserts	#13
Temporal / Range Joins	#14, #15

📚 Prerequisites

Before tackling these problems, ensure you are comfortable with:

Good luck with your interviews! 🚀

Module 6: FAANG / Big Tech Interview Masterclass ​

🎯 Who Is This For? ​

📋 Problem Index ​

🗂️ Difficulty Breakdown ​

🧠 How to Use This Module ​

🔗 Pattern Cross-Reference ​

📚 Prerequisites ​