statistics

Player list in the team 1:

select p.name from teams_players as tp
left join players as p on tp.player_id=p._id
where tp.team_id=1 and p._id is not null;

Top scorers for team 1:

select p.name,count(*) from teams_players as tp
left join players as p on tp.player_id=p._id
left join events as e on e.target_id=tp._id
where e.code=12 and tp.team_id=1 and p._id is not null
group by tp._id
order by count(*) desc;

Top assists for team 1:

select p.name,count(*) from teams_players as tp
left join players as p on tp.player_id=p._id
left join events as e on e.target_id=tp._id
where e.code=11 and tp.team_id=1 and p._id is not null
group by tp._id
order by count(*) desc;

Top sums (scores+assists) for team 1:

select p.name,count(*) from teams_players as tp
left join players as p on tp.player_id=p._id
left join events as e on e.target_id=tp._id
where (e.code=11 or e.code=12) and tp.team_id=1 and p._id is not null
group by tp._id
order by count(*) desc;

Match scores:

select t1.name,t2.name,count(distinct(e1._id)),count(distinct(e2._id)) from matches as m
join teams as t1 on t1._id = m.team1_id
join teams as t2 on t2._id = m.team2_id
left join events as e1 on e1.match_id = m._id and e1.code=11
left join events as e2 on e2.match_id=m._id and e2.code=11
join teams_players as tp1 on tp1._id = e1.target_id
join teams_players as tp2 on tp2._id = e2.target_id
where tp1.team_id=t1._id and tp2.team_id=t2._id
group by m._id;

Who started with defense:

select t1.name, t2.name, t3.name from events as e1
left outer join events as e2 on (e1.match_id = e2.match_id and e1.timestamp > e2.timestamp)
left join matches as m on e1.match_id = m._id
left join teams as t1 on m.team1_id = t1._id
left join teams as t2 on m.team2_id = t2._id
left join teams as t3 on e1.target_id = t3._id
where e1.code=6
group by e1.match_id, e1.timestamp
having count(*) < 3;

Successful flips in percent:

select m.c-count(*), m.c, 100*(m.c-count(*))/m.c from
  (select * from events as e1
  left outer join events as e2 on (e1.match_id = e2.match_id and e1.timestamp > e2.timestamp)
  where e1.code=6 and e1.target_id=1
  group by e1.match_id, e1.timestamp
  having count(*) < 3) as def,
(select count(*) as c from matches) as m;

Undirected graph of assists:

select p.name, p2.name, count(*) from events as e
join events as e2 on e.timestamp = e2.timestamp
join teams_players as tp on tp._id = e.target_id
join players as p on tp.player_id = p._id
join teams_players as tp2 on e2.target_id = tp2._id
join players as p2 on p2._id = tp2.player_id
where tp.team_id = 1 and tp2.team_id = 1 and p._id < p2._id
group by p.name, p2.name
order by count(*) desc;

Who in the match 12 scores from the team 1:

select e.code, p.name from events as e
join teams_players as tp on tp._id = e.target_id
join players as p on p._id = tp.player_id
where e.match_id=12 and (e.code=12 or e.code=11) and tp.team_id=1;