user3348557 - 1 year ago 70
SQL Question

# How to find the most common group of players

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.

I think the easiest way to get the most common team composition is to use `listagg()` to bring the members together:

``````select players, count(*) as numevents
from (select listagg(player_id, ',') within group (order by player_id) as players
from t
group by eventkey, team_id
) p
group by players
order by count(*) desc;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download