Modal salary

SQL data engineering interview problem. Difficulty: advanced. Pattern: Aggregation. About 18 minutes. Part of the Pro drill bank.

The mode is the value that appears most often. Here you want the most common salary in the company. Find the salary that appears most often among the salaries in employees that are not NULL. Return salary and freq (how many times it appears). If several salaries tie for the highest frequency, return all of them. Sort by salary.

Requirements

  • Count how many times each salary appears.
  • Keep the salary (or salaries) with the highest count.
  • If there is a tie for the highest count, return every tied salary.
  • Return salary and freq, sorted by salary.

Constraints

  • The table is employees.
  • Ignore NULL salaries.
  • Return only these 2 columns, in this order: salary, freq.

Examples

Input: employees (salary freq) name | salary Bob Kumar | 95000 Eve Ng | 95000 Dan Park | 110000 Omar Ali | 105000 Output: salary | freq 95000 | 2 Why this passes: Mode is the salary with the highest frequency; 95000 appears twice.

Topics: lakebench, sql, mode, having.

More SQL interview questions · All interview problems · Learn data engineering

advanced

Modal salary

Interview-style drill: Most frequently occurring salary.

Find the salary that appears most often among non-null `employees.salary` values. Return `salary` and `freq`. If there is a tie for most frequent, return all tied salaries. Order by `salary`.