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

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

Improving my SQL BI Skills

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

Leave a comment Cancel reply

Share this

Related

Leave a comment Cancel reply