with TL as
(
  select 0 as POS, '' as NAME, 0 as FEE, 0 as BEG from dual where 0=1
  union all select 1, 'Начальный', 800, null from dual
  union all select 2, 'Популярный', 1000, null from dual
  union all select 3, 'Премиальный', 1200, 300 from dual
  union all select 4, 'Премиум+', 1400, 1000 from dual
)
, TP as
(
  select 1 as PERIOD, null as DISCOUNT from dual
  union all select 3, 5 from dual
  union all select 6, 10 from dual
  union all select 12, 15 from dual
)
, RR as
(
  select 100 as RATE from dual
  union all select 200 from dual
  union all select 300 from dual
  union all select 1000 from dual
)
, RS as
(
  select 100 as STEP, null as RATE, null as LINE from dual
  union all select 200, 100, 'Начальный' from dual
)
, SS as
(
  select null as NAME, null as DISCOUNT from dual
  union all select 'Клуб', 10 from dual
)
select SS.NAME as STATUS
, TL.NAME as LINE
, TP.PERIOD
, RR.RATE
, dense_rank() over(partition by TL.NAME, TP.PERIOD order by RR.RATE) as RANK
, nvl(RSO.STEP, RS.STEP) as STEP
, case
    when RR.RATE = 100 then TL.FEE * (1-nvl(TP.DISCOUNT,0)/100) * TP.PERIOD * (1-nvl(SS.DISCOUNT,0)/100)
  end as FEE
from TL
join TP on (TP.PERIOD > 0)
join RR on (RR.RATE = 100 or RR.RATE >= nvl(TL.BEG,0))
join RS on (RS.RATE is null and RS.LINE is null)
left join RS RSO on (RSO.RATE = RR.RATE and RSO.LINE = TL.NAME)
join SS on (1=1)
order by SS.NAME nulls first, TL.POS, TP.PERIOD, RR.RATE