SQL Puzzle | The MIN, MAX, AVG, Last and First Value – SINGLE SELECT Puzzle

In this puzzle the requirement is you have to write a SINGLE T-SQL to get Minimum balance, Average balance, Maximum Balance and last and first value of balance per month and account. Please see expected output and input for more details. . For more details please check the sample input and expected output.

Sample Input

Id Acct Months Bal
1 10011 1 345
2 10011 1 122
3 10011 1 190
4 10011 2 111
5 10011 3 2300
6 10011 3 87820
7 10012 1 345
8 10012 1 190
9 10012 3 5000
10 10012 3 1500
11 10012 3 7000

Expected Output

Id Acct Months Bal MinimumBalance AverageBalance MaximumBalance FirstValue LastValue
1 10011 1 345 122 219.000000 345 345 190
2 10011 1 122 122 219.000000 345 345 190
3 10011 1 190 122 219.000000 345 345 190
4 10011 2 111 111 111.000000 111 111 111
5 10011 3 2300 2300 45060.000000 87820 2300 87820
6 10011 3 87820 2300 45060.000000 87820 2300 87820
7 10012 1 345 190 267.500000 345 345 190
8 10012 1 190 190 267.500000 345 345 190
9 10012 3 5000 1500 4500.000000 7000 5000 7000
10 10012 3 1500 1500 4500.000000 7000 5000 7000
11 10012 3 7000 1500 4500.000000 7000 5000 7000

Script

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

 ```-- CREATE TABLE GetMinMaxAvgVal ( Id INT ,Acct INT ,Months INT ,Bal INT ) GO INSERT INTO GetMinMaxAvgVal VALUES (1, 10011, 1, 345), (2, 10011, 1, 122), (3, 10011, 1, 190), (4, 10011, 2, 111), (5, 10011, 3, 2300), (6, 10011, 3, 87820), (7, 10012, 1, 345), (8, 10012, 1, 190), (9, 10012, 3, 5000), (10, 10012, 3, 1500), (11, 10012, 3, 7000) 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 | USING MIN, MAX, AVG and First_Value with PARTITIONS

 ```-- SELECT * , MIN(Bal) OVER(PARTITION BY Acct,Months) MinimumBalance , AVG(Bal*1.0) OVER(PARTITION BY Acct,Months) AverageBalance , MAX(Bal) OVER(PARTITION BY Acct,Months) MaximumBalance , FIRST_VALUE(Bal) OVER(PARTITION BY Acct,Months ORDER BY Id ASC) FirstValue , FIRST_VALUE(Bal) OVER(PARTITION BY Acct,Months ORDER BY Id DESC) LastValue FROM GetMinMaxAvgVal ORDER By Id -- ```

Output-1

 ```-- Id Acct Months Bal MinimumBalance AverageBalance MaximumBalance FirstValue LastValue ----------- ----------- ----------- ----------- -------------- -------------------- -------------- ----------- ----------- 1 10011 1 345 122 219.000000 345 345 190 2 10011 1 122 122 219.000000 345 345 190 3 10011 1 190 122 219.000000 345 345 190 4 10011 2 111 111 111.000000 111 111 111 5 10011 3 2300 2300 45060.000000 87820 2300 87820 6 10011 3 87820 2300 45060.000000 87820 2300 87820 7 10012 1 345 190 267.500000 345 345 190 8 10012 1 190 190 267.500000 345 345 190 9 10012 3 5000 1500 4500.000000 7000 5000 7000 10 10012 3 1500 1500 4500.000000 7000 5000 7000 11 10012 3 7000 1500 4500.000000 7000 5000 7000 (11 rows affected) -- ```

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