This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the Correlation Coefficients for the run of Cricket Players.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
PlayerName Runs MatchYear ---------- ----------- ----------- Dhoni 1200 2014 Dhoni 1300 2015 Dhoni 1100 2016 Dhoni 1800 2017 Dhoni 2000 2018 Kohli 2200 2018 Kohli 1100 2017 Kohli 1300 2016 Kohli 1500 2015 Kohli 1800 2014 Yuvraj 1000 2014 Yuvraj 900 2015 Yuvraj 1200 2016 Yuvraj 1300 2017 Yuvraj 700 2018 |
Expected Output:
|
1 2 3 4 5 |
Player1 Player2 PlayersCoefficientRuns ---------- ---------- ---------------------- Dhoni Kohli 0.332662790481163 Dhoni Yuvraj -0.311842111156846 Kohli Yuvraj -0.915321318268559 |
Sample table:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_Players (PlayerName VARCHAR(10), Runs INT, MatchYear INT) INSERT INTO tbl_Players VALUES ('Dhoni',1200, 2014),('Kohli',1800, 2014),('Yuvraj',1000, 2014) ,('Dhoni',1300, 2015),('Kohli',1500, 2015),('Yuvraj',900, 2015) ,('Dhoni',1100, 2016),('Kohli',1300, 2016),('Yuvraj',1200, 2016) ,('Dhoni',1800, 2017),('Kohli',1100, 2017),('Yuvraj',1300, 2017) ,('Dhoni',2000, 2018),('Kohli',2200, 2018),('Yuvraj',700, 2018) |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
select Player1 ,Player2 ,(Avg(Runs1 * Runs2) - (Avg(Runs1) * Avg(Runs2))) / (StDevP(Runs1) * StDevP(Runs2)) as PlayersCoefficientRuns from ( select a.PlayerName as Player1 ,a.Runs as Runs1 ,b.PlayerName as Player2 ,b.Runs as Runs2 ,a.MatchYear from tbl_Players a cross join tbl_Players b where b.PlayerName>a.PlayerName and a.MatchYear=b.MatchYear ) as t group by Player1, Player2 |
Please try the different solution for this puzzle and share it via comment...
