Which player has the most direct AND indirect assists? An indirect assist is found for a player when that player is part of a sequence of uninterupted ball possession by his team ending in a goal. create table match_events ( match_id number(10) not null , seq number(10) not null , team varchar2(1) -- A or B, A for home and B for away , player number(2) not null -- uniquely identifying the player in his own team , action varchar2(1) -- P (pass), G (goal), O(out), C(corner) ) create table matches ( id number(10) not null , team_a varchar2(100) not null , team_b varchar2(100) not null , kickoff timestamp ) insert into matches ( id, team_a, team_b) values (1, 'NL', 'EN') / insert into matches ( id, team_a, team_b) values (2, 'DE', 'NL') / insert into matches ( id, team_a, team_b) values (3, 'NL', 'FR') / insert into match_events ( match_id , seq, team, player , action ) values (1,1,'A',1,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,2,'A',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,3,'A',9,'O'); insert into match_events ( match_id , seq, team, player , action ) values (1,4,'B',13,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,5,'B',3,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,6,'A',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,7,'A',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,8,'A',11,'G'); insert into match_events ( match_id , seq, team, player , action ) values (1,9,'B',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,10,'B',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,11,'B',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,12,'B',11,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,13,'A',2,'C'); insert into match_events ( match_id , seq, team, player , action ) values (1,14,'B',11,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,15,'B',10,'G'); insert into match_events ( match_id , seq, team, player , action ) values (1,16,'A',10,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,17,'A',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,18,'A',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (1,19,'A',7,'G'); insert into match_events ( match_id , seq, team, player , action ) values (2,1,'A',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,2,'A',3,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,3,'A',8,'O'); insert into match_events ( match_id , seq, team, player , action ) values (2,4,'B',14,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,5,'B',3,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,6,'B',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,7,'B',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,8,'B',11,'G'); insert into match_events ( match_id , seq, team, player , action ) values (2,9,'A',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,10,'B',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,11,'B',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,12,'B',11,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,13,'A',2,'C'); insert into match_events ( match_id , seq, team, player , action ) values (2,14,'B',11,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,15,'A',4,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,16,'A',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,17,'A',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,18,'A',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,19,'A',7,'G'); insert into match_events ( match_id , seq, team, player , action ) values (2,23,'B',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,24,'B',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,25,'B',4,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,26,'B',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,27,'B',1,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,28,'B',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (2,29,'A',7,'G'); insert into match_events ( match_id , seq, team, player , action ) values (3,1,'A',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,2,'A',6,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,3,'A',9,'O'); insert into match_events ( match_id , seq, team, player , action ) values (3,4,'B',3,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,5,'B',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,6,'B',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,7,'B',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,8,'B',2,'G'); insert into match_events ( match_id , seq, team, player , action ) values (3,9,'A',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,10,'A',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,11,'A',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,12,'A',10,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,13,'A',2,'G'); insert into match_events ( match_id , seq, team, player , action ) values (3,21,'A',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,22,'A',6,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,23,'A',9,'O'); insert into match_events ( match_id , seq, team, player , action ) values (3,24,'B',3,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,25,'B',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,26,'B',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,27,'B',5,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,28,'B',2,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,29,'A',8,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,30,'A',9,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,31,'A',7,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,32,'A',10,'P'); insert into match_events ( match_id , seq, team, player , action ) values (3,33,'A',2,'P'); resource: Explanation of measing the ball possession http://leastthing.blogspot.nl/2012/02/how-ball-possession-is-measured-in.html with goals (match_id, players, team, seq, root_seq) as ( select me.match_id , '+'||me.player , case me.team when 'A' then m.team_a when 'B' then m.team_b end team , me.seq , me.seq from match_events me join matches m on (m.id = me.match_id) where me.action= 'G' UNION ALL select goals.match_id , goals.players ||'+'||me.player , goals.team , me.seq , goals.root_seq from match_events me join matches m on (m.id = me.match_id) join goals on ( me.seq = goals.seq -1 and me.match_id = goals.match_id and case me.team when 'A' then m.team_a when 'B' then m.team_b end = goals.team ) ) , players as ( select distinct me.match_id , me.player , case me.team when 'A' then m.team_a when 'B' then m.team_b end team from match_events me join matches m on (m.id = me.match_id) ) select p.player , p.team , count(distinct g.root_seq||g.match_id) goals_and_assists from goals g join players p on (g.match_id = p.match_id and p.team = g.team and instr(g.players, '+'||p.player) >0 ) group by p.player , p.team order by goals_and_assists desc