Scenario-Based SQL Overview

In senior-level interviews and top product companies (Google, Meta, Amazon), you won’t be asked “how to do a Join”. Instead, you’ll be given a business problem and asked to solve it using SQL.

What is Product-Level SQL?

It involves understanding the business context behind the rows. You need to calculate things like:

• Retention: How many customers came back after 7 days?
• Churn: Which users stopped using the service?
• Funnel Conversion: At what stage are users dropping off in the signup process?
• LTV (Lifetime Value): How much revenue does a user bring over their lifetime?

Skills You Need

These problems often require combining:

1. Joins + Window Functions (ROW_NUMBER, LAG, SUM OVER).
2. CTEs for multi-stage calculations (dedup, rank, then aggregate).
3. Complex Aggregations with CASE statements.
4. Date/time arithmetic (DATEDIFF, DATE_TRUNC, INTERVAL).

Common Problem Patterns

Pattern	Example	Key Technique
Retention/Churn	Users active on Day 7	DATEDIFF + COUNT(DISTINCT CASE)
Funnel Analysis	Visit → Signup → Purchase	Multiple COUNT(DISTINCT) with joins
LTV / CLV	Total spend per customer	SUM + GROUP BY
Deduplication	Keep latest row per user	ROW_NUMBER() OVER (PARTITION BY)
Sessionization	Group events into 30-min sessions	LAG + SUM(CASE)
A/B Testing	Compare conversion rates	GROUP BY test variant
Moving Averages	7-day rolling revenue	AVG OVER (ROWS BETWEEN)

How to Approach a Scenario Question

1. Clarify: Ask about edge cases — what if a user has no orders? What’s the time zone?
2. Think out loud: Explain which tables you need and how you’ll join them.
3. Start simple: Write a basic query first, then add complexity with CTEs.
4. Handle NULLs: Use COALESCE and NULLIF to avoid arithmetic errors.
5. Verify: Run a mental check with sample data.