Historical WCA Ranking Query

yiheng_130_pr_2023_04_27.sql

drop table if exists y_comp_dates;
create table y_comp_dates as
select
  id as competitionId,
  year * 10000 + endMonth * 100 + endDay as endDate
from
  Competitions;


drop table if exists y_results;
create table y_results as
select
  competitionId,
  eventId,
  roundTypeId,
  personId,
  personName,
  pos,
  best,
  average
from
  Results
where
  eventId='222'
  and average <= 150
  and average > 0;


drop table if exists y_result_dates;
create table y_result_dates as
select
  y_results.competitionId as competitionId,
  eventId,
  roundTypeId,
  personId,
  personName,
  pos,
  best,
  average,
  endDate
from
  y_results
left join y_comp_dates on y_results.competitionId = y_comp_dates.competitionId
where
  endDate <= 20230427;

drop table if exists y_average_rankings;
create table y_average_rankings as
select
  rank() over (order by min(average)) as num,
  personId,
  personName,
  min(average) as average
from
  y_result_dates
group by
  personId,
  personName;


select * 
from y_average_rankings 
order by num desc;