AfR Candidates

afr_candidates.sql

create table compdates as
select
	id as competitionId,
	year * 1e6 + month * 1e2 + day as date
from Competitions;

create table africans as 
select
	Persons.id as wcaId
from
	Persons
    left join Countries on Persons.countryId = Countries.id
where Countries.continentId = '_Africa';

create table african_results as
select 
	PersonId,
    eventId,
    best,
    average,
    date,
    regionalSingleRecord,
    regionalAverageRecord
from 
	Results
	left join compdates on compdates.competitionId = Results.competitionId
where 
	personId in (select wcaId from africans);
    
create table afr_avg_dates as
select
	eventId,
	max(date) as afr_date
from
	african_results
where
	regionalAverageRecord = 'AfR'
group by
	eventId;
    
create table afr_single_dates as
select
	eventId,
	max(date) as afr_date
from
	african_results
where
	regionalSingleRecord = 'AfR'
group by
	eventId;
    
create table dated_african_results as
select
	PersonId,
    african_results.eventId as eventId,
    best,
    average,
    date,
    regionalSingleRecord,
    regionalAverageRecord,
    afr_avg_dates.afr_date as afr_avg_date,
    afr_single_dates.afr_date as afr_single_date
from 
	african_results
    left join afr_avg_dates on african_results.eventId = afr_avg_dates.eventId
    left join afr_single_dates on african_results.eventId = afr_single_dates.eventId;

create table single_targets as
select
	eventId,
    min(best) as single_target
from 
	dated_african_results 
where
	date < afr_single_date - 1e6
    and best > 0
group by
	eventId;

create table average_targets as
select
	eventId,
    min(average) as average_target
from 
	dated_african_results 
where
	date < afr_avg_date - 1e6
    and average > 0
group by
	eventId;
    
create table afr_candidates as
select
	personId,
    dated_african_results.eventId as eventId,
    best,
    average,
    date,
    afr_avg_date,
    afr_single_date,
    single_target,
    average_target,
    if((best <= single_target and best > 0) or regionalSingleRecord = 'AfR', true, false) as single_candidate,
    if((average <= average_target and average > 0) or regionalAverageRecord = 'AfR', true, false) as average_candidate
from
	dated_african_results
    left join single_targets on single_targets.eventId = dated_african_results.eventId
    left join average_targets on average_targets.eventId = dated_african_results.eventId
where
	(best <= single_target and best > 0)
    or (average <= average_target and average > 0);

create table afr_candidates_summary as
select
    Events.name as eventName,
    personId,
    Persons.name as personName,
    min(best),
    min(average),
    case
		when max(single_candidate) and max(average_candidate) then "single & average"
        when max(single_candidate) and not max(average_candidate) then "single only"
        when not max(single_candidate) and max(average_candidate) then "average only"
        else "error"
	end as single_or_average		
from 
	afr_candidates
	left join Persons on personId = Persons.id
    left join Events on eventId = Events.id
where
	Events.rank < 200
group by
	personId,
    Persons.name,
    eventId,
    Events.name
order by
    eventId,
    max(average_candidate) desc,
    max(single_candidate) desc,
    min(average),
    min(best),
    personId;

select
	eventName,
    single_or_average,
	count(*) as num
from
	afr_candidates_summary
group by
	eventName,
    single_or_average

results_2023_12_27.csv