SQL> ed
Wrote file afiedt.buf

  1  with clients (client_id,name) as (
  2  select 1,'Nadzeya' from dual union all
  3  select 2,'фирст-ласт' from dual union all
  4  select 3,'Stax' from dual )
  5  ,orders (order_id, user_id, summa) as (
  6  select 100,1,7 from dual union all
  7  select 101,2,0.1 from dual union all
  8  select 103,2,0.2 from dual union all
  9  select 103,3,0.3 from dual union all
 10  select 105,1,9 from dual
 11  )
 12  ,oo as (
 13  select
 14    user_id
 15   ,rank() over (order by count(*) desc) rk
 16   ,count(*) cc
 17  from orders
 18  group by user_id)
 19  select
 20    listagg(nvl(name,'*Тайняк*'), ', ') within group(order by name) name_avg_lingv
 21  ,max(o.cc) cc
 22  from oo o,clients c
 23* where c.client_id(+)=o.user_id and o.rk=1
SQL> /

NAME_AVG_LINGV                                   CC
---------------------------------------- ----------
Nadzeya, фирст-ласт                               2