𝟳 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗧𝗼 𝗘𝗻𝗿𝗼𝗹𝗹 𝗜𝗻 𝟮𝟬𝟮𝟲😍 

✅ 100% FREE & Beginner-Friendly
✅ Learn AI, ML, Data Science, Ethical Hacking & More
✅ Taught by Industry Experts
✅ Practical & Hands-on Learning

📢 Start learning today and take your tech career to the next level! 🚀

𝐋𝐢𝐧𝐤 👇:- 
 
https://pdlink.in/4bQ6FpS
 
Enroll For FREE & Get Certified 🎓

🚀 Real-World SQL Scenario Based Interview Questions with Answers

📌 Question 1: Find Customers Who Purchased in Consecutive Months

Table: orders customer_id, order_date

Requirement: Identify customers who placed orders in consecutive months.

WITH monthly_orders AS (
SELECT DISTINCT
customer_id,
DATE_TRUNC('month', order_date) AS order_month
FROM orders
),
consecutive_orders AS (
SELECT
customer_id,
order_month,
LAG(order_month) OVER (
PARTITION BY customer_id
ORDER BY order_month
) AS prev_month
FROM monthly_orders
)
SELECT customer_id
FROM consecutive_orders
WHERE order_month = prev_month + INTERVAL '1 month';

📌 Question 2: Find the Top 3 Customers by Revenue Each Month
Table: orders customer_id, amount, order_date

WITH customer_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
customer_id,
SUM(amount) AS revenue
FROM orders
GROUP BY 1, 2
)

SELECT *
FROM (
SELECT *,
DENSE_RANK() OVER (
PARTITION BY month
ORDER BY revenue DESC
) AS rnk
FROM customer_revenue
) t
WHERE rnk <= 3;

📌 Question 3: Calculate Running Total Revenue

Table: sales sale_date, amount
Requirement: Show cumulative revenue over time.

SELECT
sale_date,
amount,
SUM(amount) OVER (
ORDER BY sale_date
) AS running_revenue
FROM sales;

📌 Question 4: Find Users Who Have Not Logged In During the Last 30 Days
Tables: users user_id, logins user_id, login_date

SELECT u.user_id
FROM users u
LEFT JOIN logins l
ON u.user_id = l.user_id
GROUP BY u.user_id
HAVING MAX(login_date) < CURRENT_DATE - INTERVAL '30 days'
OR MAX(login_date) IS NULL;

📌 Question 5: Detect Duplicate Transactions

Table: transactions transaction_id, customer_id, amount, transaction_date
Requirement: Find duplicate transactions based on customer, amount, and date.

SELECT
customer_id,
amount,
transaction_date,
COUNT(*) AS duplicate_count
FROM transactions
GROUP BY customer_id, amount, transaction_date
HAVING COUNT(*) > 1;

📌 Question 6: Calculate Average Order Value by Month

Table: orders order_id, amount, order_date

SELECT
DATE_TRUNC('month', order_date) AS month,
ROUND(AVG(amount), 2) AS avg_order_value
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

📌 Question 7: Find the Most Recent Order for Each Customer
Table: orders order_id, customer_id, order_date

WITH ranked_orders AS (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC
) AS rn
FROM orders
)
SELECT
customer_id,
order_id,
order_date
FROM ranked_orders
WHERE rn = 1;

📌 Question 8: Calculate Product Contribution to Total Revenue

Table: sales product_id, amount
Requirement: Find percentage contribution of each product.

SELECT
product_id,
SUM(amount) AS revenue,
ROUND(
100.0 * SUM(amount) /
SUM(SUM(amount)) OVER (),
2
) AS contribution_pct
FROM sales
GROUP BY product_id;

📌 Question 9: Find Customers with No Orders
Tables: customers customer_id, orders customer_id

SELECT c.customer_id
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

📌 Question 10: Calculate 7-Day Moving Average Sales

Table: sales sale_date, amount

SELECT
sale_date,
amount,
ROUND(
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
),
2
) AS moving_avg_7_days
FROM sales;

❤️ Double Tap For More

📊 𝗧𝗖𝗦 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀

Here's an amazing opportunity from TCS to learn essential data analytics skills completely FREE and earn a certificate

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4waJYWJ

🔥 Data Analytics continues to be one of the most in-demand career paths, and this free course is a great first step toward building job-ready skills.

⏳ Don't miss this opportunity to upskill and boost your career!

🚀 SQL Scenario Based Interview Questions with Answers Part 2

