🚀 SQL Scenario-Based Interview Questions with Answers: Part-7

📌 Question 61: Find the Longest Purchase Streak

Table: orders (customer_id, order_date)

Requirement: Find the longest consecutive daily purchase streak for each customer.

WITH purchase_days AS (

SELECT DISTINCT

customer_id,

order_date

FROM orders

),

streaks AS (

SELECT

customer_id,

order_date,

order_date -

ROW_NUMBER() OVER (

PARTITION BY customer_id

ORDER BY order_date

) * INTERVAL '1 day' AS grp

FROM purchase_days

)

SELECT

customer_id,

COUNT(*) AS longest_streak

FROM streaks

GROUP BY customer_id, grp

ORDER BY longest_streak DESC;

📌 Question 62: Find Products Purchased by More Than 80% of Customers

Tables:

products (product_id)

orders (customer_id, product_id)

SELECT

product_id

FROM orders

GROUP BY product_id

HAVING COUNT(DISTINCT customer_id) >=

(

SELECT COUNT(DISTINCT customer_id) * 0.80

FROM orders

);

📌 Question 63: Find the Highest Revenue Day for Each Month

Table: sales (sale_date, amount)

WITH daily_sales AS (

SELECT

DATE(sale_date) AS sale_day,

SUM(amount) AS revenue

FROM sales

GROUP BY DATE(sale_date)

)

SELECT

month,

sale_day,

revenue

FROM (

SELECT

DATE_TRUNC('month', sale_day) AS month,

sale_day,

revenue,

DENSE_RANK() OVER (

PARTITION BY DATE_TRUNC('month', sale_day)

ORDER BY revenue DESC

) AS rnk

FROM daily_sales

) t

WHERE rnk = 1;

📌 Question 64: Calculate Customer Purchase Frequency

Table: orders (customer_id, order_date)

Requirement: Average number of days between consecutive orders.

WITH purchase_gap AS (

SELECT

customer_id,

order_date,

LAG(order_date) OVER (

PARTITION BY customer_id

ORDER BY order_date

) AS previous_order

FROM orders

)

SELECT

customer_id,

ROUND(

AVG(order_date - previous_order),

2

) AS avg_days_between_orders

FROM purchase_gap

WHERE previous_order IS NOT NULL

GROUP BY customer_id;

📌 Question 65: Find Customers Who Bought Only One Product Category

Tables:

orders (customer_id, product_id)

products (product_id, category)

SELECT

customer_id

FROM orders o

JOIN products p

ON o.product_id = p.product_id

GROUP BY customer_id

HAVING COUNT(DISTINCT category) = 1;

📌 Question 66: Calculate Revenue by Week

Table: sales (sale_date, amount)

SELECT

DATE_TRUNC('week', sale_date) AS week,

SUM(amount) AS revenue

FROM sales

GROUP BY DATE_TRUNC('week', sale_date)

ORDER BY week;

📌 Question 67: Find Customers with the Highest Average Order Value

Table: orders (customer_id, amount)

SELECT

customer_id,

ROUND(AVG(amount), 2) AS avg_order_value

FROM orders

GROUP BY customer_id

ORDER BY avg_order_value DESC

LIMIT 10;

📌 Question 68: Find the Month with the Highest Number of New Customers

Table: users (user_id, signup_date)

SELECT

DATE_TRUNC('month', signup_date) AS month,

COUNT(*) AS new_customers

FROM users

GROUP BY DATE_TRUNC('month', signup_date)

ORDER BY new_customers DESC

LIMIT 1;