get_member_marathon_statistics

gistfile1.txt

select c.handle
      , ar.rating
      , ar.num_competitions as competitions
      , ROUND((select avg(lcr.placed) from long_comp_result lcr where lcr.coder_id = c.coder_id and attended='Y'),2) as avg_rank
      , ROUND((select avg(lcr.num_submissions) from long_comp_result lcr where lcr.coder_id = c.coder_id and attended='Y'),2) as avg_num_submissions
      , (select min(placed) from long_comp_result where placed > 0 and coder_id = c.coder_id) as best_rank
      , (select count(*) from long_comp_result where placed =1 and coder_id = c.coder_id) as wins
      , (select count(*) from long_comp_result where placed between 1 and 5 and coder_id = c.coder_id) as top_five_finishes
      , (select count(*) from long_comp_result where placed between 1 and 10 and coder_id = c.coder_id) as top_ten_finishes
      , cr.rank
      , TRIM(NVL(cr.percentile,'N/A')) as percentile
      , ar.vol
      , ar.lowest_rating as  minimum_rating
      , ar.highest_rating as maximum_rating
      , ccr.rank as country_rank
      , rd.short_name as most_recent_event_name
      , cl.date as most_recent_event_date
      , scr.rank as school_rank
      , (select ll.language_name from language_lu ll where c.language_id = ll.language_id) as default_language
  from  coder c
       , OUTER coder_rank cr
       , OUTER country_coder_rank ccr
       , OUTER school_coder_rank scr
       , OUTER(algo_rating ar, round rd, calendar cl)
 where c.handle_lower = LOWER('@handle@')
   and ar.coder_id = c.coder_id
   and ar.algo_Rating_type_id = 3
   and cr.coder_id = c.coder_id
   and cr.coder_rank_type_id = 2
   and cr.algo_rating_type_id = 3
   and ccr.coder_id = c.coder_id
   and ccr.coder_rank_type_id = 2
   and ccr.algo_rating_type_id = 3
   and rd.round_id = ar.last_rated_round_id
   and cl.calendar_id = rd.calendar_id
   and scr.coder_id = c.coder_id
   and scr.coder_rank_type_id = 2
   and scr.algo_rating_type_id = 3