-- Figure out the top ranked submissions

select ranks.abstract_id, ranks.ranking, abstract_title from abstracts, (select sum(ranking_value) as ranking, abstract_id from rankings group by abstract_id) as ranks where abstracts.abstract_id = ranks.abstract_id  order by ranks.ranking desc limit 120;

-- Top ratings and interest
select ranks.abstract_id, ranks.ranking, ranks.interest, abstract_title from abstracts, (select sum(ranking_value) as ranking, sum(case when ranking_value > 0 then 1 else 0 end) as interest, abstract_id from rankings group by abstract_id) as ranks where abstracts.abstract_id = ranks.abstract_id  order by ranks.interest desc,ranks.ranking desc limit 120;

-- number of rankers

select count(*) from (select ranker_id, count(*) from rankings group by ranker_id) as foo where count > 15;

-- simple summary with ranking percents

select abstracts.abstract_id, abstract_title, ranking, interest, round(100 * interest::float4/count) || '%' as interest_pct, presenter, presenter_organization, presenter_email from (SELECT DISTINCT ON (abstract_id) abstract_id, abstract_title, abstract_status, abstract_description, abstract_content, to_char(abstract_timestamp,'Dy, DD Mon YYYY HH24:MI:SS PST') as abstract_timestamp_string, abstract_timestamp, presenter_name as presenter, presenter_organization, presenter_email FROM abstracts LEFT JOIN presenters USING (abstract_id)) as abstracts, (select sum(ranking_value) as ranking, sum(case when ranking_value > 0 then 1 else 0 end) as interest, abstract_id from rankings group by abstract_id) as ranks, (select count(*) as count from (select ranker_id, count(*) as c from rankings group by ranker_id) as counts where c > 15) as rankers where abstracts.abstract_id = ranks.abstract_id  order by ranking desc;

-- All HTML info with ranking percents

select presenter, presenter_organization, presenter_email, abstracts.abstract_id, abstract_title, abstract_description, abstract_content, ranking, interest, round(100 * interest::float4/count) || '%' as interest_pct from (SELECT DISTINCT ON (abstract_id) abstract_id, abstract_title, abstract_status, abstract_description, abstract_content, to_char(abstract_timestamp,'Dy, DD Mon YYYY HH24:MI:SS PST') as abstract_timestamp_string, abstract_timestamp, presenter_name as presenter, presenter_organization, presenter_email FROM abstracts LEFT JOIN presenters USING (abstract_id)) as abstracts, (select sum(ranking_value) as ranking, sum(case when ranking_value > 0 then 1 else 0 end) as interest, abstract_id from rankings group by abstract_id) as ranks, (select count(*) as count from (select ranker_id, count(*) as c from rankings group by ranker_id) as counts where c > 15) as rankers where abstracts.abstract_id = ranks.abstract_id  order by ranking desc;

-- All rankers with < 15 rankings

select ranker_email,ranker_id from rankers where ranker_id in (select ranker_id from (select count(*),ranker_id from rankings group by ranker_id) as f1 where count < 15);

-- All abstracts ranked by people with just one ranking

select abstracts.abstract_id, abstract_title from abstracts left join rankings using (abstract_id) where rankings.ranker_id in ((select ranker_id from (select count(*), ranker_id from rankings group by ranker_id) as foo where count = 1)) order by abstract_title;

-- All abstracts ordered in schedule / table order

select abstract_id, abstract_title, timeslot_day_name, timeslot_day_id, timeslot_room_name, timeslot_room_id, timeslot_session_name, timeslot_session_id, timeslot_order_id from abstracts join timeslots on (abstract_timeslot_id = timeslot_id) where abstract_status = 5 order by timeslot_day_id, timeslot_session_id, timeslot_order_id, timeslot_room_id;

-- All accepted presentation abstracts and presenters with emails

select abstract_id, abstract_title, presenter_name, presenter_email, abstract_key from abstracts join presenters using (abstract_id) where abstract_status = 5 and abstract_type = 1;

-- Papers with large numbers of rankers from the same domain
select max(a.abstract_title),k.domain,count(*) from abstracts a join rankings r using (abstract_id) join (select split_part(ranker_email,'@',2) as domain, ranker_email, ranker_id from rankers) k using (ranker_id) where k.domain not in ('gmail.com','hotmail.com','yahoo.com') group by abstract_title, domain order by count desc; 

