How to use aggregate window function on varchar() datavalues?

Question

I was writing a query on a dataset and made this table as a CTE (t3) (DDL and DML statements for this table t3 are given below)

games	regions	gold	silver	bronze	total
1896 Summer	Greece	2	4	4	10
1896 Summer	UK	3	1	1	5
1896 Summer	Switzerland	0	0	0	0
1896 Summer	USA	8	4	1	13
1896 Summer	Germany	7	1	0	8
1896 Summer	France	1	2	1	4
1896 Summer	Hungary	0	1	0	1
1896 Summer	Australia	2	0	1	3

Notes: DDL and DML statements for t3:

DDL statement: create table t3 (games varchar(20), regions varchar(20), gold int, silver int, bronze int, total int);

and DML statements are:

insert into t3 values ('1896 Summer', 'Greece', 2, 4, 4, 10);
insert into t3 values ('1896 Summer', 'UK', 3, 1, 1, 5);
insert into t3 values ('1896 Summer', 'Switzerland', 0, 0, 0, 0);
insert into t3 values ('1896 Summer', 'USA', 8, 4, 1, 13);
insert into t3 values ('1896 Summer', 'Germany', 7, 1, 0, 8);
insert into t3 values ('1896 Summer', 'France', 1, 2, 1, 4);
insert into t3 values ('1896 Summer', 'Hungary', 0, 1, 0, 1);
insert into t3 values ('1896 Summer', 'Australia', 2, 0, 1, 3);

For this, I have written a query:

select distinct games, 
       concat(max(regions) over (partition by games order by gold desc), "-", max(gold) over(partition by games)) as gold_count,
       concat(max(regions) over (partition by games order by silver desc, regions), "-", max(silver) over(partition by games)) as silver_count,
       concat(max(regions) over (partition by games order by bronze desc, regions), "-", max(bronze) over(partition by games)) as bronze_count,
       concat(max(regions) over (partition by games order by total desc), "-", max(total) over(partition by games)) as total
from t3;

From this query I am getting this result

games	gold_count	silver_count	bronze_count	total
1896 Summer	USA-8	Greece-4	Greece-4	USA-13
1896 Summer	USA-8	USA-4	Greece-4	USA-13
1896 Summer	USA-8	USA-4	UK-4	USA-13
1896 Summer	USA-8	USA-4	USA-4	USA-13

How can I get the results like this:

games	gold_count	silver_count	bronze_count	total
1896 Summer	USA-8	Greece-4	Greece-4	USA-13
1896 Summer	USA-8	USA-4	Greece-4	USA-13

or like this:

games	gold_count	silver_count	bronze_count	total
1896 Summer	USA-8	Greece-4 & USA-4	Greece-4	USA-13

Answer 1

For each games, you want to display the top regions for each type of medal (gold, silver, bronze) and overall, with respect to ties.

One option ranks regions for each type of medal with rank(), then pivots with conditional aggregation:

select games, 
    group_concat( case when rn_gold   = 1 then concat(regions, '-', gold  ) end separator ' & ') as gold_count,
    group_concat( case when rn_silver = 1 then concat(regions, '-', silver) end separator ' & ') as silver_count,
    group_concat( case when rn_bronze = 1 then concat(regions, '-', bronze) end separator ' & ') as bronze_count,
    group_concat( case when rn_total  = 1 then concat(regions, '-', total ) end separator ' & ') as total_count
from (
    select t.*,
        rank() over(partition by games order by gold   desc) rn_gold,
        rank() over(partition by games order by silver desc) rn_silver,
        rank() over(partition by games order by bronze desc) rn_bronze,
        rank() over(partition by games order by total  desc) rn_total
    from t3 t
) t
group by games

Which, for your sample data, yields:

games	gold_count	silver_count	bronze_count	total_count
1896 Summer	USA-8	USA-4 & Greece-4	Greece-4	USA-13

fiddle