sum(raw_amount/power(10, decimals)) as outflow

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts ON address = contract_address

WHERE symbol LIKE 'sUSD'

AND to_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')

AND block_timestamp > CURRENT_DATE - 14

GROUP BY 1

)

SELECT from_address as users,

loss

FROM (SELECT from_address,

bet_count,

case WHEN outflow is NULL THEN -1 * inflow ELSE outflow - inflow END as loss

FROM (SELECT to_address,

count(DISTINCT tx_hash) as bet_count,

sum(raw_amount/power(10, decimals)) as inflow

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts on address = contract_address

WHERE symbol LIKE 'sUSD'

AND from_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')

AND block_timestamp > CURRENT_DATE - 14

GROUP BY 1

)

LEFT OUTER join outflow on to_address = from_address

HAVING NOT loss is NULL

AND NOT from_address IS NULL

)

group by 1, 2

order by 2 desc

limit 5