SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.
For employees with non-null department and salary, return: name, department, salary, rank = RANK(), dense_rank = DENSE_RANK() Order by department, salary descending, name. Return columns in this order: name, department, salary, row_num, rank, dense_rank.
Input: employees (Engineering slice) name | department | salary Alice Chen | Engineering | 120000 Dan Park | Engineering | 110000 Bob Kumar | Engineering | 95000 Eve Ng | Engineering | 95000 Output: name | department | salary | row_num | rank | dense_rank Alice Chen | Engineering | 120000 | 1 | 1 | 1 Dan Park | Engineering | 110000 | 2 | 2 | 2 Bob Kumar | Engineering | 95000 | 3 | 3 | 3 Eve Ng | Engineering | 95000 | 4 | 3 | 3 Why this passes: Each user's eligible order totals are aggregated; users with higher totals appear earlier in the ranked output.
Topics: lakebench, sql, rank, dense_rank, row_number.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: Show all three ranking functions within each department.
For employees with non-null department and salary, return: `name`, `department`, `salary`, `row_num` = ROW_NUMBER(), `rank` = RANK(), `dense_rank` = DENSE_RANK() Partition by department, order by salary descending, then name. Order by `department`, `salary` descending, `name`.