Real World SQL Questions

Find Customers Who Purchased Exactly Two Different Products in a Single Month

Tables: Orders (order_id, customer_id, product_id, order_date)

Identify Customers Who Haven’t Made Any Purchase in the Last 6 Months
Tables: Customers (customer_id, customer_name), Orders (order_id, customer_id, order_date)

Calculate the Percentage of Orders Delivered Later Than Expected
Tables: Orders (order_id, order_date, expected_delivery_date, actual_delivery_date)

List the Top 3 Products per Category Based on Revenue
Tables: Products (product_id, category), Sales (sale_id, product_id, amount)

Calculate Each Customer’s Lifetime Spending (Customer Lifetime Value)
Tables: Customers (customer_id), Orders (order_id, customer_id, order_date, amount)

Find Employees Who Have Changed Departments More Than Twice
Tables: Employee_Dept_History (employee_id, department, start_date, end_date)

Identify Users Who Made Their First Purchase During a Promotional Campaign
Tables: Users (user_id), Orders (order_id, user_id, order_date, promo_applied)

Find Days Where Total Sales Decreased More Than 20% Compared to the Previous Day
Tables: Daily_Sales (date, total_sales_amount)

List Products That Have Never Been Out of Stock
Tables: Products (product_id, name), Inventory (product_id, inventory_date, stock_available)

Compare Average Order Values for New vs Returning Customers
Tables: Orders (order_id, customer_id, order_amount, order_date)

Source: Shakra Shamim LinkedIn Profile

1. Find the Second-Highest Salary?
2. Find Employees With the Highest Salary in Each Department?
3. Find Consecutive Login Streaks?
4. Find Customers Who Have Never Ordered?
5. Find Running Totals in a Sales Table?
6. Find the Nth Highest Salary (Generalized Solution)?
7. Find Duplicate Records in a Table?
8. Delete Duplicates, Keeping Only One Record?
9. Find Products That Were Never Sold?
10. Find Top 3 Selling Products Per Category?
11. Find the Most Frequently Ordered Product?
12. Find Gaps in a Sequence (Missing IDs in an Ordered List)?
13. Rolling Average of Sales for the Last 7 Days. ?
14. Find Users Who Placed Orders in Every Month of a Year?
15. Find Employees Who Earn More Than Their Manager?

Source: Abhishek Sahu LinkedIn Profile.