07 | March | 2018 | Improving my SQL BI Skills

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

Improving my SQL BI Skills

Daily Archives: March 7, 2018

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

SQL PUZZLE | SUM EVERY 3 rows for each Id Puzzle | SQL Interview Question

Share this

Share this