📌 Question 11: Find the Second Highest Salary in Each Department 
Table: employees (employee_id, department_id, salary)

WITH ranked_salary AS (
    SELECT *,
           DENSE_RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS rnk
    FROM employees
)
SELECT department_id, employee_id, salary
FROM ranked_salary
WHERE rnk = 2;

📌 Question 12: Identify Users Who Purchased on Their First Visit 
Tables: visits (user_id, visit_date) | orders (user_id, order_date)

WITH first_visit AS (
    SELECT user_id,
           MIN(visit_date) AS first_visit_date
    FROM visits
    GROUP BY user_id
)
SELECT DISTINCT f.user_id
FROM first_visit f
JOIN orders o
    ON f.user_id = o.user_id
   AND f.first_visit_date = o.order_date;

📌 Question 13: Find Products Never Sold 
Tables: products (product_id, product_name) | sales (product_id)

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

📌 Question 14: Calculate Month-over-Month Revenue Growth 
Table: orders (order_date, revenue)

WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', order_date) AS month,
           SUM(revenue) AS total_revenue
    FROM orders
    GROUP BY 1
)
SELECT month,
       total_revenue,
       LAG(total_revenue) OVER (ORDER BY month) AS previous_month,
       ROUND(
           100.0 *
           (total_revenue - LAG(total_revenue) OVER (ORDER BY month))
           /
           LAG(total_revenue) OVER (ORDER BY month),
           2
       ) AS growth_pct
FROM monthly_revenue;

📌 Question 15: Find Employees Earning More Than Department Average 
Table: employees (employee_id, department_id, salary)

SELECT employee_id, department_id, salary
FROM (
    SELECT *,
           AVG(salary) OVER (
               PARTITION BY department_id
           ) AS dept_avg
    FROM employees
) t
WHERE salary > dept_avg;

📌 Question 16: Find Longest Consecutive Login Streak 
Table: logins (user_id, login_date)

WITH cte AS (
    SELECT user_id,
           login_date,
           login_date -
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY login_date
           ) * INTERVAL '1 day' AS grp
    FROM logins
)
SELECT user_id, COUNT(*) AS streak_days
FROM cte
GROUP BY user_id, grp
ORDER BY streak_days DESC;

📌 Question 17: Find Peak Sales Day of Every 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 *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY DATE_TRUNC('month', sale_day)
               ORDER BY revenue DESC
           ) rn
    FROM daily_sales
) t
WHERE rn = 1;

📌 Question 18: Find Customers Who Ordered Every Month 
Table: orders (customer_id, order_date)

WITH customer_months AS (
    SELECT customer_id,
           COUNT(DISTINCT DATE_TRUNC('month', order_date)) AS months_active
    FROM orders
    GROUP BY customer_id
),
total_months AS (
    SELECT COUNT(DISTINCT DATE_TRUNC('month', order_date)) AS total_months
    FROM orders
)
SELECT customer_id
FROM customer_months c
CROSS JOIN total_months t
WHERE c.months_active = t.total_months;

📌 Question 19: Find Top Selling Product Category 
Tables: products (product_id, category) | sales (product_id, quantity)

SELECT category, SUM(quantity) AS total_sold
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY category
ORDER BY total_sold DESC
LIMIT 1;

📌 Question 20: Calculate Median Salary 
Table: employees (employee_id, salary)

SELECT PERCENTILE_CONT(0.5)
WITHIN GROUP (ORDER BY salary)
AS median_salary
FROM employees;

💡 Double Tap ❤️ For More

🎓 𝗚𝗼𝗼𝗴𝗹𝗲 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝟮𝟬𝟮𝟲 🚀

Learn job-ready skills from Google and boost your resume?🌟

✔️ Learn from Google Experts
✔️ Industry-Recognized Certificates
✔️ Beginner-Friendly Learning Paths
✔️ Self-Paced Courses
✔️ Enhance Resume & LinkedIn Profile
✔️ Build Job-Ready Skills

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4vjLGVq

⏳ Start Learning Today & Upgrade Your Career!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find employees who earn more than the average salary of their own department.

𝗠𝗲: Challenge accepted! 💪

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

💡 Explanation:

The query uses a correlated subquery to calculate the average salary for each employee's department.

• The outer query iterates through each employee.
• The inner query calculates the average salary of that employee's department.
• If an employee's salary is greater than their department's average, they're included in the result.

