from_address,

sum(raw_amount/1e18) as sUSD_amount,

count(distinct(origin_from_address)) as count_bettors

from optimism.core.fact_token_transfers

where --tx_hash = '0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865'

to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and contract_address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'

and block_timestamp >= current_date - 14

group by 1),

base2 as (select

to_address,

sum(raw_amount/1e18) as sUSD_amount,

count(distinct(origin_from_address)) as count_bettors

from optimism.core.fact_token_transfers

where --tx_hash = '0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865'

from_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and contract_address = '0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9'

and block_timestamp >= current_date - 14

group by 1),

base3 as (select ifnull(from_address, to_address) as bettors,

sum(ifnull(a.sUSD_amount,0)) - sum(ifnull(b.sUSD_amount,0)) as gain_loss,

row_number() over (order by gain_loss desc) as ordering,

case

when gain_loss < 0 then 'Loss'

when gain_loss = 0 then 'Equal'

when gain_loss > 0 then 'Gain'

end as differences,

1 as numbering

from base a

full outer join base2 b

on a.from_address = b.to_address

group by 1

order by 2 desc