1️⃣Explain Order of Execution of SQL query
2️⃣Provide a use case for each of the functions Rank, Dense_Rank & Row_Number ( 💡
majority struggle )
3️⃣Write a query to find the cumulative sum/Running Total
4️⃣Find the Most selling product by sales/ highest Salary of employees
5️⃣Write a query to find the 2nd/nth highest Salary of employees
6️⃣Difference between union vs union all
7️⃣Identify if there any duplicates in a table
8️⃣Scenario based Joins question, understanding of Inner, Left and Outer Joins via simple yet
tricky question
9️⃣LAG, write a query to find all those records where the transaction value is greater then
previous transaction value
1️⃣0️⃣Rank vs Dense Rank, query to find the 2nd highest Salary of employee
( Ideal soln should handle ties)
1️⃣1️⃣Write a query to find the Running Difference (Ideal sol'n using windows function)
1️⃣2️⃣Write a query to display year on year/month on month growth
1️⃣3️⃣Write a query to find rolling average of daily sign-ups
1️⃣4️⃣Write a query to find the running difference using self join (helps in understanding the
logical approach, ideally this question is solved via windows function)
1️⃣5️⃣Write a query to find the cumulative sum using self join
(helps in understanding the logical approach, ideally this question is solved via windows
function)
What are triggers in SQL and how do they work? How do you handle duplicate records in a
table?
How do you concatenate strings in SQL?
What is the purpose of the HAVING clause in SQL and how does it differ from the WHERE
clause?
Explain the concept of a self-join in SQL and when it might be used.
How do you perform data aggregation across multiple groups in SQL?
Explain the difference between a database and a schema in SQL.
What is the purpose of the ORDER BY clause in SQL and how does it work?
What are window functions? Explain with examples. How do you perform string
manipulation and pattern matching in SQL?
Explain the difference between a left outer join and a right outer join in SQL.
How do you perform data validation and data cleansing in SQL?
What is a schema in SQL and why is it used?
How do you calculate the difference between two dates in SQL?
𝐅𝐢𝐧𝐝𝐢𝐧𝐠 𝐝𝐮𝐩𝐥𝐢𝐜𝐚𝐭𝐞 𝐫𝐨𝐰𝐬 𝐢𝐧 𝐚 𝐭𝐚𝐛𝐥𝐞:
-- By using window function
SELECT * FROM ( SELECT e.*, ROW_NUMBER() OVER (PARTITION BY EmployeeID) rn FROM
Employee e ) x WHERE x.rn > 1;
By using GROUP BY
SELECT Name, COUNT(EmployeeID) FROM Employee GROUP BY Name HAVING
COUNT(EmployeeID) > 1;
𝐅𝐢𝐧𝐝𝐢𝐧𝐠 𝐭𝐡𝐞 𝐬𝐞𝐜𝐨𝐧𝐝 𝐡𝐢𝐠𝐡𝐞𝐬𝐭 𝐬𝐚𝐥𝐚𝐫𝐲:
SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM
Employee);
𝐐𝐮𝐞𝐫𝐲𝐢𝐧𝐠 𝐭𝐡𝐞 𝐞𝐦𝐩𝐥𝐨𝐲𝐞𝐞 𝐧𝐚𝐦𝐞 𝐚𝐧𝐝 𝐭𝐡𝐞𝐢𝐫 𝐦𝐚𝐧𝐚𝐠𝐞𝐫 𝐧𝐚𝐦𝐞:
SELECT e.Name AS EmployeeName, m.Name AS ManagerName FROM Employee e INNER
JOIN Employee m ON e.ManagerID = m.EmployeeID;
📌 Question 1: Finding the Third Highest Salary
🔍 SQL Query:
"Show me the third highest salary from the employee table."
📌 Question 2: Department-Wise Third Highest Salary
🔍 SQL Query:
"Let's find each department's third top earner."
📌 Question 3: Identifying Consecutive Occurrences
🔍 SQL Query:
"Spot any numbers that pop up thrice in a row."
📌 Question 4: Days with Higher Temperatures Than Previous Days
🔍 SQL Query:
"Highlight days hotter than the ones before."
📌 Question 5: Gap Filling in a Dataset
🔍 SQL Query:
"Fill in the gaps and patch the missing pieces."
📌 Question 6: Identifying Most Recent Purchase Dates per Customer
🔍 SQL Query:
"Who bought last, and when?"
📌 Question 7: Finding Days Between First and Last Purchases per Customer
🔍 SQL Query:
"How many days between a customer's first and latest buys?
Window functions
Row number
RANK
Index
Class 14,12 min
Top 10 interview questions.
https://www.youtube.com/watch?v=Iv9qBz-cyVA&t=260s
Q2
https://www.youtube.com/watch?v=Iv9qBz-cyVA&t=260s
solution
solution
Another approach
6.
7.
We need to get all order details of Shilpa even if it is case sensitive
8 solution
1. Find average department salary of employees by department wise.
Namaste sql class9, 55 min
Another approach using CTE
1. We have Winners table. Find the number of matches played,won and lost
Solution
Namaste sql class 9, 1hr 5 min
Another approach using CTE
Find top 2 salaries from each department
With CTE
Cannot use where as select statement executes at last
So,we have to use subquery
Q. find top 5 products on sales wise
Result
If they as for employee no need to do aggregation.
WE have 2 tables.find duration of each call.
Solution
Namastesql Class 11, 6 min
Sql medium complex interview problems youtube ankit bansal
Sol
Another approach
