## Problem Given two tables: ```sql employees(id INT, name VARCHAR, department_id INT, salary DECIMAL) departments(id INT, name VARCHAR) ``` Write queries for each of the following: **Q1:** For each department, return the department name, average salary, and the name of the highest-paid employee. **Q2:** Return employees whose salary is above the average salary of their own department. **Q3:** For each department, rank employees by salary descending. Return all employees with their rank. **Example output for Q3:** ``` dept_name | emp_name | salary | rank -----------+----------+--------+----- Engineering| Alice | 120000 | 1 Engineering| Bob | 95000 | 2 Marketing | Carol | 85000 | 1 ``` ## Follow-ups 1. What is the difference between `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()`? 2. Rewrite Q2 without a subquery using a window function. 3. How would you find the second-highest salary per department? 4. If the employees table has 10M rows, how do you optimize Q1?

InterviewDB Question

Employee Salaries - SQL Aggregation and Ranking Query

phone

Question Details

Problem Given two tables: Write queries for each of the following: Q1: For each department, return the department name, average salary, and the name of the highest-paid employee. Q2: Return employees whose salary is above the average salary of their…

Full Details

🔒

Unlock full leaked content

Full insider details, leaked discussions, and candidate experiences.

Get Premium →

Topics

Coding Phone

More from Rippling

Reddit Rippling SDE-2 Phone Screening (Reject) 1p3a Rippling Software Engineer Interview Experience: AI Coding Rounds and Offer Reddit Rippling Fullstack SWE Intern Interview 1p3a Rippling Fulltime SDE Video Interview Experience: Expense System Challenge 1p3a Rippling Senior SDE Onsite Interview System Design and Coding

← All Rippling questions Rippling Software Engineer Questions → All Arrays Questions →