Day 6 | SQL Aggregation and Joins Questions Asked in FAANG

SQL Series – This series aim to make sure you are able to learn and understand at least 100 of the most asked concepts in SQL interviews.
Today is Day 6 of the SQL Series – SQL Aggregation and Joins Questions Asked in FAANG
Bookmark this page, and make sure to be consistent in it. Also, get a notebook and make notes of each question/concept put in here

We have created this series to make you practice and learn ~120 Most Asked SQL interview Questions

Day 1 – Basic SQL Questions + Most Asked SQL Interview Questions
Day 2 – JSON, Date and Time SQL Interview Questions
Day 3 – SQL Query Optimization and Data Modeling Interview Questions
Day 4 – Transactions and Concurrency
Day 5 – SQL Hard Interview Questions
Day 6 – SQL Aggregation and Joins Questions Asked in FAANG
Day 7 – Error Handling & Debugging + Stored Procedures & CTE
Day 8 – Complex Data Handling
Day 9 – Data Modeling
Day 10 – SQL Most Asked Query Interview Questions

The Data Monk Best Seller Books and Services

1. Become a Full Stack Analytics Professional in 2026 – 2200 Interview Questions covering 23 topics
2. Book mock interviews/Career Guidance Session on Top Mate to practice

And below is our master class

1 Month Complete 1:1 Mentorship along with 3 e-books, 4 mock interviews, 4 career guidance sessions, and a complete resume review

You can also go through our The Data Monk’s Youtube Channel with 160+ videos across all the top interview topics

Table of Contents

• Advanced Aggregation Patterns (4 Questions)
• Complex Join Scenarios (4 Questions)
• FAANG Interview Tips

Introduction: Why Advanced Aggregation and Joins Matter in FAANG Interviews

SQL aggregation and joins are fundamental skills that every data engineer and backend engineer at FAANG companies must master. Unlike simple SELECT queries, advanced aggregation and complex joins test your ability to:

• Think in sets: Can you manipulate groups of data, not just individual rows?
• Optimize performance: Will your query scale to 100M+ rows?
• Handle edge cases: NULL values, empty groups, duplicate joins?
• Combine multiple concepts: Subqueries + window functions + conditional logic
• Solve real business problems: Cohort analysis, funnel metrics, duplicate detection

In this guide, you’ll see the exact types of questions asked at Facebook/Meta, Amazon, Google, Apple, and Netflix—and how to solve them like a senior engineer.

