user3348557 user3348557 - 5 months ago 11
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.

Answer

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;
Comments