This is a classic SQL interview question that tests your understanding of:
✅ Correlated Subqueries
✅ Aggregate Functions (AVG)
✅ Filtering with WHERE

🎯 Expected Output Example

+----------+------------+--------+
| Employee | Department | Salary |
+----------+------------+--------+
| John     | IT         | 90,000 |
| Sarah    | HR         | 70,000 |
| David    | Finance    | 85,000 |
+----------+------------+--------+

(Only employees earning above their department's average salary.)

🚀 Correlated subqueries are asked frequently in interviews. Learn when to use them—and also know how to rewrite them using window functions for better performance on large datasets.

❤️ React with ❤️ for more SQL interview challenges!

𝗠𝗶𝗰𝗿𝗼𝘀𝗼𝗳𝘁 𝟭𝟬𝟬+ 𝗙𝗥𝗘𝗘 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 𝗳𝗼𝗿 𝗔𝘇𝘂𝗿𝗲, 𝗔𝗜, 𝗖𝘆𝗯𝗲𝗿𝘀𝗲𝗰𝘂𝗿𝗶𝘁𝘆 & 𝗠𝗼𝗿𝗲 🚀

Learn the most in-demand tech skills from Microsoft completely FREE🌟

Microsoft Learn offers 100+ free courses designed to help students, freshers, and professionals build job-ready skills in today's fastest-growing technology domains.

✅ 100% Free Learning
✅ Beginner to Advanced Levels

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4f0GNuH

🚀 Learn. Practice. Upskill. Get Career Ready

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:

You have 2 minutes to solve this SQL query.

Find the employees who have the highest salary in each department.

𝗠𝗲: Challenge accepted! 💪

SELECT
    employee_id,
    employee_name,
    department,
    salary
FROM (
    SELECT
        employee_id,
        employee_name,
        department,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department
            ORDER BY salary DESC
        ) AS rnk
    FROM employees
) ranked
WHERE rnk = 1;

💡 Explanation:

This query uses the DENSE_RANK() window function to rank employees by salary within each department.

• PARTITION BY department creates separate rankings for each department.

• ORDER BY salary DESC ranks the highest salary as 1.

• DENSE_RANK() ensures that if multiple employees have the same highest salary, they all receive Rank 1.

• The outer query filters only the employees with rnk = 1.

This question tests your knowledge of:

✅ Window Functions

✅ DENSE_RANK() vs RANK() vs ROW_NUMBER()

✅ Partitioning Data

🎯 Output Example

Employee | Department | Salary

John | IT | 95,000

Sarah | HR | 80,000

David | Finance | 90,000

Alice | IT | 95,000

(John and Alice both appear because they share the highest salary in the IT department.)

🚀 Whenever an interview question asks for the top N records per group, think of window functions.

DENSE_RANK(), RANK(), and ROW_NUMBER() are among the most commonly tested SQL concepts.

❤️ React with ❤️ for more SQL interview challenges!

📊 𝗣𝘄𝗖 𝗶𝘀 𝗼𝗳𝗳𝗲𝗿𝗶𝗻𝗴 𝗮 𝗙𝗥𝗘𝗘 𝗣𝗼𝘄𝗲𝗿 𝗕𝗜 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗣𝗿𝗼𝗴𝗿𝗮𝗺

This helps tolearn data visualization, dashboard creation, KPI analysis, and business intelligence skills that companies actively look for.

✅ Free Certificate
✅ Self-Paced Learning
✅ Hands-On Power BI Projects
✅ Beginner Friendly
✅ Resume & LinkedIn Boost

Don't miss this opportunity to add an in-demand skill to your profile and stand out from the crowd! 💼🔥

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4g5sKFa

Share with yours friends who wants to start a career in Data Analytics

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find employees whose salary is higher than their manager's salary.

Assume the table structure is:

employees(employee_id, employee_name, manager_id, salary)

𝗠𝗲: Challenge accepted! 💪

SELECT
e.employee_id,
e.employee_name,
e.salary AS employee_salary,
m.employee_name AS manager_name,
m.salary AS manager_salary
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

💡 Explanation:

This query uses a self join because both employees and managers are stored in the same table.

• e represents the employee.
• m represents the manager.
• The join matches each employee with their manager using manager_id.
• The WHERE clause filters employees whose salary is greater than their manager's salary.

This question tests your understanding of:
✅ Self Joins
✅ Aliases (e and m)
✅ Comparing values across related rows

🎯 Expected Output Example