Advanced SQL Aggregation: 4 FAANG Interview Questions {#aggregation}

Aggregation questions test your mastery of GROUP BY, HAVING, window functions, and conditional logic. FAANG interviewers ask these to see if you can build complex metrics like retention rates, daily active users (DAU), and funnel metrics.

Question 1 (Aggregation): Calculate Customer Cohort Retention Rates

Asked at: Facebook/Meta, Netflix

Interview Question:

“You have a user events table with user_id, event_date, and event_type. Cohorts are defined by the month of a user’s first purchase. Calculate the monthly retention rate for each cohort. Return: cohort_month, month_number (1 for first month, 2 for second, etc.), cohort_size, and retained_users_count.”

Why FAANG Asks This:

This is a real business metric used by every tech company. Understanding retention is critical for:

• Product teams: Measuring feature success
• Marketing: Understanding user lifetime value
• Finance: Predicting revenue and churn
• Engineering: Knowing if your changes improved user engagement

Sample Data:

CREATE TABLE UserEvents (
    user_id INT,
    event_date DATE,
    event_type VARCHAR(50)
);

INSERT INTO UserEvents VALUES
-- Cohort: January 2024 (first purchase date)
(1, '2024-01-05', 'purchase'),
(1, '2024-02-10', 'purchase'),
(1, '2024-03-15', 'purchase'),
(2, '2024-01-10', 'purchase'),
(2, '2024-02-20', 'purchase'),
(3, '2024-01-15', 'purchase'),  -- Churned after cohort month

-- Cohort: February 2024
(4, '2024-02-05', 'purchase'),
(4, '2024-03-10', 'purchase'),
(5, '2024-02-08', 'purchase'),  -- Only purchased in cohort month

-- Cohort: March 2024
(6, '2024-03-20', 'purchase');

Solution: Using Window Functions + Conditional Aggregation

WITH first_purchase AS (
    SELECT 
        user_id,
        MIN(DATE_TRUNC('month', event_date)) as cohort_month
    FROM UserEvents
    WHERE event_type = 'purchase'
    GROUP BY user_id
),
cohort_with_months AS (
    SELECT 
        fp.user_id,
        fp.cohort_month,
        YEAR_MONTH(ue.event_date) as event_month,
        DATEDIFF(YEAR_MONTH(ue.event_date), YEAR_MONTH(fp.cohort_month)) + 1 as month_number
    FROM first_purchase fp
    INNER JOIN UserEvents ue ON fp.user_id = ue.user_id
    WHERE ue.event_type = 'purchase'
),
cohort_summary AS (
    SELECT 
        cohort_month,
        COUNT(DISTINCT CASE WHEN month_number = 1 THEN user_id END) as cohort_size,
        month_number,
        COUNT(DISTINCT user_id) as retained_users
    FROM cohort_with_months
    GROUP BY cohort_month, month_number
)
SELECT 
    cohort_month,
    month_number,
    cohort_size,
    retained_users,
    ROUND(100.0 * retained_users / cohort_size, 2) as retention_rate_pct
FROM cohort_summary
ORDER BY cohort_month, month_number;

Question 2 (Aggregation): Find Top N Items Per Category Using Dense Rank

Asked at: Amazon, Google

Interview Question:

“Find the top 3 products by sales amount in EACH category. Include: category, product_id, amount, and rank. Handle ties properly—if two products have the same amount, both should be included even if it exceeds 3.”

Solution: Using DENSE_RANK Window Function

WITH ranked_products AS (
    SELECT 
        category,
        product_id,
        amount,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY amount DESC) as rank
    FROM Sales
)
SELECT 
    category,
    product_id,
    amount,
    rank
FROM ranked_products
WHERE rank <= 3
ORDER BY category, rank, product_id;

