SQL Puzzle | Fetch 2 records from each salary bracket

Tags

SQL Puzzle | Fetch 2 records from each salary bracket

This is a good puzzle from interview stand point. In the puzzle you to break down salary data into below brackets & from each bracket return 2 records.

0-999
1000-1999
2000-2999
3000+

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

Sample Input

EmpId	EmpName	Salary
1	Pawan	1000
2	Ramesh	1100
3	Krishna	200
4	Sharlee	219
5	Isha	2000
6	Honey	576
7	Avika	7000
8	Mayank	8000
9	Pushpa	19000

Expected Output

EmpId	EmpName	Salary
3	Krishna	200
4	Sharlee	219
1	Pawan	1000
2	Ramesh	1100
5	Isha	2000
7	Avika	7000
8	Mayank	8000

Script

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

--


CREATE TABLE RangeEmployees
(

	 EmpId SMALLINT PRIMARY KEY
	,EmpName VARCHAR(10)
	,Salary INT
)
GO

INSERT INTO RangeEmployees VALUES
(1,'Pawan',1000),
(2,'Ramesh',1100),
(3,'Krishna',200),
(4,'Sharlee',219),
(5,'Isha',2000),
(6,'Honey',576),
(7,'Avika',7000),
(8,'Mayank',8000),
(9,'Pushpa',19000)
GO


--

Rules/Restrictions

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

Add your solution(s) in the comments section or send you solution(s) to

pawankkmr@gmail.com

SOLUTION – 1


--

/*
**  Solution 1
*/

SELECT A.* FROM 
(VALUES (0,999), (1000,1999), (2000,2999), (3000,2147483647)) AS t(a,b)
CROSS APPLY
(
       SELECT TOP 2 EmpId,EmpName,Salary FROM rangeemployees R where r.salary BETWEEN t.a AND t.b
)A
GO

 

--

SOLUTION – 2


--

 
/*
**  Solution 2
*/

SELECT TOP 2 * 
       FROM RangeEmployees 
       WHERE Salary between 0 and 999 
UNION ALL
       SELECT TOP 2 * 
       FROM RangeEmployees
       WHERE Salary between 1000 and 1999
UNION ALL
       SELECT TOP 2 * 
       FROM RangeEmployees
       WHERE Salary between 2000 and 2999 
UNION ALL
       SELECT TOP 2 * 
       FROM RangeEmployees
       WHERE Salary >= 3000
 

--

SOLUTION – 3


--

/*
**  Solution 3
*/

SELECT EmpId,EmpName,Salary 
FROM 
(
       select *
       , row_number() over (partition by case 
                                                            when salary < 1000 then 1 
                                                            when salary < 2000 then 2 
                                                            when salary < 3000 then 3 
                                                            else 4 end 
                                          order by (select 1)) category
       from RangeEmployees
)A 
where category < 3
GO
 

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com

1 thought on “SQL Puzzle | Fetch 2 records from each salary bracket”

revank219 said:

May 18, 2021 at 4:38 PM

with cte as (
select *,case when salary >=’0′ and salary =’1000′ and salary =’2000′ and salary <= '2999' then 'High_category'
else 'Very_high_category' end as salary_category from RangeEmployees
),cte1 as (
select *,ROW_NUMBER()over(partition by salary_category order by salary asc)rnk from cte
)select empid,empname,salary from cte1 where rnk <=2 order by salary

LikeLike

M	T	W	T	F	S	S
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30

Improving my SQL BI Skills

SQL Puzzle | Fetch 2 records from each salary bracket

1 thought on “SQL Puzzle | Fetch 2 records from each salary bracket”

Leave a comment Cancel reply

Share this

Related

1 thought on “SQL Puzzle | Fetch 2 records from each salary bracket”

Leave a comment Cancel reply