---

with uid_keys as (
    select distinct
        lower(nullif(btrim(rd.uidpensionscheme), '')) as uid_key
    from back_office.report_conformity_pu_bu_detail rd
    where rd.report_conformity_pu_bu_id = 97
      and rd.account in ('38902', '39002')
      and nullif(btrim(rd.uidpensionscheme), '') is not null
),
matches as (
    select
        k.uid_key,
        count(ps.id) as match_count,
        string_agg(distinct ps.name, ' | ') as scheme_name,
        string_agg(distinct ps.brief, ' | ') as scheme_brief
    from uid_keys k
    left join prod_line.pens_scheme ps
        on lower(nullif(btrim(ps.uid_1c), '')) = k.uid_key
    group by k.uid_key
)
select
    count(*) over () as total_uid,
    count(*) filter (
        where match_count = 1
    ) over () as matched_uid,
    uid_key,
    match_count,
    scheme_name,
    scheme_brief
from matches
order by match_count desc, uid_key
limit 5;