Finding Median Rows per Partition
Given a table
Employee with columns id (INT, PK), company (VARCHAR), and salary (INT), write a SQL query to identify the specific rows that represent the median salary for each company. Rules for median calculation: Sort salaries in ascending order for each company. In case of identical salaries, use id as a secondary ascending sort key to break ties. If a company has an odd number of employees, return the single middle row. If a company has an even number of employees, return the two middle rows. The result should include the id, company, and salary columns for these specific median records.PostgreSQLCTEWindow Function
10