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
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. and 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 multiple events. However, I do not know how many times this composition appears.
I am trying to wrack my puny brain and I think partition by is something that is needed. I'm trying to put a "count" on each team composition to see how many times it appear, but I am struggling with putting together a coherent statement.