-- Why DENSE_RANK?
-- RANK()        : 1, 2, 2, 4       (skips rank 3 when there's a tie)
-- DENSE_RANK()  : 1, 2, 2, 3       (consecutive ranks, no skips)
-- ROW_NUMBER()  : 1, 2, 3, 4       (assigns unique number to each row)

Interview Tip: The interviewer is testing if you understand the difference between RANK(), DENSE_RANK(), and ROW_NUMBER(). When they ask ‘top N including ties’, use DENSE_RANK(). Always clarify which behavior they want.

Question 3 (Aggregation): Calculate Running Totals with Conditional Restart (Sales Streak)

Asked at: Apple, Google

Interview Question:

“Calculate the cumulative sales for each store’s current sales streak. A streak breaks if a day has $0 sales.”

Solution: Window Functions with Conditional Grouping

WITH streak_groups AS (
    SELECT 
        store_id,
        sale_date,
        sales,
        SUM(CASE 
            WHEN sales = 0 THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY store_id ORDER BY sale_date) as streak_group
    FROM StoreSales
)
SELECT 
    store_id,
    sale_date,
    sales,
    SUM(sales) OVER (
        PARTITION BY store_id, streak_group 
        ORDER BY sale_date
    ) as cumulative_in_streak
FROM streak_groups
ORDER BY store_id, sale_date;

Question 4 (Aggregation): Calculate Customer Lifetime Value with Multiple Conditions

Asked at: Amazon, Netflix

Interview Question:

“Calculate CLV: total spend minus returns, but only count orders from the last 12 months. Only include customers with CLV > $100.”

Solution: Complex Conditional Aggregation

WITH last_12_months AS (
    SELECT 
        customer_id,
        order_amount,
        return_amount,
        order_date
    FROM Orders
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
)
SELECT 
    customer_id,
    ROUND(SUM(order_amount) - SUM(return_amount), 2) as clv,
    COUNT(*) as order_count,
    COUNT(CASE WHEN return_amount > 0 THEN 1 END) as return_count,
    ROUND(AVG(order_amount), 2) as avg_order_value
FROM last_12_months
GROUP BY customer_id
HAVING SUM(order_amount) - SUM(return_amount) > 100
ORDER BY clv DESC;

Advanced SQL Joins: 4 FAANG Interview Questions {#joins}

Join questions test your ability to combine data from multiple tables correctly, handle NULL values, and avoid common pitfalls.

Question 5 (Joins): Multiple Joins with Aggregation – User Activity Summary

Asked at: Meta/Facebook, Netflix

Interview Question:

“Show each user: total_posts, total_likes_received, and average_likes_per_post. Include users who have 0 posts and 0 likes.”

Solution: Multiple LEFT JOINs with Careful Aggregation

SELECT 
    u.user_id,
    u.name,
    COUNT(DISTINCT p.post_id) as total_posts,
    COUNT(DISTINCT l.like_id) as total_likes_received,
    CASE 
        WHEN COUNT(DISTINCT p.post_id) = 0 THEN NULL
        ELSE ROUND(
            COUNT(DISTINCT l.like_id) * 1.0 / COUNT(DISTINCT p.post_id), 
            2
        )
    END as avg_likes_per_post
FROM Users u
LEFT JOIN Posts p ON u.user_id = p.user_id
LEFT JOIN Likes l ON p.post_id = l.post_id
GROUP BY u.user_id, u.name
ORDER BY u.user_id;

-- CRITICAL: Use COUNT(DISTINCT ...) to avoid double-counting
-- when multiple likes exist for the same post

Question 6 (Joins): Self-Join with Complex Condition – Find Duplicate Transactions

Asked at: Google, Apple

Interview Question:

“Find transactions that are potential duplicates (same user, same amount, within 5 minutes). Show both transaction_ids and flag as POTENTIAL_FRAUD.”

Solution: Self-Join with Complex WHERE Conditions

SELECT 
    t1.transaction_id as transaction_id_1,
    t2.transaction_id as transaction_id_2,
    t1.user_id,
    t1.amount,
    TIMESTAMPDIFF(SECOND, t1.timestamp, t2.timestamp) as time_diff_seconds,
    'POTENTIAL_FRAUD' as fraud_flag
FROM Transactions t1
INNER JOIN Transactions t2 ON 
    t1.user_id = t2.user_id
    AND t1.amount = t2.amount
    AND t2.timestamp > t1.timestamp
    AND TIMESTAMPDIFF(SECOND, t1.timestamp, t2.timestamp) <= 300
ORDER BY t1.user_id, t1.transaction_id;

Question 7 (Joins): Three-Table Join with Filtering – Customer Retention Analysis

Asked at: Amazon, Google

Interview Question:

“Find customers who: (1) placed an order in 2024, (2) placed ANOTHER order in 2025, and (3) left a review. Return: customer_id, first_order_date_2024, second_order_date_2025, and review_count.”

Solution: Multiple Joins with CTEs

WITH orders_2024 AS (
    SELECT 
        customer_id,
        MIN(order_date) as first_order_2024
    FROM Orders
    WHERE YEAR(order_date) = 2024
    GROUP BY customer_id
),
orders_2025 AS (
    SELECT 
        customer_id,
        MIN(order_date) as first_order_2025
    FROM Orders
    WHERE YEAR(order_date) = 2025
    GROUP BY customer_id
),
customer_reviews AS (
    SELECT 
        customer_id,
        COUNT(*) as review_count
    FROM ProductReviews
    GROUP BY customer_id
)
SELECT 
    o24.customer_id,
    o24.first_order_2024,
    o25.first_order_2025,
    COALESCE(cr.review_count, 0) as review_count
FROM orders_2024 o24
INNER JOIN orders_2025 o25 ON o24.customer_id = o25.customer_id
LEFT JOIN customer_reviews cr ON o24.customer_id = cr.customer_id
ORDER BY o24.customer_id;

Question 8 (Joins): Complex Filter After Join – Find Products Never Ordered by Premium Customers

Asked at: Meta, Netflix

Interview Question:

“Find products that have NEVER been ordered by premium customers, even if ordered by free-tier customers.”

Solution: Using LEFT JOIN + WHERE NULL Pattern

WITH premium_products AS (
    SELECT DISTINCT oi.product_id
    FROM OrderItems oi
    INNER JOIN Orders o ON oi.order_id = o.order_id
    INNER JOIN Customers c ON o.customer_id = c.customer_id
    WHERE c.subscription_tier = 'premium'
),
free_order_counts AS (
    SELECT 
        oi.product_id,
        COUNT(oi.item_id) as free_order_count
    FROM OrderItems oi
    INNER JOIN Orders o ON oi.order_id = o.order_id
    INNER JOIN Customers c ON o.customer_id = c.customer_id
    WHERE c.subscription_tier = 'free'
    GROUP BY oi.product_id
)
SELECT 
    p.product_id,
    p.name,
    COALESCE(foc.free_order_count, 0) as total_orders_by_free_customers
FROM Products p
INNER JOIN free_order_counts foc ON p.product_id = foc.product_id
LEFT JOIN premium_products pp ON p.product_id = pp.product_id
WHERE pp.product_id IS NULL;

🎯 Want to Pass FAANG SQL Interviews Guaranteed?

These 8 questions are just the beginning. FAANG companies ask 50+ variations of SQL patterns.

Get 220+ FAANG SQL Questions with Solutions →

Access comprehensive coverage of:

• Window functions mastery
• Complex joins and subqueries
• Optimization techniques
• Real business problems
• Step-by-step solutions

How FAANG Companies Test SQL: Interview Tips {#faang-tips}

What FAANG Interviewers Actually Look For

✅ Traits of Candidates Who PASS:

• “Let me clarify the requirements…” → Shows you understand the problem before jumping to code
• “Here are my assumptions…” → Demonstrates critical thinking
• “Let me walk through an example…” → Shows concrete thinking
• “This approach is O(n log n). For 100M rows that means…” → Shows production awareness
• “I’d optimize this with an index on…” → Shows system design thinking
• “Let me consider edge cases…” → Shows mature thinking
• “Two approaches: one simple, one optimized…” → Shows flexibility

❌ Traits of Candidates Who FAIL:

• “Let me just code this…” → No thought process
• “I’m not sure about window functions” → Not up-to-date
• “This takes 30 seconds, that’s fine” → No performance awareness
• “I’ll filter in the application” → Fundamental misunderstanding
• “I haven’t heard of DENSE_RANK” → Lack of preparation
• “This works for our data” → Not thinking about scale
• “Is this right?” → Lack of confidence

Common FAANG SQL Interview Patterns

The FAANG Interview Format

Typical 60-minute SQL interview:

1. 0-5 minutes: Interviewer explains scenario, shows sample data
2. 5-10 minutes: You clarify requirements, ask edge cases
3. 10-40 minutes: You code, explain thinking
4. 40-50 minutes: Test edge cases, discuss performance
5. 50-60 minutes: Variation question or deeper dive

Key Takeaways: Master Advanced SQL for FAANG

• Aggregation is about groups: Master GROUP BY, HAVING, window functions
• Joins require precision: COUNT(DISTINCT), LEFT vs INNER, order matters
• Think about scale: Will this work for 100M rows?
• Know your functions: RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD
• Always ask questions: Clarify requirements before coding
• Show your process: Explain your thinking, don’t just write code
• Consider edge cases: NULLs, empty results, duplicates, ties
• Practice variations: Each question has 5-10 variations

Ready to ace your FAANG interview? The 8 questions here represent core patterns. FAANG companies ask 50+ variations. Systematic practice is your path to success.

Get 220+ practice questions with solutions →