I am trying to find the frequency or count of player compositions using SQL. Here is my dataset
event key team_id player_id
12235384031441051453625936 1 27
12235384031441051453625936 1 11
12235384031441051453625936 1 42
12235384031441051453625936 1 15
12235384031441051453625936 1 6
12235384031441051453625936 2 30
12235384031441051453625936 2 35
12235384031441051453625936 2 9
12235384031441051453625936 2 6
12235384031441051453625936 2 28
12235384031441051453626082 1 14
12235384031441051453626082 1 28
12235384031441051453626082 1 27
12235384031441051453626082 1 9
12235384031441051453626082 1 6
12235384031441051453626082 2 11
12235384031441051453626082 2 27
12235384031441051453626082 2 42
12235384031441051453626082 2 15
12235384031441051453626082 2 6
So for each event, there players on team 1 and team 2, the player_ids represent a player. I have roughly 500 events, and a lot of these players will appear in multiple events, but not always with the same players. Every team will always have 5 players, and the event keys represent a game. So I basically have 500 of the dataset above.
I am trying to find the most common team composition. For example, using the dataset above, I see players 27,11,42,15,6 in event ending in 936 and event ending in 082. So based on THIS dataset, this team composition has a COUNT of 2.
I am trying to get an output that counts the amount of times a group of players appear in the same team together and I am struggling with coming up with a coherent statement that does this.
Aucun commentaire:
Enregistrer un commentaire