Divide employees into 3 buckets (quartiles) based on salary.

Problem Statement

<p>Divide <code>employees</code> into 3 buckets (quartiles) based on salary.</p>

Examples

Input: employees table: +----+---------+--------+ | id | name | salary | +----+---------+--------+ | 1 | Alice | 50000 | | 2 | Bob | 60000 | | 3 | Charlie | 80000 | | 4 | Dana | 90000 | | 5 | Eve | 100000 | | 6 | Frank | 120000 | +----+---------+--------+

Output: +---------+--------+--------+ | name | salary | bucket | +---------+--------+--------+ | Alice | 50000 | 1 | | Bob | 60000 | 1 | | Charlie | 80000 | 2 | | Dana | 90000 | 2 | | Eve | 100000 | 3 | | Frank | 120000 | 3 | +---------+--------+--------+

Explanation: The window function evaluates the calculation across the partition in the specified ordering.

Complexity

Time Complexity: -

Space Complexity: -

Hints

💡 Hint 1: Use a window function with OVER (...) to compute values across rows related to the current row without collapsing them like GROUP BY. 💡 Hint 2: Check if PARTITION BY is required to split windows by category or if only an ORDER BY inside OVER (...) is needed. 💡 Hint 3: Structure the query as: <window_func>() OVER (PARTITION BY ... ORDER BY ...) AS <alias>;

Editorial & Approach

Problem Overview & Intuition

To solve "Window Function: NTILE", we query the relational database engine using declarative SQL. The goal is to divide employees into 3 buckets (quartiles) based on salary. 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 row projections to isolate the requested data.
Format & Order: Sort the resulting records according to specified order criteria.

Optimal Implementation (SQL)

SELECT name, salary, NTILE(3) OVER (ORDER BY salary) AS bucket FROM employees;

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.

Window Function: NTILE

Medium

Divide employees into 3 buckets (quartiles) based on salary.

Example Scenarios

1Example 1

Input:

employees table

id	name	salary
1	Alice	50000
2	Bob	60000
3	Charlie	80000
4	Dana	90000
5	Eve	100000
6	Frank	120000

Output:

name	salary	bucket
Alice	50000	1
Bob	60000	1
Charlie	80000	2
Dana	90000	2
Eve	100000	3
Frank	120000	3

Explanation:

The window function evaluates the calculation across the partition in the specified ordering.

SQL Editor

Loading Editor...

Query Results

Run a query to see results here.