postgresql - Writing SQL query to find ranking -

i'm trying determine given person how many people have better score do, , group different teams belong to. so, in tables below, i'm grabbing list of team_id team_person table person_id matches person care about. me of teams belong to.

then need know each person_id in team belong can find out maximum score performances table.

once have that, want determine, each team_id, how many people on team have better score do, better defined having larger value.

i've gotten way beyond abilities sql @ point. have far, seems me maximum score people care about, (basically final "by team" requirement) this:

    select person_id, max(score) m     performances     category_id = 7 , person_id in (         -- find people on teams belong         select distinct person_id         team_person          team_id in (             -- find teams belong             select distinct team_id              team_person             person_id = 2         )     )     group person_id     order 2 desc 

my 2 relevant tables defined so, , i'm using psql 9.1.15

                                     table "public.team_person"    column   |           type           |                          modifiers                       ------------+--------------------------+-------------------------------------------------------------  ident      | integer                  | not null default nextval('team_person_ident_seq'::regclass)  team_id    | integer                  | not null  person_id  | integer                  | not null  *chop extraneous columns* indexes:     "team_person_pkey" primary key, btree (ident)     "teampersonunique" unique constraint, btree (team_id, person_id) foreign-key constraints:     "team_person_person_id_fkey" foreign key (person_id) references person(ident) on delete cascade     "team_person_team_id_fkey" foreign key (team_id) references team(ident) on delete cascade referenced by:     table "roster" constraint "roster_team_person_id_fkey" foreign key (team_person_id) references team_person(ident) on delete set null triggers:     update_team_person_modified before insert or update on team_person each row execute procedure update_modified_column()                                          table "public.performances"    column    |           type           |                          modifiers                        -------------+--------------------------+--------------------------------------------------------------  ident       | bigint                   | not null default nextval('performances_ident_seq'::regclass)  category_id | integer                  | not null  person_id   | integer                  | not null  score       | real                     | not null  *chop extraneous columns* indexes:     "performances_pkey" primary key, btree (ident) foreign-key constraints:     "performances_category_id_fkey" foreign key (category_id) references performance_categories(ident) on delete cascade     "performances_person_id_fkey" foreign key (person_id) references person(ident) on delete cascade 

first, state problem, without assumptions how solution. you've done well:

determine given person how many people have better score do, , group different teams belong to.

but i'd rephrase bit:

for each team given person member of, how many people in team have better score subject person?

i don't know you, seems simpler now. take team table, left outer join team_person , filter teams we're member of, left outer join performances find games played team, left outer join team_person again other people who're members of each team, left outer join performances, filter out teams subject person isn't member of, group , aggregate.

it's underspecified corner cases (like team you're member, or team didn't play game), eh, whatever.

---

problems:

there's no team table. since don't care in team table, can omit join , use team_person join root.

your team_person table defective, way. should have unique constraint on (team_id, person_id). or, better, should primary key. doesn't matter query because duplicate team memberships won't change result, it's bad data modelling. can't member of team more once.

performances should have column identifying particular game or whatever. since haven't shown one, i'm going assume mean you're looking people who, in game, performed better subject person @ least once, in game or game. if want find people did better in particular game need suitable key on performances.

fatal problem: performances missing column linking performance team. makes impossible solve problem because can't performances given person on given team. i'm going assume there in fact team_id on performances , left out.

---

so, allowing above issues, i'd first acquire data big join, group , aggregate it. join give us, each team played in, each of our performances, each other player, each of other performances, 1 row relevant information. can compare performances , aggregate.

the below totally untested, since didn't provide sample data , chopped important parts out of schema (or schema defective), i'd try like:

select   my_performances.team_id,    -- find how many distinct people scored better @ least once,   -- no matter how many times or in game.   count(distinct other_team_person.person_id)  -- start join our team memberships , how scored in each. -- if didn't play games team don't produce result row -- it, inner join. team_person my_team_person inner join performances my_performances on    (my_performances.person_id = my_team_person.person_id     , my_performances.team_id = my_team_person.team_id)  -- other members of teams we're member of, skipping -- ourselves. `inner join` fine here because know -- team ourselves member isn't interesting -- , might skip it. inner join team_person others_team_person on (   my_team_person.team_id = other_team_person.team_id   , my_team_person.person_id <> other_team_person.person_id)  -- how each of people performed in each team they're in -- (because of previous filter, considers teams we're in too). -- inner join because if never played can't beat us. inner join performances other_performances on (   other_team_person.person_id = other_performances.person_id   , other_team_person.team_id = other_performances.team_id)  -- make sure `my_team_person` teams we're member of my_team_person.person_id = $1  -- discard rows other person didn't better   , my_performances.score < other_performances.score  -- emit 1 row per team we're member of group my_performances.team_id; 

if want show teams never played , teams you're player, you'll need change inner joins left outer joins.

if want compare find people beat within given game, you're going need column on performances, term in join on other_performances restrict matching in same game my_performances.