Employee Employee Salary Manager Manager Salary

John 90,000 David 80,000
Sarah 85,000 Michael 75,000


🚀 Self joins are one of the most frequently asked SQL interview topics. Practice scenarios involving employees, managers, organizational hierarchies, categories, and parent-child relationships.

❤️ React with ❤️ for more SQL interview challenges!

📊 𝗙𝗥𝗘𝗘 𝗧𝗮𝘁𝗮 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗩𝗶𝗿𝘁𝘂𝗮𝗹 𝗜𝗻𝘁𝗲𝗿𝗻𝘀𝗵𝗶𝗽 | 𝗪𝗶𝘁𝗵 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗲 🚀

Here's an amazing opportunity to complete the FREE Tata Data Analytics Virtual Internship and earn a certificate that you can showcase on your Resume and LinkedIn.

✅ 100% FREE
✅ Self-Paced & Online
✅ Beginner-Friendly
✅ Certificate on Completion
✅ Real Business Case Studies
✅ Resume & LinkedIn Boost

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4eybW8J

🚀 Upskill Today. Build Your Portfolio. Get Career Ready!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:

You have 2 minutes to solve this SQL query.

Find the top 3 highest-paid employees in each department.

𝗠𝗲: Challenge accepted! 💪

SELECT

    employee_id,

    employee_name,

    department,

    salary

FROM (

    SELECT

        employee_id,

        employee_name,

        department,

        salary,

        DENSE_RANK() OVER (

            PARTITION BY department

            ORDER BY salary DESC

        ) AS salary_rank

    FROM employees

) ranked

WHERE salary_rank <= 3

ORDER BY department, salary DESC;

💡 Explanation:

The query uses the DENSE_RANK() window function to rank employees based on salary within each department.

• PARTITION BY department creates a separate ranking for every department.

• ORDER BY salary DESC ranks the highest salary first.

• DENSE_RANK() assigns the same rank to employees with identical salaries.

• The outer query returns only employees with a rank of 3 or less.

This question tests your understanding of:

✅ Window Functions

✅ DENSE_RANK()

✅ Top N per Group

✅ Partitioning Data

🎯 Expected Output Example

Employee  Department Salary Rank

John  IT  95,000 1

Alice  IT  95,000 1

Bob  IT  90,000 2

Mike  IT  85,000 3

Sarah  HR  80,000 1

🚀 Know when to use each ranking function:

• ROW_NUMBER() → No ties (unique ranking)

• RANK() → Leaves gaps after ties

• DENSE_RANK() → No gaps after ties (ideal for Top N with ties)

❤️ React with ❤️ for more SQL interview challenges!

📊 𝗙𝗥𝗘𝗘 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘁𝗶𝗰𝘀 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗡𝗼 𝗘𝘅𝗽𝗲𝗿𝗶𝗲𝗻𝗰𝗲 𝗡𝗲𝗲𝗱𝗲𝗱! 🚀

Want to start a career in Data Analytics but don't know where to begin?

These 5 FREE beginner-friendly courses will help you learn the most in-demand data skills and build a strong foundation.

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/3SOk64h

🚀 Start Learning Today. Build Your Portfolio. Land Your Dream Data Job!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find customers who have never placed an order.

Tables:

customers(customer_id, customer_name)

orders(order_id, customer_id, order_date)


𝗠𝗲: Challenge accepted! 💪

SELECT
c.customer_id,
c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

💡 Explanation:

This query uses a LEFT JOIN to return all customers, whether or not they have placed an order.

• LEFT JOIN keeps every customer in the result.
• Customers without matching records in the orders table will have NULL values.
• The WHERE o.customer_id IS NULL condition filters only customers who have never placed an order.

This question tests your understanding of:
✅ LEFT JOIN
✅ Finding missing records
✅ NULL handling

🎯 Expected Output Example

Customer ID Customer Name

105 Alice
112 David
118 Sarah


🚀 Questions about finding unmatched records are very common in interviews. Practice using:

- LEFT JOIN ... IS NULL

- NOT EXISTS

- NOT IN (carefully, because NULL values can affect results)

Among these, NOT EXISTS is often preferred for correctness and performance in many databases.

❤️ React with ❤️ for more SQL interview challenges!

𝗧𝗖𝗦 𝗙𝗥𝗘𝗘 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗢𝗻 𝗗𝗮𝘁𝗮 𝗠𝗮𝗻𝗮𝗴𝗲𝗺𝗲𝗻𝘁 - 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘😍

TCS iON is offering a FREE Master Data Management Course with a Certificate,

✅ 100% FREE Learning
✅ Certificate on Completion
✅ Self-Paced Online Course
✅ Beginner-Friendly Content
✅ Industry-Relevant Skills
✅ Resume & LinkedIn Profile Boost

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlink.in/4jGFBw0

🚀 Start Learning Today. Upskill for Free. Get Career Ready!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the department with the highest average salary.

𝗠𝗲: Challenge accepted! 💪

SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department
ORDER BY average_salary DESC
LIMIT 1;

💡 Explanation:
The query calculates the average salary for each department and returns the department with the highest average salary.

• GROUP BY department groups employees by department
• AVG(salary) calculates the average salary for each department
• ORDER BY average_salary DESC sorts departments from highest to lowest average salary
• LIMIT 1 returns only the top department

This question tests your understanding of:
✅ GROUP BY
✅ Aggregate Functions AVG
✅ ORDER BY
✅ LIMIT

🎯 Expected Output
Department Average_Salary
IT 88,500

🚀 Bonus Handles Ties
If multiple departments share the highest average salary, use DENSE_RANK():

SELECT
department,
average_salary
FROM (
SELECT
department,
AVG(salary) AS average_salary,
DENSE_RANK() OVER (
ORDER BY AVG(salary) DESC
) AS rnk
FROM employees
GROUP BY department
) ranked
WHERE rnk = 1;

This version returns all departments tied for the highest average salary.

🚀 Whenever you see questions like highest, lowest, top N, or rank, think beyond LIMIT. Ask yourself: What if there's a tie? Window functions like DENSE_RANK() often provide a more complete solution.

❤️ React with ❤️ for more SQL interview challenges!

🚀 𝗡𝗩𝗜𝗗𝗜𝗔 𝗙𝗥𝗘𝗘 𝗔𝗜 𝗖𝗲𝗿𝘁𝗶𝗳𝗶𝗰𝗮𝘁𝗶𝗼𝗻 𝗖𝗼𝘂𝗿𝘀𝗲𝘀 | 𝗟𝗲𝗮𝗿𝗻 𝗙𝗿𝗼𝗺 𝗔𝗜 𝗜𝗻𝗱𝘂𝘀𝘁𝗿𝘆 𝗟𝗲𝗮𝗱𝗲𝗿𝘀

Want to build cutting-edge *AI skills* from one of the world's leading AI and GPU companies?

*NVIDIA* offers *FREE AI Certification Courses* to help students, freshers, developers, and professionals

🔗 𝗘𝗻𝗿𝗼𝗹𝗹 𝗙𝗼𝗿 𝗙𝗥𝗘𝗘👇:

https://pdlinks.in/nvdia

🚀 Start Learning Today. Earn Your Certificate. Build Your Future in AI!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find employees who earn the same salary as at least one other employee in the same department.

𝗠𝗲: Challenge accepted! 💪
SELECT
employee_id,
employee_name,
department,
salary
FROM employees
WHERE (department, salary) IN (
SELECT
department,
salary
FROM employees
GROUP BY department, salary
HAVING COUNT(*) > 1
)
ORDER BY department, salary DESC;

💡 Explanation:
The query identifies duplicate salary values within each department.

• The subquery groups records by department and salary.
• **HAVING COUNT(*) > 1** finds salary values that appear more than once in the same department.
• The outer query returns all employees whose (department, salary) matches those duplicate combinations.

This question tests your understanding of:
✅ GROUP BY
✅ HAVING
✅ Multi-column filtering
✅ Identifying duplicate records

🎯 Expected Output Example
Employee Department Salary
John IT 80,000
Alice IT 80,000
David HR 65,000
Sarah HR 65,000

🚀 Alternative Using Window Functions
SELECT
employee_id,
employee_name,
department,
salary
FROM (
SELECT
*,
COUNT(*) OVER (
PARTITION BY department, salary
) AS salary_count
FROM employees
) t
WHERE salary_count > 1;

This approach avoids a subquery with GROUP BY and is a great way to showcase your knowledge of window functions.

🚀 When interview questions ask you to find duplicates, think of these three approaches:

1. GROUP BY + HAVING
2. Window functions COUNT() OVER
3. Self Join for specific comparison scenarios

Knowing multiple solutions demonstrates strong SQL problem-solving skills.

