🚀 SQL Business Scenario Interview Questions with Answers Part 6

📌 Question 51: Find Customers Who Haven't Ordered in the Last 90 Days

Tables: customers (customer_id), orders (customer_id, order_date)

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id
HAVING MAX(order_date) < CURRENT_DATE - INTERVAL '90 days'
    OR MAX(order_date) IS NULL;

📌 Question 52: Calculate Average Revenue Per User ARPU

Tables: users (user_id), orders (user_id, amount)

SELECT
ROUND(
    SUM(amount) * 1.0 /
    COUNT(DISTINCT u.user_id),
    2
) AS arpu
FROM users u
LEFT JOIN orders o
ON u.user_id = o.user_id;

📌 Question 53: Find the Fastest Selling Product

Table: inventory (product_id, launch_date)
Table: sales (product_id, sale_date)

Requirement: Find the product with the fewest days from launch to first sale.

WITH first_sale AS (
SELECT
    product_id,
    MIN(sale_date) AS first_sale_date
FROM sales
GROUP BY product_id
)
SELECT
i.product_id,
(first_sale_date - launch_date) AS days_to_sell
FROM inventory i
JOIN first_sale f
ON i.product_id = f.product_id
ORDER BY days_to_sell
LIMIT 1;

📌 Question 54: Find Customers Who Purchased in Every Quarter

Table: orders (customer_id, order_date)

WITH customer_quarters AS (
SELECT
customer_id,
COUNT(
DISTINCT DATE_TRUNC('quarter', order_date)
) AS quarter_count
FROM orders
GROUP BY customer_id
),
total_quarters AS (
SELECT COUNT(
DISTINCT DATE_TRUNC('quarter', order_date)
) AS total_quarters
FROM orders
)
SELECT customer_id
FROM customer_quarters
CROSS JOIN total_quarters
WHERE quarter_count = total_quarters;

📌 Question 55: Find Revenue Contribution of Top 10 Customers

Table: orders (customer_id, amount)

WITH customer_revenue AS (
SELECT
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY customer_id
)
SELECT
SUM(revenue) AS top10_revenue,
ROUND(
100.0 * SUM(revenue) /
(
SELECT SUM(amount)
FROM orders
),
2
) AS contribution_pct
FROM (
SELECT revenue
FROM customer_revenue
ORDER BY revenue DESC
LIMIT 10
) t;

📌 Question 56: Find Products Never Returned

Tables:
products (product_id)
sales (order_id, product_id)
returns (order_id)

SELECT DISTINCT
p.product_id
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
LEFT JOIN returns r
ON s.order_id = r.order_id
WHERE r.order_id IS NULL;

📌 Question 57: Calculate Daily Revenue Growth

Table: sales (sale_date, amount)

WITH daily_sales AS (
SELECT
sale_date,
SUM(amount) AS revenue
FROM sales
GROUP BY sale_date
)
SELECT
sale_date,
revenue,
ROUND(
100.0 *
(
revenue -
LAG(revenue) OVER(
ORDER BY sale_date
)
/
LAG(revenue) OVER(
ORDER BY sale_date
),
2
) AS growth_pct
FROM daily_sales;

📌 Question 58: Find the Most Loyal Customers

Table: orders (customer_id, order_date)

Requirement: Customers who placed orders in the highest number of distinct months.

SELECT
customer_id,
COUNT(
DISTINCT DATE_TRUNC('month', order_date)
) AS active_months
FROM orders
GROUP BY customer_id
ORDER BY active_months DESC
LIMIT 10;

📌 Question 59: Find Products With Zero Sales

Tables:
products (product_id)
sales (product_id)

SELECT
p.product_id
FROM products p
LEFT JOIN sales s
ON p.product_id = s.product_id
WHERE s.product_id IS NULL;

📌 Question 60: Calculate Average Orders Per Customer

Table: orders (customer_id)

SELECT
ROUND(
COUNT(*) * 1.0 /
COUNT(DISTINCT customer_id),
2
) AS avg_orders_per_customer
FROM orders;

❤️ Double Tap For More