alter session set workarea_size_policy = manual;
alter session set sort_area_size = 2097151000;

column chk format 99999999999999999999

drop table trans;

create table trans as
select t2.*, t1.*
from (select level transaction_id, abs(trunc(1e3 * sin(level * level))) quantity, abs(trunc(1e3 * sin(level) * (1 + level / 2e5))) price
      from dual
      connect by level <= 2e5) t1
    ,(select level product_id
      from dual
      connect by level <= 5) t2
order by 1, 2;

set timing on

prompt window functions

select sum(result) chk
from
(
select
  t.*,
  nth_value(price, 1) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 19, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 2) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 18, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 3) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 17, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 4) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 16, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 5) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 15, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 6) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 14, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 7) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 13, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 8) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 12, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 9) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 11, 0) over (partition by product_id order by transaction_id) +  
  nth_value(price, 10) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 10, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 11) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 9, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 12) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 8, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 13) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 7, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 14) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 6, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 15) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 5, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 16) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 4, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 17) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 3, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 18) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 2, 0) over (partition by product_id order by transaction_id) +
  nth_value(price, 19) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 1, 0) over (partition by product_id order by transaction_id) + 
  nth_value(price, 20) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following) * lag(quantity, 0, 0) over (partition by product_id order by transaction_id) result
from trans t
) tt;

prompt single join + agg functions (KEEP)

with t as(select trans.*, row_number() over(partition by product_id order by transaction_id) rn from trans)
select sum(result) chk
from
(
select t1.product_id
      ,max(t1.transaction_id) transaction_id
      ,sum(t1.quantity * t2.price) as result
      ,max(t1.quantity) keep (dense_rank last order by t1.rn) quantity
      ,max(t1.price) keep (dense_rank last order by t1.rn) price
from t t1 join t t2 on t1.product_id = t2.product_id
where t2.rn <= 20
group by t1.product_id, t1.rn - t2.rn
having max(t2.rn) = 20
);

prompt single join + agg functions (NO KEEP)

with t as(select trans.*, row_number() over(partition by product_id order by transaction_id) rn from trans)
select sum(result) chk
from
(
select t1.product_id
      ,max(t1.transaction_id) transaction_id
      ,sum(t1.quantity * t2.price) as result
      ,max(case when t2.rn=20 then t1.quantity end) quantity
      ,max(case when t2.rn=20 then t1.price end) price
from t t1 join t t2 on t1.product_id = t2.product_id
where t2.rn <= 20
group by t1.product_id, t1.rn - t2.rn
having max(t2.rn) = 20
);

prompt single join + window functions (NO LAG)

with t as(select trans.*, row_number() over(partition by product_id order by transaction_id) rn from trans)
select sum(result) chk
from (select t1.*
            ,sum(t1.quantity * t2.price) over (partition by t1.product_id, t1.rn - t2.rn) result
            ,max(t2.rn) over (partition by t1.product_id, t1.rn - t2.rn) mrn
            ,row_number() over(partition by t1.product_id, t1.rn - t2.rn order by t1.transaction_id desc) i
      from t t1 join t t2 on t1.product_id = t2.product_id
      where t2.rn <= 20) tt
where i = 1 and mrn = 20;

prompt two joins

with ss as
(
select row_number() over (partition by product_id order by transaction_id) i, s.*
from trans s
)
select sum(result) chk
from
(
select s1.product_id, s1.transaction_id, s1.quantity, s1.price, sum(s2.quantity * s3.price) result
from ss s1
join ss s3 on s1.product_id = s3.product_id and s3.i <= 20
join ss s2 on s1.product_id = s2.product_id and s1.i + s3.i - 20 = s2.i
group by s1.product_id, s1.transaction_id, s1.quantity, s1.price
) tt;

set timing off