SQL Puzzle | Break Table in Equal Parts

In this puzzle you have to break the table in equal parts. Let’s say you have 14 rows in the table and the ask was to break the table in 3 equal parts – of course in the first 2 parts we will have 5 rows and in the last part we will have 4 rows.

Please check out the sample input values and sample expected output below.

Sample Input

EmpId EmpName Salary
1 a 1000
2 b 900
3 c 100
4 d 1100
5 e 1300
6 f 700
7 g 330
8 h 800
9 i 500
10 j 340
11 k 600
12 l 700
13 m 1000
14 n 1800

Expected Output

Grouper EmpSalaryRanges Counts
1 100-600 5
2 700-1000 5
3 1000-1800 4

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE EmpRangers ( EmpId INT ,EmpName VARCHAR(10) ,Salary INT ) GO INSERT INTO EmpRangers VALUES (1,'a',1000), (2,'b',900), (3,'c',100), (4,'d',1100), (5,'e',1300), (6,'f',700), (7,'g',330), (8,'h',800), (9,'i',500), (10,'j',340), (11,'k',600), (12,'l',700), (13,'m',1000) GO INSERT INTO EmpRangers VALUES (14,'n',1800) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

 ``` -- ;WITH CTE AS ( SELECT Salary ,NTILE(3) OVER (ORDER BY Salary) Grouper FROM EmpRangers ) SELECT Grouper, CONCAT(MIN(Salary),'-',MAX(Salary)) EmpSalaryRanges, COUNT(*) Counts FROM CTE GROUP BY Grouper -- ```