🚀 SQL Scenario-Based Interview Questions with Answers (Part 9)

💼 FAANG & Product Company SQL Interview Scenarios

📌 Question 81: Find Users Who Were Active on 3 Consecutive Days

Table: user_activity (user_id, activity_date)

WITH activity AS (

SELECT DISTINCT

user_id,

activity_date

FROM user_activity

),

groups AS (

SELECT

user_id,

activity_date,

activity_date -

ROW_NUMBER() OVER (

PARTITION BY user_id

ORDER BY activity_date

) * INTERVAL '1 day' AS grp

FROM activity

)

SELECT

user_id,

COUNT() AS consecutive_days

FROM groups

GROUP BY user_id, grp

HAVING COUNT() >= 3;

📌 Question 82: Find the Top 5 Selling Products in Each Category

Tables: products (product_id, category) sales (product_id, quantity)

WITH product_sales AS (

SELECT

p.category,

s.product_id,

SUM(s.quantity) AS total_quantity

FROM sales s

JOIN products p

ON s.product_id = p.product_id

GROUP BY p.category, s.product_id

)

SELECT *

FROM (

SELECT *,

DENSE_RANK() OVER (

PARTITION BY category

ORDER BY total_quantity DESC

) AS rnk

FROM product_sales

) t

WHERE rnk <= 5;

📌 Question 83: Find Customers Whose Latest Order Is Their Highest Value Order

Table: orders (customer_id, order_date, amount)

WITH customer_orders AS (

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date DESC

) AS latest_order,

RANK() OVER (

PARTITION BY customer_id

ORDER BY amount DESC

) AS highest_order

FROM orders

)

SELECT

customer_id,

order_date,

amount

FROM customer_orders

WHERE latest_order = 1

AND highest_order = 1;

📌 Question 84: Calculate Rolling 30-Day Revenue

Table: sales (sale_date, amount)

SELECT

sale_date,

SUM(amount) OVER (

ORDER BY sale_date

RANGE BETWEEN INTERVAL '29 days' PRECEDING

AND CURRENT ROW

) AS rolling_30_day_revenue

FROM sales;

📌 Question 85: Find Products Purchased by Exactly One Customer

Table: orders (customer_id, product_id)

SELECT

product_id

FROM orders

GROUP BY product_id

HAVING COUNT(DISTINCT customer_id) = 1;

📌 Question 86: Find the Longest Inactive Period for Each Customer

Table: orders (customer_id, order_date)

WITH gaps AS (

SELECT

customer_id,

order_date,

order_date -

LAG(order_date) OVER (

PARTITION BY customer_id

ORDER BY order_date

) AS inactive_days

FROM orders

)

SELECT

customer_id,

MAX(inactive_days) AS longest_gap

FROM gaps

GROUP BY customer_id;

📌 Question 87: Calculate Revenue Share by Product Category

Tables: products (product_id, category) sales (product_id, amount)

SELECT

category,

SUM(amount) AS revenue,

ROUND(

100.0 * SUM(amount) /

SUM(SUM(amount)) OVER (),

2

) AS revenue_share

FROM sales s

JOIN products p

ON s.product_id = p.product_id

GROUP BY category

ORDER BY revenue DESC;