Find departments with more than 1 employee AND average salary above 70000.

Problem Statement

<p>Find <code>departments</code> with more than 1 employee AND average salary above 70000.</p>

Examples

Input: employees table: +----+---------+------------+--------+ | id | name | department | salary | +----+---------+------------+--------+ | 1 | Alice | IT | 90000 | | 2 | Bob | IT | 80000 | | 3 | Charlie | HR | 60000 | | 4 | Dana | Sales | 75000 | | 5 | Eve | Sales | 80000 | +----+---------+------------+--------+

Output: +------------+-----+--------------------+ | department | cnt | avg_sal | +------------+-----+--------------------+ | Sales | 2 | 77500.000000000000 | | IT | 2 | 85000.000000000000 | +------------+-----+--------------------+

Explanation: Only records matching the specified criteria are returned in the final result.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Identify the grouping dimension(s) and which columns require aggregate functions (such as COUNT, SUM, AVG, MIN, or MAX). 💡 Hint 2: Add the GROUP BY clause for all non-aggregated columns listed in the SELECT projection. 💡 Hint 3: If filtering groups, use HAVING; otherwise use WHERE before grouping: SELECT <group_col>, <AGG>(...) FROM <table> GROUP BY <group_col>;

Editorial & Approach

Problem Overview & Intuition

To solve "HAVING with Multiple Conditions", we query the relational database engine using declarative SQL. The goal is to find departments with more than 1 employee and average salary above 70000. By formulating an optimal execution plan with appropriate projection and filtering, the database engine executes the query with minimal overhead.

Step-by-Step Approach

Analyze Schema: Identify the target tables, necessary foreign keys, and expected output columns.
Construct Filtering & Logic: Apply grouping & aggregates to isolate the requested data.
Format & Order: Ensure columns match the expected project schema in order.

Optimal Implementation (SQL)

SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM employees GROUP BY department HAVING COUNT(*) > 1 AND AVG(salary) > 70000;

Complexity Analysis

Time Complexity O(N log N) for sorting or partitioning rows.

Space Complexity O(N) for intermediate group hash tables or window buffers.

Key Considerations & Edge Cases

Empty Tables: The query executes safely returning zero rows without syntax error.
NULL Values: Columns containing NULL values are properly handled by standard ANSI SQL semantics.
Case Sensitivity: String comparisons and keywords adhere to PostgreSQL/standard SQL rules.

HAVING with Multiple Conditions

Medium

Find departments with more than 1 employee AND average salary above 70000.

Example Scenarios

1Example 1

Input:

employees table

id	name	department	salary
1	Alice	IT	90000
2	Bob	IT	80000
3	Charlie	HR	60000
4	Dana	Sales	75000
5	Eve	Sales	80000

Output:

department	cnt	avg_sal
Sales	2	77500.000000000000
IT	2	85000.000000000000

Explanation:

Only records matching the specified criteria are returned in the final result.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.