Companies Whose Average Employee Salary Is Above 40,000

A company directory keeps its employees, companies and salaries in three tables. Write a query that returns the name of every company whose employees' average salary is strictly greater than 40,000. The report names the three tables (`EMPLOYEE`, `COMPANY` and `SALARY`), the strict 40,000 threshold and the company name as the output, and suggests ordering by company id so that the output order is stable. It does not list the tables' columns, so this version fixes the schema below, with exactly one salary row per employee. ### Input Table(s) **`employee`**: one row per employee. | Column | Type | Meaning | | --- | --- | --- | | `id` | INTEGER | Primary key; unique per employee | | `name` | TEXT | Employee name | **`company`**: one row per company. | Column | Type | Meaning | | --- | --- | --- | | `id` | INTEGER | Primary key; unique per company | | `name` | TEXT | Company name; two different companies may share a name | **`salary`**: one row per employee. | Column | Type | Meaning | | --- | --- | --- | | `employee_id` | INTEGER | Primary key; references `employee.id` | | `company_id` | INTEGER | References `company.id`; the company the employee works for | | `salary` | INTEGER | The employee's salary, a positive whole number | ### Output Contract Write one read-only PostgreSQL query (`SELECT`, optionally with `WITH`) that returns one row per qualifying company, with exactly this column: | Column | Type | Meaning | | --- | --- | --- | | `name` | text | The qualifying company's `company.name` | A company qualifies when the average of its employees' salaries is strictly greater than 40,000. Order the rows by company `id` ascending. The id is not an output column, but it is unique, so the order is fully determined. The result may have zero rows. ### Example `employee`: | id | name | | --- | --- | | 1 | Avery | | 2 | Blake | | 3 | Casey | | 4 | Devon | | 5 | Emery | | 6 | Finley | | 7 | Gray | `company`: | id | name | | --- | --- | | 1 | Northwind | | 2 | Harbor Mutual | | 3 | Summit Data | | 4 | Northwind | | 5 | Lakeside Labs | `salary`: | employee_id | company_id | salary | | --- | --- | --- | | 1 | 1 | 52000 | | 2 | 1 | 30000 | | 3 | 2 | 40000 | | 4 | 2 | 40000 | | 5 | 3 | 40000 | | 6 | 3 | 40001 | | 7 | 4 | 45000 | Expected result: | name | | --- | | Northwind | | Summit Data | | Northwind | - Northwind (id 1): salaries 52,000 and 30,000 average 41,000, which is above the threshold, so it is returned. - Harbor Mutual (id 2): the average is exactly 40,000, which is not strictly greater, so it is excluded. - Summit Data (id 3): the average is 40,000.5, which is strictly greater, so it is returned. - Northwind (id 4): a different company that shares a name with id 1. Its only employee earns 45,000, so it is returned as its own row. - Lakeside Labs (id 5): no employees, so it has no average and is excluded. The rows appear in company id order: 1, 3, 4. ### Constraints and Clarifications - No column contains NULL. `salary.employee_id` is unique and every employee appears in `salary` exactly once, so each employee has one salary and works for one company. Every `employee_id` and `company_id` in `salary` exists in its parent table. - A company's average is the exact arithmetic mean of its employees' salaries, with no rounding or truncation before the comparison: an average of 40,000.5 qualifies, and an average of exactly 40,000 does not. - A company with no rows in `salary` has no employees and no average, and is not returned. - Companies that share a name are evaluated separately, and each qualifying one appears as its own row. - If no company qualifies, or the tables are empty, the result has no rows. ```hint Test the boundary Before submitting, check what your query returns for a company whose average is exactly 40,000 and for one whose average is 40,000.5. ```