美しいミズキ

1
select
2
    master_match_schedule_id,
3
    organization_id,
4
    win_count
5
from (
6
    select
7
        tm.master_match_schedule_id,
8
        p.organization_id,
9
        count(*) as win_count,
10
        row_number() over (
11
          partition by tm.master_match_schedule_id
12
          order by count(*) desc
13
        ) as rn
14
    from team_event_matches tm
15
    join match_schedule ms on ms.match_schedule_id = tm.match_schedule_id
16
    join matches m on ms.match_id = m.match_id
17
    join teams t on t.team_id = m.winner_id
18
    join participants p on p.participant_id = t.participant_one_id
19
    where ms.division_id = 'div-03fd4f8e805edecc356a4b6c06cbb285'
20
    and ms.tournament_id = '257de693732eb1bdb4ab69aaada3a6e2'
21
    and tm.team_event_code = 'TEAM-CC24129B'
22
    group by
23
    tm.master_match_schedule_id,
24
    p.organization_id
25
) t
26
where rn = 1
27
order by master_match_schedule_id;