Stock Span and SQL Salary Query | Dataford Interview Questions

Your question is Stock Span and SQL Salary Query. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Avalara needs a compensation report showing employees who earn the second-highest distinct salary among active employees. Write a PostgreSQL query using a window function and a join to return all employees tied at that salary.

Requirements

Consider only employees where is_active is TRUE and salary is not NULL.
Rank distinct salary levels from highest to lowest using an appropriate window function.
Return every employee whose salary has rank 2, including ties.
Include the employee ID, employee name, salary, and Avalara department name. Sort the output by employee_id.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	References departments.department_id
salary	NUMERIC(12,2)	Annual employee salary
is_active	BOOLEAN	Whether the employee is currently active

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Avalara department name