Forum

Forums
SQL
Interview Questions...
Advanced SQL Interv...
 
Share:
Notifications
Clear all

Advanced SQL Interview Questions for Data Analyst Roles in Cognizant & Capgemini (2025 Edition)

 
Interview Questions & Answers
Last Post by SQL Admin 4 months ago
1 Posts
1 Users
0 Reactions
317 Views
RSS
Posts: 134
 SQL Admin
Admin
Topic starter
Nov 09, 2025 5:36 am
(@sql-admin)
Reputable Member
Joined: 6 years ago

Preparing for Data Analyst interviews at Cognizant or Capgemini in 2025?
These advanced SQL interview questions reflect the latest real-world problems asked by MNCs to assess your analytical mindset, data transformation skills, and optimization knowledge.

This post covers complex joins, window functions, subqueries, CTEs, and performance tuning techniques that data professionals face in analytics projects.

🔹 Top Advanced SQL Interview Questions & Answers (2025 Edition)

1️⃣ Explain the difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

Answer:
These are window functions used for ranking:

 
SELECT Name, Salary,
RANK() OVER(ORDER BY Salary DESC) AS Rnk,
DENSE_RANK() OVER(ORDER BY Salary DESC) AS DenseRnk,
ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RowNum
FROM Employees;

  • RANK() skips numbers on ties

  • DENSE_RANK() doesn’t skip

  • ROW_NUMBER() gives a unique sequence

2️⃣ How do you find top 3 employees by department?

 
SELECT * FROM (
SELECT Department, Name, Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY Salary DESC) AS rn
FROM Employees
) t WHERE rn <= 3;

✅ Frequently asked in Cognizant Data Analyst rounds.

3️⃣ What are CTEs and how are they useful?

A CTE (Common Table Expression) improves query readability and can be recursive.
Example:

 
WITH DeptCTE AS (
SELECT Department, COUNT(*) AS EmpCount
FROM Employees
GROUP BY Department
)
SELECT * FROM DeptCTE WHERE EmpCount > 5;

4️⃣ How do you optimize SQL queries for faster performance?

  • Create indexes on join/filter columns

  • Replace subqueries with JOINs

  • Use EXPLAIN PLAN to identify bottlenecks

  • Limit SELECT to required columns

  • Avoid SELECT * and unnecessary ORDER BY

5️⃣ How do you identify duplicate rows in a table?

 
SELECT Name, COUNT(*)
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;

To remove duplicates but keep one:

 
DELETE FROM Employees
WHERE RowID NOT IN (
SELECT MIN(RowID) FROM Employees GROUP BY Name, Salary
);

6️⃣ What is the difference between correlated and non-correlated subqueries?

  • Non-correlated: Executes once.

  • Correlated: Runs per row of outer query.
    Example:

 
SELECT e.Name
FROM Employees e
WHERE e.Salary > (
SELECT AVG(Salary) FROM Employees WHERE Department = e.Department
);

7️⃣ How can you find running totals or cumulative sums?

 
SELECT Name, Salary,
SUM(Salary) OVER(ORDER BY EmployeeID) AS RunningTotal
FROM Employees;

➡️ Common analytics-based question in Capgemini interviews.

8️⃣ How to detect and delete orphan records?

 
SELECT c.CustomerID
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

Then delete them using the same logic.

9️⃣ What’s the difference between UNION and UNION ALL?

  • UNION removes duplicates

  • UNION ALL includes all results (faster for large datasets)

🔟 How to find the nth highest salary efficiently?

 
SELECT Salary
FROM (
SELECT Salary, DENSE_RANK() OVER(ORDER BY Salary DESC) AS rnk
FROM Employees
) t WHERE rnk = 5;

✅ Efficient and preferred by Cognizant for analytic queries.

🧩 Bonus Optimization Tip:

For analytical workloads, use:

  • CTEs instead of nested subqueries

  • Window functions instead of group joins

  • Indexes for filtering and sorting columns

  • Partitioning for large data tables

🧠 Conclusion

These advanced SQL interview questions from Cognizant and Capgemini (2025) test your ability to think like a data analyst, not just a SQL developer.
Mastering concepts like CTEs, window functions, and query optimization will help you excel in both technical and client-facing interviews.


Topic Tags
Advanced SQL Interview Qu Cognizant SQL Interview Capgemini SQL Data Analy SQL Window Functions SQL Optimization Techniq

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
Topic Tags:  Advanced SQL Interview Qu (1) , Cognizant SQL Interview (2) , Capgemini SQL Data Analy (1) , SQL Window Functions (2) , SQL Optimization Techniq (2) ,
Share: