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 🙂

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 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/