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 
)


for each player: find the number of interrupted sequences ending in a goal in which the player participated

select me.match_id
,      me.seq
,      me.player
,      case me.team
       when 'A' then m.team_a
       when 'B' then m.team_b
       end team
,      me.action
from   match_events me
       join
       matches m
       on (me.match_id = m.id)


with player_actions as
( select me.match_id
  ,      me.seq
  ,      me.player
  ,      case me.team
         when 'A' then m.team_a
         when 'B' then m.team_b
         end team
  ,      me.action
  from   match_events me
         join
         matches m
         on (me.match_id = m.id)
)         
select match_id
, mr.match_num
, mr.seq
, mr.team
, mr.player
from   player_actions  
match_recognize
(
  partition by match_id
  ORDER BY seq
  MEASURES MATCH_NUMBER() AS match_num
  ALL ROWS PER MATCH
  AFTER MATCH SKIP TO LAST GOAL
     PATTERN (POSSESSION+ GOAL)
     DEFINE
       POSSESSION AS POSSESSION.team = FIRST(POSSESSION.team),
       GOAL AS GOAL.action='G' and GOAL.team = POSSESSION.team
) MR



with player_actions as
( select me.match_id
  ,      me.seq
  ,      me.player
  ,      case me.team
         when 'A' then m.team_a
         when 'B' then m.team_b
         end team
  ,      me.action
  from   match_events me
         join
         matches m
         on (me.match_id = m.id)
)         
select team
,      player
,      count(distinct match_id||'XX'||match_num) assists_and_goals
from   ( select mr.match_id
,      mr.team
,      mr.player
,      match_num
from    player_actions
match_recognize
(
  partition by match_id
  ORDER BY seq
  MEASURES MATCH_NUMBER() AS match_num
  ,        seq as seq
  ALL ROWS PER MATCH
  AFTER MATCH SKIP TO LAST GOAL
     PATTERN (POSSESSION+ GOAL)
     DEFINE
       POSSESSION AS POSSESSION.team = FIRST(POSSESSION.team),
       GOAL AS GOAL.action='G' and GOAL.team = POSSESSION.team
) MR
) player_goal_contributions
group
by     team
,      player
order 
by     assists_and_goals desc

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