Suraz is planning to create his dream IPL team, So he wants to select the players into three teams randomly. Help Suraz to choose the players randomly.
Write an SQL query to randomly group players into three teams, Each team should have one all-rounder, spin bowler, wicket-keeper, and two batsmen, and a fast bowler.
We have been given two table teams and a Player table. Teams table has two entities team_name and team_id. The player table has three entities player_name,player_id, and role.
Sample Output:-
My Solution:-
Before jumping to the final solution I would suggest you give it a try and then jump to the solution.
I have implemented the solution using the NTILE windowing function. This function will help me to partition the player table into 3 groups based on the player roles randomly(as I have used newid() in Order By clause).
Final Query:-
Thanks for reading. I hope you enjoyed this fun little case study - it was fun for me to create!. You can also share your approach to solve this problem.
