Why does pre-sorted index not help me in window function? I am trying to speed up this query. Currently it takes ~6s.
It queries a TEMP TABLE providers_tam_with_offices_tmp.
select *,
  %(max_providers_to_target_per_territory)s as max_providers,
  case
    when call_point_rank = 1 then
        rank()
        over (partition by team_id, territory_name, call_point_rank order by office_tam desc, account_id, address_key, tam desc, id)
    else NULL
    end as cum_provider_count
from
(
  select *,
    -- We order by `account_id, address_key`, and then by `id` as a tie breaker for
    -- deterministic results.
      rank()
      over (partition by team_id, territory_name, id order by office_tam desc, account_id, address_key,
        tam desc, id ) as call_point_rank
  from
    providers_tam_with_offices_tmp
) a

As an experiment, I tried adding this index to providers_tam_with_offices_tmp.
create index providers_tam_with_offices_tmp_idx on providers_tam_with_offices_tmp
(team_id, territory_name, id, office_tam desc, account_id, address_key,
        tam desc, id );

But the query plan shows that this doesn't eliminate the need to sort the table first before calculating the first window function:

While the utility of this might be questionable, I am interested in the general case of why the optimiser wouldn't spot that there's already an index with exactly the sorting required.
Or have I misunderstood how window functions required the data to be organised?
You haven't shown that it doesn't spot the index, only that it doesn't use it.  If you set enable_sort = off; then what happens?
If the table is not well vacuumed and the correlation of team_id with the physical row ordering is low, then reading the index in order will mean hopping all over the table to check that each row from the index is actually visible, leading to the expectation of a lot of costly random IO.  This will be slower (or is expected to be slower) than a nice sequential scan followed by a sequential-IO-optimized sort.