1️⃣ Retrieve Employees with the Highest Salary in Each Department (Full Details)
Table: Employees
Columns: id, name, dept, salary
SELECT *
FROM Employees e
WHERE salary = (
SELECT MAX(salary)
FROM Employees
WHERE dept = e.dept
);
🧠 _Use case:_ Get full employee details, not just the salary, for top earners per department.
2️⃣ Find Departments Without Employees
Tables: Departments (id, name), Employees (id, name, dept_id)
SELECT d.*
FROM Departments d
LEFT JOIN Employees e ON d.id = e.dept_id
WHERE e.id IS NULL;
🧠 _Use case:_ Identify departments that haven’t been staffed yet.
3️⃣ Rank Employees by Salary Within Department (With Ties)
Table: Employees
SELECT id, name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_rank
FROM Employees;
🧠 _Use case:_ Useful for performance reviews or compensation analysis.
4️⃣ Find Consecutive Login Days Per User
Table: Logins (user_id, login_date)
SELECT user_id, login_date,
DATEDIFF(login_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp
FROM Logins;
🧠 _Use case:_ Group by user_id and grp to find streaks of consecutive logins.
5️⃣ Get Employees with the Minimum Salary in the Company
Table: Employees
SELECT *
FROM Employees
WHERE salary = (SELECT MIN(salary) FROM Employees);
🧠 _Use case:_ Identify underpaid or entry-level employees.
6️⃣ Find Managers Who Don’t Have Any Direct Reports
Table: Employees (id, name, manager_id)
SELECT *
FROM Employees
WHERE id NOT IN (
SELECT DISTINCT manager_id
FROM Employees
WHERE manager_id IS NOT NULL
);
🧠 Use case: Spot inactive or placeholder managers.
💬 Double Tap ❤️ For More