🚀 SQL Scenario-Based Interview Questions with Answers Part 10

📌 Question 91: Find the Top 3 Customers Contributing 50% of Total Revenue

Table: orders (customer_id, amount)

WITH customer_revenue AS (

SELECT

customer_id,

SUM(amount) AS revenue

FROM orders

GROUP BY customer_id

),

ranked AS (

SELECT

customer_id,

revenue,

SUM(revenue) OVER (ORDER BY revenue DESC) AS running_revenue,

SUM(revenue) OVER () AS total_revenue

FROM customer_revenue

)

SELECT

customer_id,

revenue

FROM ranked

WHERE running_revenue <= total_revenue * 0.50

LIMIT 3;

📌 Question 92: Find the First Product Purchased by Every Customer

Table: orders (customer_id, product_id, order_date)

WITH ranked_orders AS (

SELECT *,

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date

) AS rn

FROM orders

)

SELECT

customer_id,

product_id,

order_date

FROM ranked_orders

WHERE rn = 1;

📌 Question 93: Find Users Who Logged In Every Week for the Last 12 Weeks

Table: logins (user_id, login_date)

SELECT

user_id

FROM logins

WHERE login_date >= CURRENT_DATE - INTERVAL '84 days'

GROUP BY user_id

HAVING COUNT(

DISTINCT DATE_TRUNC('week', login_date)

) = 12;

📌 Question 94: Find the Most Profitable Product

Tables:

products (product_id, cost_price)

sales (product_id, selling_price, quantity)

SELECT

s.product_id,

SUM(

(selling_price - cost_price) * quantity

) AS profit

FROM sales s

JOIN products p

ON s.product_id = p.product_id

GROUP BY s.product_id

ORDER BY profit DESC

LIMIT 1;

📌 Question 95: Find the Longest Continuous Subscription

Table: subscriptions (user_id, start_date, end_date)

SELECT

user_id,

MAX(end_date - start_date) AS subscription_days

FROM subscriptions

GROUP BY user_id

ORDER BY subscription_days DESC

LIMIT 1;

📌 Question 96: Calculate Revenue Lost Due to Returned Orders

Tables:

orders (order_id, amount)

returns (order_id)

SELECT

SUM(o.amount) AS lost_revenue

FROM orders o

JOIN returns r

ON o.order_id = r.order_id;

📌 Question 97: Find Customers Who Bought the Same Product More Than Once

Table: orders (customer_id, product_id)

SELECT

customer_id,

product_id,

COUNT() AS purchase_count

FROM orders

GROUP BY customer_id, product_id

HAVING COUNT() > 1;

📌 Question 98: Find the Peak Sales Month for Every Year

Table: sales (sale_date, amount)

WITH monthly_sales AS (

SELECT

EXTRACT(YEAR FROM sale_date) AS year,

DATE_TRUNC('month', sale_date) AS month,

SUM(amount) AS revenue

FROM sales

GROUP BY

EXTRACT(YEAR FROM sale_date),

DATE_TRUNC('month', sale_date)

)

SELECT

year,

month,

revenue

FROM (

SELECT *,

DENSE_RANK() OVER (

PARTITION BY year

ORDER BY revenue DESC

) AS rnk

FROM monthly_sales

) t

WHERE rnk = 1;

📌 Question 99: Find Customers Who Purchased All Products

Tables:

customers (customer_id)

products (product_id)

orders (customer_id, product_id)