Conditional aggregation, KEEP and ANY_VALUE

Three aggregate tricks that save whole queries:

• Conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) counts only the rows that meet a condition, so one pass over the data gives you several counts side by side.
• KEEP (DENSE_RANK FIRST/LAST ORDER BY ...): a value from the first or last row of the group ("the date of the team's biggest win").
• ANY_VALUE(x): any one value of x from the group, when they're all the same anyway and you just need one (saves adding it to GROUP BY).

The test case

Each team's record over both seasons: matches, won, lost, tied, and the date of their biggest win by runs (blank if they never won by runs). Most wins first.

Expected result

Way 1: conditional SUMs and KEEP

select t.team_code,
       count(*)                                                        as matches,
       sum(case when m.winner_id = t.team_id  then 1 else 0 end)       as won,
       sum(case when m.winner_id <> t.team_id then 1 else 0 end)       as lost,
       sum(case when m.winner_id is null      then 1 else 0 end)       as tied,
       to_char(max(case when m.winner_id = t.team_id and m.win_type = 'RUNS' then m.match_date end)
                 keep (dense_rank first order by
                       case when m.winner_id = t.team_id and m.win_type = 'RUNS' then m.win_margin end
                       desc nulls last), 'YYYY-MM-DD')                 as biggest_win
from   cr_teams t
join   cr_matches m on t.team_id in (m.home_team_id, m.away_team_id)
group  by t.team_code
order  by won desc, t.team_code;

• The three SUM(CASE ...) columns read the matches once and sort each into won, lost or tied. A tie has winner_id null, so winner_id <> team_id is unknown and the tie doesn't count as a loss.
• The KEEP column takes the rows with the largest runs margin among the team's wins and returns their latest date (MAX, in case two wins share the biggest margin). For teams with no win by runs every candidate is null, so the result is blank.

Way 2: COUNT with CASE, and a join for the biggest win

with team_matches as (
  select t.team_code, t.team_id, m.*
  from   cr_teams t
  join   cr_matches m on t.team_id in (m.home_team_id, m.away_team_id)
),
biggest as (
  select team_code, to_char(match_date, 'YYYY-MM-DD') as biggest_win
  from   (select tm.team_code, tm.match_date,
                 row_number() over (partition by tm.team_code order by tm.win_margin desc, tm.match_date desc) as rn
          from   team_matches tm
          where  tm.winner_id = tm.team_id and tm.win_type = 'RUNS')
  where  rn = 1
)
select r.team_code, r.matches, r.won, r.lost, r.tied, b.biggest_win
from   (select team_code,
               count(*)                                              as matches,
               count(case when winner_id = team_id  then 1 end)      as won,
               count(case when winner_id <> team_id then 1 end)      as lost,
               count(case when winner_id is null    then 1 end)      as tied
        from   team_matches
        group  by team_code) r
left   join biggest b on b.team_code = r.team_code
order  by r.won desc, r.team_code;

COUNT(CASE WHEN ... THEN 1 END) is the same trick as SUM(CASE ... THEN 1 ELSE 0 END): COUNT skips the nulls the CASE returns when the condition fails. The biggest win comes from a separate top-1-per-group step (page 5.7), with match_date desc as the tie-breaker so it agrees with Way 1's MAX. Longer, but each piece is simple.

ANY_VALUE in one line

When a column is the same for the whole group, you can avoid grouping by it:

select m.season, any_value(m.stage) as stage, count(*) as matches
from   cr_matches m
where  m.stage = 'FINAL'
group  by m.season;

Without ANY_VALUE you'd write MAX(m.stage) (works, but suggests there's a choice) or add it to GROUP BY (works, but clutters it). ANY_VALUE says what you mean: "they're all the same".

19c and 26ai

All of this works on both. ANY_VALUE is listed as new in 21c, but it's also in 19c release updates (it ran on our 19.33).

Gotchas

• SUM(CASE ... ELSE 0) vs COUNT(CASE ...): the same answer for counts; SUM is also the one to use for adding up values (sum(case when ... then runs end)).
• KEEP needs care with nulls: order the "best" key so that nulls sort last, as above.
• ANY_VALUE really is any value. If the values differ, you get one of them, unpredictably.