Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL PUZZLE | SUM of Previous 3 rows for each 3 group ROW PER Id Puzzle | SQL Interview Question

In this puzzle first group the data into 3 rows for each Id

1 : First 3 rows should be combined together, then next 3 rows.
2 : If you have less then 3 rows then also we need to go ahead and combine the rows.

Once the group is set then we have to get the sum of previous 3 rows for each group.

For more details please refer sample input and expected output.

Sample Input

Id Nums Val
1 101 10
1 102 5
1 103 10
1 104 20
2 105 25
2 106 10
2 107 15
2 108 45
2 109 5
3 110 0
3 111 10
3 112 20
3 113 80
3 114 20
3 115 5
4 116 10

Expected output

id nums val Sums
1 101 10 10
1 102 5 15
1 103 10 25
1 104 20 20
2 105 25 25
2 106 10 35
2 107 15 50
2 108 45 45
2 109 5 50
3 110 0 0
3 111 10 10
3 112 20 30
3 113 80 80
3 114 20 100
3 115 5 105
4 116 10 10

Script – DDL and INSERT sample data

--

CREATE TABLE TheThreeGroups
(
	 Id INT
	,Nums INT
	,Val INT
)
GO

INSERT INTO TheThreeGroups VALUES
(1,101,10),
(1,102,5),
(1,103,10),
(1,104,20),
(2,105,25),
(2,106,10),
(2,107,15),
(2,108,45),
(2,109,5),
(3,110,0),
(3,111,10),
(3,112,20),
(3,113,80),
(3,114,20),
(3,115,5),
(4,116,10)
GO

SELECT * FROM TheThreeGroups
GO


--

SOLUTION 1 – USING SUM AND ROW_NUMBER

--

;WITH CTE AS 
(
	select *
	,ROW_NUMBER() over(partition by id order by (select 10)) as rnk
	,(ROW_NUMBER() over(partition by id order by (select 10))-1)/3 as groupid from TheThreeGroups
)
SELECT DISTINCT c.id,c.nums, c.val ,sum(c2.val) 
OVER (PARTITION BY c.id, c.nums, c.groupid) Sums
FROM cte c
INNER JOIN cte c2 on c.id=c2.id
WHERE c.rnk>=c2.rnk and c.groupid=c2.groupid
ORDER BY c.nums


--

OUTPUT 1

--

id          nums        val         Sums
----------- ----------- ----------- -----------
1           101         10          10
1           102         5           15
1           103         10          25
1           104         20          20
2           105         25          25
2           106         10          35
2           107         15          50
2           108         45          45
2           109         5           50
3           110         0           0
3           111         10          10
3           112         20          30
3           113         80          80
3           114         20          100
3           115         5           105
4           116         10          10

(16 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com