SELECT emp_no, hired_date FROM employees WHERE hire_date>'2000-01-01';
3. Find employees whose first name is John.
SELECT emp_no, first_name FROM employees WHERE first_name='John';
4. Find employees whose last name starts with S.
SELECT emp_no, first_name, last_name FROM employees WHERE last_name LIKE 'S%';
5. Find employees whose first name contains "an".
SELECT emp_no, first_name, last_name FROM employees WHERE first_name LIKE '%an%';
6. Find all female employees.
SELECT emp_no, first_name, last_name FROM employees WHERE gender= 'F';
7. Find employees hired between two dates.
SELECT emp_no, first_name, hired_date FROM employees WHERE hire_date BETWEEN '2025-09-11' AND '205-10-11';
8. Find the 10 earliest hired employees.
SELECT emp_no, first_name, hired_date FROM employees ORDER BY hire_date asc LIMIT 10;
9. Find employees whose emp_no is between 10001 and 10100.
SELECT emp_no, first_name FROM employees WHERE emp_no BETWEEN 10001 AND 10100;
10. Find distinct employee first names.
SELECT DISTINCT first_name FROM employees;
11. Find the number of employees.
SELECT COUNT(*) as no_of_emp FROM employees;
12. Find the earliest and latest hire date.
SELECT MIN(hire_date) as earliest_hire, MAX(hire_date) as latest_hire FROM employees;
13. Find employees hired in a particular year.
SELECT emp_no, first_name, hired_date FROM employees WHERE year(hire_date)=2025;
14. Find employees whose first and last names are the same.
SELECT emp_no, first_name, last_name FROM employees WHERE first_name = last_name;
15. Sort employees by hire date, newest first.
SELECT emp_no, first_name, hire_date FROM employees ORDER BY hire_date desc;
🟡 Level 2 — Aggregations & GROUP BY
Find the number of employees by gender.
SELECT COUNT(*) as no_of_emp,gender FROM employees GROUP BY gender;
2. Find the number of employees hired each year.
SELECT COUNT(*) as no_of_emp, YEAR(hire_date) FROM employees GROUP BY YEAR(hire_date) ORDER BY YEAR(hire_date) desc;
3. Find the average salary across all employees.
SELECT AVG(salary) as avg_salary FROM salaries;
4. Find the minimum and maximum salary.
SELECT MIN(salary) as min_salary, MAX(salary) as max_salary FROM salaries;
5. Find the average salary by employee gender.
SELECT emp.gender, AVG(s.salary) as avg_salary FROM employees emp JOIN salaries s on emp.emp_no=s.emp_no GROUP BY gender;
6. Find the number of employees in each department.
SELECT dept_no, COUNT(DISTINCT emp_no) as no_of_emp FROM department GROUP BY dept_no;
7. Find the average salary in each department.
SELECT de.dept_no, AVG(s.salary) AS avg_salary FROM salaries s JOIN dept_emp de ON s.emp_no = de.emp_no GROUP BY de.dept_no;
8. Find the highest salary in each department.
SELECT de.dept_no, MAX(s.salary) AS max_salary FROM salaries s JOIN dept_emp de ON s.emp_no = de.emp_no GROUP BY de.dept_no;
## for current time ## WHERE s.to_date = '9999-01-01' ## AND de.to_date = '9999-01-01'
9. Find departments having more than 10,000 employees.
SELECT dept_no, COUNT(DISTINCT emp_no) AS no_of_emp FROM dept_emp GROUP BY dept_no HAVING COUNT(DISTINCT emp_no) > 10000;
10. Find departments where the average salary is greater than 60,000.
WITH cte as (SELECT dept.dept_no, AVG(s.salary) as avg_salary FROM salaries s JOIN dept_emp dept ON s.emp_no= dept.emp_no GROUP BY dept.dept_no) SELECT dept_no, avg_salary FROM cte WHERE avg_salary>60000;
&&
SELECT de.dept_no, AVG(s.salary) AS avg_salary FROM salaries s JOIN dept_emp de ON s.emp_no = de.emp_no GROUP BY de.dept_no HAVING AVG(s.salary) > 60000;
11. Find the number of employees hired each month.
SELECT MONTH(hire_date) as month,YEAR(hire_date) as year,COUNT(*) as no_of_emp FROM employees GROUP BY MONTH(hire_date) ## GROUP BY YEAR(hire_date), MONTH(hire_date) ORDER BY month, year;
12. Find the number of employees for each job title.
SELECT title, COUNT(*) as no_of_emp FROM titles WHERE to_date = '9999-01-01' GROUP BY title;
13. Find the average salary for each job title.
SELECT t.title, AVG(s.salary) as avg_salary FROM salaries s JOIN title t ON s.emp_no=t.emp_no GROUP BY title
14. Find the department with the highest number of employees.
SELECT dept_no, COUNT(DISTINCT emp_no) AS no_of_emp FROM dept_emp GROUP BY dept_no ORDER BY no_of_emp DESC LIMIT 1;
&&
WITH cte as (SELECT dept_no, COUNT(*) as no_of_emp FROM dept_emp GROUP BY dept_no) , ranked AS ( SELECT dept_no, no_of_emp, RANK() OVER (ORDER BY no_of_emp DESC) AS rnk FROM cte ) SELECT dept_no, no_of_emp FROM ranked WHERE rnk = 1;
15. Find the department with the highest average salary.
ELECT d.dept_no, AVG(s.salary) as avg_salary FROM salaries s JOIN dept_emp d ON s.emp_no=d.emp_no GROUP BY d.dept_no OEDER BY avg_salary desc LIMIT 1;
&&
WITH cte as (SELECT d.dept_no, AVG(s.salary) as avg_salary FROM salaries s JOIN dept_emp d ON s.emp_no=d.emp_no GROUP BY d.dept_no),
ranks as ( SELECT dept_no, avg_salary, RANK() OVER (ORDER BY avg_salary desc) as rn FROM cte)
SELECT dept_no, avg_salary FROM ranks WHERE rn=1;
🟠Level 3 — JOINs
Get employee name and their department number.
SELECT e.emp_no, e.first_name, d.dept_no FROM employees e JOIN dept_emp d ON e.emp_no=d.emp_no WHERE d.to_date = '9999-01-01';
2. Get employee name and department name.
SELECT e.emp_no, e.first_name, de.dept_no, d.dept_name FROM employees e JOIN dept_emp d ON e.emp_no=d.emp_no JOIN departments de ON de.dept_no=d.dept_no;
3. Get employee name, department name, and hire date.
SELECT e.first_name, de.dept_no, d.dept_name, e.hire_date FROM employees e JOIN dept_emp d ON e.emp_no=d.emp_no JOIN departments de ON de.dept_no=d.dept_no;
4. Find all employees currently working in each department.
SELECT e.emp_no, d.dept_no, e.to_date FROM employees e JOIN dept_emp d ON e.emp_no=d.emp_no WHERE e.todate='9999-01-01';
5. Find all employees who have worked in more than one department.
SELECT e.emp_no, d.dept_no ,count(*) as no_of_dept FROM dept_emp d JOIN employees e ON d.emp_no=e.emp_no GROUP BY emp_no HAVING count(*)>1;
6. Find employees who have never been assigned to a department.
SELECT e.emp_no, e.first_name, e.last_name FROM employees e LEFT JOIN dept_emp d ON e.emp_no = d.emp_no WHERE d.emp_no IS NULL;
7. Find departments that currently have no employees.
SELECT d.dept_no, d.dept_name FROM departments d LEFT JOIN dept_emp de ON d.dept_no = de.dept_no AND de.to_date = '9999-01-01' WHERE de.emp_no IS NULL;
8. Get employee name and current salary.
SELECT s.salary, e.first_name, s.to_date FROM employees e JOIN salaries s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01';
from pyspark.sql import functions as F df1= salaries.filter(F.col("to_date")=='9999-01-01') df= employees.join(df1,on="emp_no").select(F.col("first_name"),F.col("salary"),F.col("to_date"))
9. Get employee name, department, and current salary.
SELECT e.first_name, d.dept_name, s.salary FROM employees e JOIN salaries s ON e.emp_no= s.emp_no AND s.to_date = '9999-01-01' JOIN dept_emp de ON e.emp_no= de.emp_no AND de.to_date = '9999-01-01' JOIN departments d ON de.dept_no= d.dept_no AND d.to_date = '9999-01-01' ORDER BY s.salary
10. Find the highest-paid employee in each department.
Get Himanshi Dahiya’s stories in your inbox
Join Medium for free to get updates from this writer.
with cte as (SELECT e.emp_no, s.salary, d.dept_no, ROW_NUMBER() OVER ( PARTITION BY d.dept_no ORDER BY s.salary DESC ) AS rn FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date = '9999-01-01' JOIN dept_emp d ON e.emp_no=d.emp_no AND d.to_date = '9999-01-01' ) SELECT emp_no, salary, dept_no FROM cte WHERE rn=1;
from pyspark.sql import functions as F from pyspark.sql.window import Window df= (employees.alias("e") .join(salaries.alias("s"),(F.col("e.emp_no")==F.col("s.emp_no")) & (F.col("to_date")=='9999-01-01'), inner .join(dept_emp.alias("d"),(F.col("e.emp_no")==F.col("d.emp_no")) & (F.col("to_date")=='9999-01-01'), inner .select( F.col("e.emp_no"), F.col("s.salary"), F.col("d.dept_no")) .withColumn("rn", F.row_number().over(Window.partitionBy("dept_no").orderBy(F.col("salary").desc()) .filter(F.col("rn")==1) .drop("rn")
df.display()
11. Find the lowest-paid employee in each department.
with cte as (SELECT e.emp_no, s.salary, d.dept_no, ROW_NUMBER() OVER ( PARTITION BY d.dept_no ORDER BY s.salary ASC ) AS rn FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date = '9999-01-01' JOIN dept_emp d ON e.emp_no=d.emp_no AND d.to_date = '9999-01-01' ) SELECT emp_no, salary, dept_no FROM cte WHERE rn=1;
from pyspark.sql import functions as F from pyspark.sql.window import Window df= (employees.alias("e") .join(salaries.alias("s"),(F.col("e.emp_no")==F.col("s.emp_no")) & (F.col("to_date")=='9999-01-01'), inner .join(dept_emp.alias("d"),(F.col("e.emp_no")==F.col("d.emp_no")) & (F.col("to_date")=='9999-01-01'), inner .select( F.col("e.emp_no"), F.col("s.salary"), F.col("d.dept_no")) .withColumn("rn", F.row_number().over(Window.partitionBy("dept_no").orderBy(F.col("salary").asc()) .filter(F.col("rn")==1) .drop("rn")
df.display()
12. Find the second-highest-paid employee in each department.
with cte as (SELECT e.emp_no, s.salary, d.dept_no, ROW_NUMBER() OVER ( PARTITION BY d.dept_no ORDER BY s.salary DESC ) AS rn FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date = '9999-01-01' JOIN dept_emp d ON e.emp_no=d.emp_no AND d.to_date = '9999-01-01' ) SELECT emp_no, salary, dept_no FROM cte WHERE rn=2;
13. Find employees whose salary is greater than the average salary.
with cte as ( SELECT e.emp_no, s.salary,AVG(s.salary) over() as avg_s FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date='9999-01-01' ) SELECT emp_no, salary FROM cte WHERE salary>avg_s;
from pyspark.sql import functions F avg_s=salaries.agg(F.avg("salary").alias("average_s")).first()["average_s"] df= (employees.alias("e") .join(salaries.alias("s"), (F.col("e.emp_no")==F.col("s.emp_no")) & (F.col("to_date")=='9999-01-01').inner .filter(F.col("salary")>average_s) .select( F.col("e.emp_no"),F.col("s.salary")) df.dispaly()
14. Find employees whose salary is greater than their department’s average salary.
with cte as (SELECT e.emp_no, s.salary, d.dept_no, AVG(s.salary) OVER ( PARTITION BY d.dept_no ) AS avg_d_salary FROM employees e JOIN salaries s ON e.emp_no=s.emp_no JOIN dept_emp d ON e.emp_no=d.emp_no) SELECT emp_no, salary FROM cte WHERE salary> avg_d_salary ;
WITH cte AS ( SELECT emp_no, salary, COUNT(*) OVER (PARTITION BY salary) AS salary_count FROM salaries WHERE to_date = '9999-01-01' ) SELECT emp_no, salary FROM cte WHERE salary_count > 1;
Find employees earning more than the overall average salary.
WITH cte as (SELECT e.emp_no, e.first_name, s.salary, AVG(s.salary) over() as avg_s FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date='9999-01-01' ) SELECT emp_no, first_name, salary FROM cte WHERE salary>avg_s;
from pyspark.sql import functions as F df= employees .join((salaries, "emp_no") .filter(F.col("to_date")=='9999-01-01'))) .withColumn("avg_s", F.avg("salary").over(Window.partitionBy())) .filter(F.col("salary") > F.col("avg_s") .select(F.col("emp_no"), F.col("first_name"), F.col("salary")) df.display()
2. Find employees earning the maximum salary.
WITH cte as (SELECT e.emp_no, e.first_name, s.salary, DENSE_RANK() over(orderBy s.salary desc) as rn FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date='9999-01-01') SELECT emp_no, first_name, salary FROM cte Where rn=1;
OR
SELECT e.emp_no, e.first_name, s.salary FROM employees e JOIN salaries s ON e.emp_no = s.emp_no WHERE s.to_date = '9999-01-01' AND s.salary = ( SELECT MAX(salary) FROM salaries WHERE to_date = '9999-01-01' );
3. Find employees earning the second-highest salary.
WITH cte as (SELECT e.emp_no, e.first_name, s.salary, DENSE_RANK() over(orderBy s.salary desc) as rn FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_date='9999-01-01') SELECT emp_no, first_name, salary FROM cte Where rn=2;
4. Find employees whose salary is greater than the average salary of their department.
WITH cte as (SELECT e.emp_no, d.dept_no, s.salary, AVG(s.salary) OVER(PARTITION BY d.dept_no)) as avg_s FROM employees e JOIN salaries s ON e.emp_no=s.emp_no AND s.to_Date='9999-01-01' JOIN dept_emp d ON e.emp_no=d.emp_no AND d.to_Date='9999-01-01' ) SELECT emp_no, dept_no, salary FROM cte WHERE salary> avg_s
5.Find the department with the highest average salary.
SELECT d.dept_no, AVG(s.salary) as avg_s FROM salaries s JOIN dept_emp d ON s.emp_no=d.emp_no AND d.to_Date='9999-01-01' Group By d.dept_no Order BY AVG(s.salary) desc LIMIT 1;
6. Find employees who have the same title as employee 10001.
WITH cte AS ( SELECT title FROM titles WHERE emp_no = 10001 AND to_date = '9999-01-01' ) SELECT t.emp_no, t.title FROM titles t JOIN cte c ON t.title = c.title WHERE t.to_date = '9999-01-01' AND t.emp_no <> 10001;
from pyspark.sql import functions as F
# Get employee 10001's current title cte = ( titles .filter( (F.col("emp_no") == 10001) & (F.col("to_date") == "9999-01-01") ) .select("title") )
# Find employees having the same title df = ( titles.alias("t") .join( cte.alias("c"), F.col("t.title") == F.col("c.title") ) .filter(F.col("t.to_date") == "9999-01-01") .select( F.col("t.emp_no"), F.col("t.title") ) )
df.display()
7. Find employees who have never received a salary increase.
8. Find employees who have worked in every department? Hint: relational division.
from pyspark.sql import functions as F
# Total number of departments total_depts = departments.select("dept_no").distinct().count()
# Employees who have worked in every department df = ( dept_emp .groupBy("emp_no") .agg( F.countDistinct("dept_no").alias("dept_count") ) .filter(F.col("dept_count") == total_depts) .select("emp_no") )
df.display()
9. Find departments whose average salary is above the company-wide average salary.
with cte as ( SELECT d.dept_no, s.salary, AVG(s.salary) as avg_sal FROM salaries s JOIN dept_emp_no ON s.emp_no=d.emp_no group by dept_no ) company_avg AS ( SELECT AVG(salary) AS avg_c_salary FROM salaries WHERE to_date = '9999-01-01') SELECT c.dept_no, c.avg_sal, ca.avg_c_salary FROM cte c Where c.avg_sal> ca.avg_c_salary;
🔴 Level 5 — Window Functions
This is very important for Data Engineer interviews.
Rank employees by salary.
SELECT e.emp_no, s.salary, DENSE_RANK() over(order By salary desc)as rank FROM salaries s JOIN employees e ON s.emp_no=e.emp_no;
2. Find the top 3 highest-paid employees in each department.
WITH cte as (SELECT s.emp_no, d.dep_no, s.salary, DENSE_RANK() over(PARTITION BY dept_no order By salary desc)as rank FROM salaries s JOIN dept_emp d ON s.emp_no=d.emp_no) SELECT emp_no, dept_no, salary, rank FROM cte WHERE rank<=3 order by dept_no, salary desc;
4. Find the salary difference between an employee and the previous employee.
Find the salary difference between an employee and the next employee.
Calculate cumulative salary by employee.
Calculate cumulative salary within each department.
Calculate the average salary using a window function.
Show each employee’s salary and their department’s average salary.
Calculate each employee’s percentage contribution to total department salary.
Find employees whose salary is above the department average.
Find the top 2 salaries for every department.
🟣 Level 6 — LAG() / LEAD() / Salary History
These are especially useful because the salaries table contains salary history.
Find employees whose salary increased compared with their previous salary.
Find the amount of each salary increase.
Find the largest salary increase for each employee.
Find employees who received more than one salary increase.
Find the first salary of every employee.
Find the latest salary of every employee.
Find the salary immediately before the current salary.
Find the percentage salary increase between two salary records.
Find the employee with the largest salary increase.
Find employees whose salary decreased at some point.
####### Play around date/ timeperiod top 10%, 1% or some thing
Level 7 — CTE Questions
find employees earning above the company average.
WITH company_avg AS ( SELECT AVG(salary) AS avg_c_salary FROM salaries WHERE to_date = '9999-01-01' ) SELECT s.emp_no, s.salary, c.avg_c_salary FROM salaries s CROSS JOIN company_avg c WHERE s.to_date = '9999-01-01' AND s.salary > c.avg_c_salary;
2. Using a CTE, calculate department-wise salary statistics.
WITH cte AS ( SELECT d.dept_no, AVG(s.salary) AS avg_salary, MIN(s.salary) AS min_salary, MAX(s.salary) AS max_salary, SUM(s.salary) AS total_salary, COUNT(s.emp_no) AS employee_count FROM salaries s JOIN dept_emp d ON s.emp_no = d.emp_no WHERE s.to_date = '9999-01-01' AND d.to_date = '9999-01-01' GROUP BY d.dept_no ) SELECT dept_no, avg_salary, min_salary, max_salary, total_salary, employee_count FROM cte ORDER BY dept_no;
3. Create a CTE for current employees and join it with current salaries.
WITH current_employees AS ( SELECT e.emp_no, e.first_name, e.last_name, d.dept_no FROM employees e JOIN dept_emp d ON e.emp_no = d.emp_no WHERE d.to_date = '9999-01-01' ) SELECT ce.emp_no, ce.first_name, ce.last_name, ce.dept_no, s.salary FROM current_employees ce JOIN salaries s ON ce.emp_no = s.emp_no WHERE s.to_date = '9999-01-01';
4. Find employees whose salary is in the top 10% of their department.
WITH cte AS ( SELECT d.dept_no, s.emp_no, s.salary, NTILE(10) OVER ( PARTITION BY d.dept_no ORDER BY s.salary DESC ) AS salary_bucket FROM salaries s JOIN dept_emp d ON s.emp_no = d.emp_no WHERE s.to_date = '9999-01-01' AND d.to_date = '9999-01-01' ) SELECT dept_no, emp_no, salary FROM cte WHERE salary_bucket = 1 ORDER BY dept_no, salary DESC;