❤️ React with ❤️ for more SQL interview challenges!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the third highest salary from the employees table without using LIMIT or TOP.

𝗠𝗲: Challenge accepted! 💪

SELECT
salary
FROM (
SELECT
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) ranked
WHERE salary_rank = 3;

💡 Explanation:
This query uses the DENSE_RANK() window function to rank distinct salary values in descending order.

• ORDER BY salary DESC assigns Rank 1 to the highest salary.
• DENSE_RANK() ensures duplicate salaries receive the same rank.
• The outer query filters for salary_rank = 3, returning the third highest distinct salary.

🎯 Expected Output Example
Salary Rank
95,000 1
90,000 2
85,000 3

Output:
Third Highest Salary
85,000

🚀 Alternative Solution Using a Correlated Subquery
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (
SELECT COUNT(DISTINCT salary)
FROM employees e2
WHERE e2.salary > e1.salary
);

This solution avoids window functions and is commonly asked to test your understanding of correlated subqueries.

🚀 Questions involving the Nth highest or Nth lowest value are interview favorites. Be comfortable solving them using:

1. DENSE_RANK()
2. RANK()
3. Correlated Subqueries
4. Common Table Expressions (CTEs)

Being able to provide multiple approaches leaves a strong impression on interviewers.

❤️ React with ❤️ for more interview challenges!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the employees who joined in the last 30 days.

Assume the table structure:
employees(employee_id, employee_name, department, joining_date)

𝗠𝗲: Challenge accepted! 💪

SELECT
employee_id,
employee_name,
department,
joining_date
FROM employees
WHERE joining_date >= CURRENT_DATE - INTERVAL '30 days';

💡 Explanation:
This query filters employees whose joining date falls within the last 30 days.

• CURRENT_DATE returns today's date.
• INTERVAL '30 days' subtracts 30 days from the current date.
• The WHERE clause returns employees who joined on or after that date.

This question tests your understanding of:
✅ Date Functions
✅ Date Arithmetic
✅ Filtering Records with Dates

🎯 Expected Output Example
Employee Department Joining Date
John IT 2026-06-10
Sarah HR 2026-06-22

🚀 Database-Specific Alternatives

MySQL
SELECT *
FROM employees
WHERE joining_date >= CURDATE() - INTERVAL 30 DAY;

SQL Server
SELECT *
FROM employees
WHERE joining_date >= DATEADD(DAY, -30, GETDATE());

Oracle
SELECT *
FROM employees
WHERE joining_date >= SYSDATE - 30;

🚀 Tip for SQL Job Seekers:
Date-related SQL questions are very common. Make sure you're comfortable with:
• Finding records from the last N days
• Current month/year filters
• Date differences
• Date formatting
• Database-specific date functions

❤️ React with ❤️ for more SQL interview challenges!

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿:
You have 2 minutes to solve this SQL query.

Find the customer(s) who placed the highest number of orders.

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

𝗠𝗲: Challenge accepted! 💪

SELECT
    customer_id,
    customer_name,
    total_orders
FROM (
    SELECT
        c.customer_id,
        c.customer_name,
        COUNT(o.order_id) AS total_orders,
        DENSE_RANK() OVER (
            ORDER BY COUNT(o.order_id) DESC
        ) AS rnk
    FROM customers c
    JOIN orders o
        ON c.customer_id = o.customer_id
    GROUP BY
        c.customer_id,
        c.customer_name
) ranked
WHERE rnk = 1;

💡 Explanation:
The query first counts the total number of orders placed by each customer and then ranks them based on the order count.

✅ COUNT(o.order_id) calculates the number of orders per customer.

✅ GROUP BY ensures one row per customer.

✅ DENSE_RANK() ranks customers from highest to lowest order count.

✅ The outer query returns all customers with rnk = 1, including ties.

This question tests your understanding of:

✅ JOIN

✅ GROUP BY

✅ Aggregate Functions COUNT

✅ Window Functions DENSE_RANK

🎯 Expected Output Example

+----------+--------------+
| Customer | Total Orders |
+----------+--------------+
| John     | 25           |
| Sarah    | 25           |
+----------+--------------+

Both customers are returned because they are tied for the highest number of orders.

🚀 Tip for SQL Job Seekers:
Whenever an interview asks for the highest, lowest, most, or least, think about whether multiple records could tie for first place. Using DENSE_RANK() instead of LIMIT 1 makes your solution more robust and interview-ready.

❤️ React with ❤️ for more SQL interview challenges!