Tags

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


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