select *
from "users"
inner join (select scorable_id,
ROUND(AVG(charity::int)::float * 100)::int as charity,
COUNT(charity) as charity_count,
row_number()
over (ORDER BY ROUND(AVG(charity::int)::float * 100)::int desc, COUNT(charity)) as charity_rank,
ROUND(AVG(affinity::int)::float * 100)::int as affinity,
COUNT(affinity) as affinity_count,
row_number()
over (ORDER BY ROUND(AVG(affinity::int)::float * 100)::int desc, COUNT(affinity)) as affinity_rank,
ROUND(AVG(secretariat::int)::float * 100)::int as secretariat,
COUNT(secretariat) as secretariat_count,
row_number()
over (ORDER BY ROUND(AVG(secretariat::int)::float * 100)::int desc, COUNT(secretariat)) as secretariat_rank,
ROUND(AVG(altruism::int)::float * 100)::int as altruism,
COUNT(altruism) as altruism_count,
row_number()
over (ORDER BY ROUND(AVG(altruism::int)::float * 100)::int desc, COUNT(altruism)) as altruism_rank,
ROUND(AVG(righteousness::int)::float * 100)::int as righteousness,
COUNT(righteousness) as righteousness_count,
row_number()
over (ORDER BY ROUND(AVG(righteousness::int)::float * 100)::int desc, COUNT(righteousness)) as righteousness_rank,
ROUND(AVG(joviality::int)::float * 100)::int as joviality,
COUNT(joviality) as joviality_count,
row_number()
over (ORDER BY ROUND(AVG(joviality::int)::float * 100)::int desc, COUNT(joviality)) as joviality_rank,
ROUND(AVG(deliberation::int)::float * 100)::int as deliberation,
COUNT(deliberation) as deliberation_count,
row_number()
over (ORDER BY ROUND(AVG(deliberation::int)::float * 100)::int desc, COUNT(deliberation)) as deliberation_rank,
ROUND(AVG(sacrificing::int)::float * 100)::int as sacrificing,
COUNT(sacrificing) as sacrificing_count,
row_number()
over (ORDER BY ROUND(AVG(sacrificing::int)::float * 100)::int desc, COUNT(sacrificing)) as sacrificing_rank,
ROUND(AVG(cooperation::int)::float * 100)::int as cooperation,
COUNT(cooperation) as cooperation_count,
row_number()
over (ORDER BY ROUND(AVG(cooperation::int)::float * 100)::int desc, COUNT(cooperation)) as cooperation_rank,
ROUND(AVG(humility::int)::float * 100)::int as humility,
COUNT(humility) as humility_count,
row_number()
over (ORDER BY ROUND(AVG(humility::int)::float * 100)::int desc, COUNT(humility)) as humility_rank,
ROUND(AVG(chumminess::int)::float * 100)::int as chumminess,
COUNT(chumminess) as chumminess_count,
row_number()
over (ORDER BY ROUND(AVG(chumminess::int)::float * 100)::int desc, COUNT(chumminess)) as chumminess_rank,
ROUND(AVG(generosity::int)::float * 100)::int as generosity,
COUNT(generosity) as generosity_count,
row_number()
over (ORDER BY ROUND(AVG(generosity::int)::float * 100)::int desc, COUNT(generosity)) as generosity_rank,
ROUND(AVG(think_well_of::int)::float * 100)::int as think_well_of,
COUNT(think_well_of) as think_well_of_count,
row_number()
over (ORDER BY ROUND(AVG(think_well_of::int)::float * 100)::int desc, COUNT(think_well_of)) as think_well_of_rank,
ROUND(AVG(wisdom::int)::float * 100)::int as wisdom,
COUNT(wisdom) as wisdom_count,
row_number()
over (ORDER BY ROUND(AVG(wisdom::int)::float * 100)::int desc, COUNT(wisdom)) as wisdom_rank,
ROUND(AVG(patience::int)::float * 100)::int as patience,
COUNT(patience) as patience_count,
row_number()
over (ORDER BY ROUND(AVG(patience::int)::float * 100)::int desc, COUNT(patience)) as patience_rank,
ROUND(AVG(modesty::int)::float * 100)::int as modesty,
COUNT(modesty) as modesty_count,
row_number()
over (ORDER BY ROUND(AVG(modesty::int)::float * 100)::int desc, COUNT(modesty)) as modesty_rank,
ROUND(AVG(mercy::int)::float * 100)::int as mercy,
COUNT(mercy) as mercy_count,
row_number()
over (ORDER BY ROUND(AVG(mercy::int)::float * 100)::int desc, COUNT(mercy)) as mercy_rank,
ROUND(AVG(clemency::int)::float * 100)::int as clemency,
COUNT(clemency) as clemency_count,
row_number()
over (ORDER BY ROUND(AVG(clemency::int)::float * 100)::int desc, COUNT(clemency)) as clemency_rank,
ROUND(AVG(serenity::int)::float * 100)::int as serenity,
COUNT(serenity) as serenity_count,
row_number()
over (ORDER BY ROUND(AVG(serenity::int)::float * 100)::int desc, COUNT(serenity)) as serenity_rank,
ROUND(AVG(white_hearted::int)::float * 100)::int as white_hearted,
COUNT(white_hearted) as white_hearted_count,
row_number()
over (ORDER BY ROUND(AVG(white_hearted::int)::float * 100)::int desc, COUNT(white_hearted)) as white_hearted_rank,
ROUND(AVG(forgiveness::int)::float * 100)::int as forgiveness,
COUNT(forgiveness) as forgiveness_count,
row_number()
over (ORDER BY ROUND(AVG(forgiveness::int)::float * 100)::int desc, COUNT(forgiveness)) as forgiveness_rank,
ROUND(AVG(courageous::int)::float * 100)::int as courageous,
COUNT(courageous) as courageous_count,
row_number()
over (ORDER BY ROUND(AVG(courageous::int)::float * 100)::int desc, COUNT(courageous)) as courageous_rank,
ROUND(AVG(pity::int)::float * 100)::int as pity,
COUNT(pity) as pity_count,
row_number()
over (ORDER BY ROUND(AVG(pity::int)::float * 100)::int desc, COUNT(pity)) as pity_rank,
ROUND(AVG(magnanimity::int)::float * 100)::int as magnanimity,
COUNT(magnanimity) as magnanimity_count,
row_number()
over (ORDER BY ROUND(AVG(magnanimity::int)::float * 100)::int desc, COUNT(magnanimity)) as magnanimity_rank,
ROUND(AVG(longanimity::int)::float * 100)::int as longanimity,
COUNT(longanimity) as longanimity_count,
row_number()
over (ORDER BY ROUND(AVG(longanimity::int)::float * 100)::int desc, COUNT(longanimity)) as longanimity_rank,
ROUND(AVG(honesty::int)::float * 100)::int as honesty,
COUNT(honesty) as honesty_count,
row_number()
over (ORDER BY ROUND(AVG(honesty::int)::float * 100)::int desc, COUNT(honesty)) as honesty_rank,
ROUND(AVG(justice::int)::float * 100)::int as justice,
COUNT(justice) as justice_count,
row_number()
over (ORDER BY ROUND(AVG(justice::int)::float * 100)::int desc, COUNT(justice)) as justice_rank,
ROUND(AVG(glory::int)::float * 100)::int as glory,
COUNT(glory) as glory_count,
row_number()
over (ORDER BY ROUND(AVG(glory::int)::float * 100)::int desc, COUNT(glory)) as glory_rank,
ROUND(AVG(determined::int)::float * 100)::int as determined,
COUNT(determined) as determined_count,
row_number()
over (ORDER BY ROUND(AVG(determined::int)::float * 100)::int desc, COUNT(determined)) as determined_rank,
ROUND(AVG(chastity::int)::float * 100)::int as chastity,
COUNT(chastity) as chastity_count,
row_number()
over (ORDER BY ROUND(AVG(chastity::int)::float * 100)::int desc, COUNT(chastity)) as chastity_rank,
ROUND(AVG(tolerance::int)::float * 100)::int as tolerance,
COUNT(tolerance) as tolerance_count,
row_number()
over (ORDER BY ROUND(AVG(tolerance::int)::float * 100)::int desc, COUNT(tolerance)) as tolerance_rank,
ROUND(AVG(high_vigor::int)::float * 100)::int as high_vigor,
COUNT(high_vigor) as high_vigor_count,
row_number()
over (ORDER BY ROUND(AVG(high_vigor::int)::float * 100)::int desc, COUNT(high_vigor)) as high_vigor_rank,
ROUND(AVG(jealousy::int)::float * 100)::int as jealousy,
COUNT(jealousy) as jealousy_count,
row_number()
over (ORDER BY ROUND(AVG(jealousy::int)::float * 100)::int desc, COUNT(jealousy)) as jealousy_rank,
ROUND(AVG(physiognomy::int)::float * 100)::int as physiognomy,
COUNT(physiognomy) as physiognomy_count,
row_number()
over (ORDER BY ROUND(AVG(physiognomy::int)::float * 100)::int desc, COUNT(physiognomy)) as physiognomy_rank,
ROUND(AVG(oratory::int)::float * 100)::int as oratory,
COUNT(oratory) as oratory_count,
row_number()
over (ORDER BY ROUND(AVG(oratory::int)::float * 100)::int desc, COUNT(oratory)) as oratory_rank,
ROUND(AVG(smartness::int)::float * 100)::int as smartness,
COUNT(smartness) as smartness_count,
row_number()
over (ORDER BY ROUND(AVG(smartness::int)::float * 100)::int desc, COUNT(smartness)) as smartness_rank,
ROUND(AVG(contentedness::int)::float * 100)::int as contentedness,
COUNT(contentedness) as contentedness_count,
row_number()
over (ORDER BY ROUND(AVG(contentedness::int)::float * 100)::int desc, COUNT(contentedness)) as contentedness_rank,
ROUND(AVG(secretive::int)::float * 100)::int as secretive,
COUNT(secretive) as secretive_count,
row_number()
over (ORDER BY ROUND(AVG(secretive::int)::float * 100)::int desc, COUNT(secretive)) as secretive_rank,
ROUND(AVG(anger_management::int)::float * 100)::int as anger_management,
COUNT(anger_management) as anger_management_count,
row_number()
over (ORDER BY ROUND(AVG(anger_management::int)::float * 100)::int desc, COUNT(anger_management)) as anger_management_rank,
ROUND(AVG(likeable::int)::float * 100)::int as likeable,
COUNT(likeable) as likeable_count,
row_number()
over (ORDER BY ROUND(AVG(likeable::int)::float * 100)::int desc, COUNT(likeable)) as likeable_rank,
ROUND(AVG(jokester::int)::float * 100)::int as jokester,
COUNT(jokester) as jokester_count,
row_number()
over (ORDER BY ROUND(AVG(jokester::int)::float * 100)::int desc, COUNT(jokester)) as jokester_rank,
ROUND(AVG(noble_person::int)::float * 100)::int as noble_person,
COUNT(noble_person) as noble_person_count,
row_number()
over (ORDER BY ROUND(AVG(noble_person::int)::float * 100)::int desc, COUNT(noble_person)) as noble_person_rank,
ROUND(AVG(integrity::int)::float * 100)::int as integrity,
COUNT(integrity) as integrity_count,
row_number()
over (ORDER BY ROUND(AVG(integrity::int)::float * 100)::int desc, COUNT(integrity)) as integrity_rank,
ROUND(AVG(energetic::int)::float * 100)::int as energetic,
COUNT(energetic) as energetic_count,
row_number()
over (ORDER BY ROUND(AVG(energetic::int)::float * 100)::int desc, COUNT(energetic)) as energetic_rank,
ROUND(AVG(advice::int)::float * 100)::int as advice,
COUNT(advice) as advice_count,
row_number()
over (ORDER BY ROUND(AVG(advice::int)::float * 100)::int desc, COUNT(advice)) as advice_rank,
ROUND(AVG(helping_others::int)::float * 100)::int as helping_others,
COUNT(helping_others) as helping_others_count,
row_number()
over (ORDER BY ROUND(AVG(helping_others::int)::float * 100)::int desc, COUNT(helping_others)) as helping_others_rank,
ROUND(AVG(devoutness::int)::float * 100)::int as devoutness,
COUNT(devoutness) as devoutness_count,
row_number()
over (ORDER BY ROUND(AVG(devoutness::int)::float * 100)::int desc, COUNT(devoutness)) as devoutness_rank,
ROUND(AVG(piousness::int)::float * 100)::int as piousness,
COUNT(piousness) as piousness_count,
row_number()
over (ORDER BY ROUND(AVG(piousness::int)::float * 100)::int desc, COUNT(piousness)) as piousness_rank,
ROUND(AVG(respecting_covenant::int)::float * 100)::int as respecting_covenant,
COUNT(respecting_covenant) as respecting_covenant_count,
row_number()
over (ORDER BY ROUND(AVG(respecting_covenant::int)::float * 100)::int desc, COUNT(respecting_covenant)) as respecting_covenant_rank,
ROUND(AVG(dignity::int)::float * 100)::int as dignity,
COUNT(dignity) as dignity_count,
row_number()
over (ORDER BY ROUND(AVG(dignity::int)::float * 100)::int desc, COUNT(dignity)) as dignity_rank,
ROUND(AVG(reverence::int)::float * 100)::int as reverence,
COUNT(reverence) as reverence_count,
row_number()
over (ORDER BY ROUND(AVG(reverence::int)::float * 100)::int desc, COUNT(reverence)) as reverence_rank,
ROUND(AVG(flatter::int)::float * 100)::int as flatter,
COUNT(flatter) as flatter_count,
row_number()
over (ORDER BY ROUND(AVG(flatter::int)::float * 100)::int desc, COUNT(flatter)) as flatter_rank,
(CASE WHEN ROUND(AVG(charity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(charity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(affinity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(affinity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(secretariat::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(secretariat::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(altruism::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(altruism::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(righteousness::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(righteousness::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(joviality::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(joviality::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(deliberation::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(deliberation::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(sacrificing::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(sacrificing::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(cooperation::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(cooperation::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(humility::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(humility::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(chumminess::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(chumminess::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(generosity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(generosity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(think_well_of::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(think_well_of::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(wisdom::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(wisdom::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(patience::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(patience::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(modesty::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(modesty::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(mercy::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(mercy::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(clemency::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(clemency::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(serenity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(serenity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(white_hearted::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(white_hearted::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(forgiveness::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(forgiveness::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(courageous::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(courageous::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(pity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(pity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(magnanimity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(magnanimity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(longanimity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(longanimity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(honesty::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(honesty::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(justice::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(justice::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(glory::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(glory::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(determined::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(determined::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(chastity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(chastity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(tolerance::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(tolerance::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(high_vigor::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(high_vigor::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(jealousy::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(jealousy::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(physiognomy::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(physiognomy::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(oratory::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(oratory::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(smartness::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(smartness::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(contentedness::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(contentedness::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(secretive::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(secretive::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(anger_management::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(anger_management::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(likeable::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(likeable::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(jokester::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(jokester::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(noble_person::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(noble_person::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(integrity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(integrity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(energetic::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(energetic::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(advice::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(advice::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(helping_others::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(helping_others::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(devoutness::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(devoutness::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(piousness::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(piousness::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(respecting_covenant::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(respecting_covenant::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(dignity::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(dignity::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(reverence::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(reverence::int)::float * 100)::int END +
CASE WHEN ROUND(AVG(flatter::int)::float * 100)::int IS NULL THEN 0 ELSE ROUND(AVG(flatter::int)::float * 100)::int END) / (CASE WHEN ROUND(AVG(charity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(affinity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(secretariat::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(altruism::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(righteousness::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(joviality::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(deliberation::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(sacrificing::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(cooperation::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(humility::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(chumminess::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(generosity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(think_well_of::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(wisdom::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(patience::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(modesty::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(mercy::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(clemency::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(serenity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(white_hearted::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(forgiveness::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(courageous::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(pity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(magnanimity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(longanimity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(honesty::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(justice::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(glory::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(determined::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(chastity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(tolerance::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(high_vigor::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(jealousy::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(physiognomy::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(oratory::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(smartness::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(contentedness::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(secretive::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(anger_management::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(likeable::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(jokester::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(noble_person::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(integrity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(energetic::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(advice::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(helping_others::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(devoutness::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(piousness::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(respecting_covenant::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(dignity::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(reverence::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END +
CASE WHEN ROUND(AVG(flatter::int)::float * 100)::int IS NULL THEN 0 ELSE 1 END) as total,
COUNT(id) as total_count,
row_number() over (ORDER BY (ROUND(AVG(charity::int)::float * 100)::int +
ROUND(AVG(affinity::int)::float * 100)::int +
ROUND(AVG(secretariat::int)::float * 100)::int +
ROUND(AVG(altruism::int)::float * 100)::int +
ROUND(AVG(righteousness::int)::float * 100)::int +
ROUND(AVG(joviality::int)::float * 100)::int +
ROUND(AVG(deliberation::int)::float * 100)::int +
ROUND(AVG(sacrificing::int)::float * 100)::int +
ROUND(AVG(cooperation::int)::float * 100)::int +
ROUND(AVG(humility::int)::float * 100)::int +
ROUND(AVG(chumminess::int)::float * 100)::int +
ROUND(AVG(generosity::int)::float * 100)::int +
ROUND(AVG(think_well_of::int)::float * 100)::int +
ROUND(AVG(wisdom::int)::float * 100)::int +
ROUND(AVG(patience::int)::float * 100)::int +
ROUND(AVG(modesty::int)::float * 100)::int +
ROUND(AVG(mercy::int)::float * 100)::int +
ROUND(AVG(clemency::int)::float * 100)::int +
ROUND(AVG(serenity::int)::float * 100)::int +
ROUND(AVG(white_hearted::int)::float * 100)::int +
ROUND(AVG(forgiveness::int)::float * 100)::int +
ROUND(AVG(courageous::int)::float * 100)::int +
ROUND(AVG(pity::int)::float * 100)::int +
ROUND(AVG(magnanimity::int)::float * 100)::int +
ROUND(AVG(longanimity::int)::float * 100)::int +
ROUND(AVG(honesty::int)::float * 100)::int +
ROUND(AVG(justice::int)::float * 100)::int +
ROUND(AVG(glory::int)::float * 100)::int +
ROUND(AVG(determined::int)::float * 100)::int +
ROUND(AVG(chastity::int)::float * 100)::int +
ROUND(AVG(tolerance::int)::float * 100)::int +
ROUND(AVG(high_vigor::int)::float * 100)::int +
ROUND(AVG(jealousy::int)::float * 100)::int +
ROUND(AVG(physiognomy::int)::float * 100)::int +
ROUND(AVG(oratory::int)::float * 100)::int +
ROUND(AVG(smartness::int)::float * 100)::int +
ROUND(AVG(contentedness::int)::float * 100)::int +
ROUND(AVG(secretive::int)::float * 100)::int +
ROUND(AVG(anger_management::int)::float * 100)::int +
ROUND(AVG(likeable::int)::float * 100)::int +
ROUND(AVG(jokester::int)::float * 100)::int +
ROUND(AVG(noble_person::int)::float * 100)::int +
ROUND(AVG(integrity::int)::float * 100)::int +
ROUND(AVG(energetic::int)::float * 100)::int +
ROUND(AVG(advice::int)::float * 100)::int +
ROUND(AVG(helping_others::int)::float * 100)::int +
ROUND(AVG(devoutness::int)::float * 100)::int +
ROUND(AVG(piousness::int)::float * 100)::int +
ROUND(AVG(respecting_covenant::int)::float * 100)::int +
ROUND(AVG(dignity::int)::float * 100)::int +
ROUND(AVG(reverence::int)::float * 100)::int +
ROUND(AVG(flatter::int)::float * 100)::int) / ((CASE
WHEN ROUND(AVG(charity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(affinity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(secretariat::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(altruism::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(righteousness::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(joviality::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(deliberation::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(sacrificing::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(cooperation::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(humility::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(chumminess::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(generosity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(think_well_of::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(wisdom::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(patience::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(modesty::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(mercy::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(clemency::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(serenity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(white_hearted::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(forgiveness::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(courageous::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(pity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(magnanimity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(longanimity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(honesty::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(justice::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(glory::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(determined::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(chastity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(tolerance::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(high_vigor::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(jealousy::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(physiognomy::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(oratory::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(smartness::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(contentedness::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(secretive::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(anger_management::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(likeable::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(jokester::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(noble_person::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(integrity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(energetic::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(advice::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(helping_others::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(devoutness::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(piousness::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(respecting_covenant::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(dignity::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(reverence::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END) +
(CASE
WHEN ROUND(AVG(flatter::int)::float * 100)::int IS NULL
THEN 0
ELSE 1 END)) desc) as total_rank
from "scores"
where exists(select * from "users" where scores.user_id = users.id)
and "scorable_type" = 'users'
group by "scorable_id") as "scores" on "users"."id" = "scores"."scorable_id"
order by "total" desc
limit 12