1. Find Duplicate Records in a Table
SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
2. Find the Second Highest Salary
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
3. Customers with More Than 3 Orders in Last 30 Days
SELECT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(*) > 3;
4. Calculate Monthly Revenue
SELECT DATE_TRUNC('month', sale_date) AS month,
SUM(amount) AS monthly_revenue
FROM sales
GROUP BY month
ORDER BY month;5. Find Employees Without Managers
SELECT *
FROM employees
WHERE manager_id IS NULL;
6. Join Two Tables and Filter by Amount
SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.amount > 100;
7. Use CASE for Conditional Logic
SELECT name,
CASE
WHEN score >= 90 THEN 'Excellent'
WHEN score >= 75 THEN 'Good'
ELSE 'Needs Improvement'
END AS rating
FROM students;
8. Find Top-Selling Products
SELECT product_id, SUM(quantity) AS total_sold
FROM sales
GROUP BY product_id
ORDER BY total_sold DESC
LIMIT 5;
9. Identify Inactive Users
SELECT user_id
FROM users
WHERE last_login < CURRENT_DATE - INTERVAL '90 days';
๐ Calculate Conversion Rate
SELECT COUNT(*) FILTER (WHERE status = 'converted') * 100.0 / COUNT(*) AS conversion_rate
FROM leads;
๐ก Pro Tip: Practice these with real datasets and explain your logic clearly in interviews.
๐ฌ Tap โค๏ธ if this helped you prep smarter!