Advanced SQL With Partitions | Dataford Interview Questions

Your question is Advanced SQL With Partitions. 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

Meesho's People Analytics team wants a department-level view of salary equity and compensation ranges. Write a PostgreSQL query using CTEs and window functions to compare average salaries by gender and identify the second-highest distinct salary in each department.

Requirements

Calculate the average non-null salary for each gender within each department.
Return the gender with the higher average salary. Return Tie when the averages are equal and No gender data when no valid gender salary exists.
Return the second-highest distinct salary in each department. If fewer than two distinct salaries exist, return NULL.
Exclude rows with a null salary or null gender from gender comparisons, while considering all non-null salaries for salary ranking.
Include departments with no employees, and order the result by department_id.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
department_id	INT	Department assigned to the employee
gender_of_employee	VARCHAR(30)	Reported employee gender
salary	NUMERIC(12,2)	Annual employee salary