Statistics

Here's some SQL requests to fetch various game statistics from the database.

• List first bloods for each vuln number

select * from get_first_bloods();

• List teams rated by number of stolen flags

select t.id, t.name, count(distinct sf) as flags_stolen
from teams t 
join stolenflags sf 
    on sf.attacker_id = t.id 
group by t.id 
    having count(sf) > 0 
order by count(distinct sf) desc;

• Get count of stolen flags

select count(*) from stolenflags;

• Get count of stolen flags per service

select t.id, t.name, count(distinct sf) 
from stolenflags sf 
join flags f on 
    f.id = sf.flag_id
join tasks t on 
    t.id = f.task_id
group by t.id
order by count(distinct sf) desc;

• Get the team by flag

select t.id, t.name 
from flags f 
join teams t on 
    t.id = f.team_id 
where f.flag='<flag>';