SELECT

to_address,

count(DISTINCT tx_hash) as bets,

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

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts

ON address = contract_address

--WHERE tx_hash LIKE lower('0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865')

WHERE from_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')

AND symbol LIKE 'sUSD'

AND block_timestamp > CURRENT_DATE - 14

GROUP BY 1

), vol_out as (

SELECT

from_address,

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

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts

ON address = contract_address

--WHERE tx_hash LIKE lower('0x2072d57af9eb255993f0307d9713a2518ba7686cee15a47d6af1ba07031fa865')

WHERE to_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')

AND symbol LIKE 'sUSD'

AND block_timestamp > CURRENT_DATE - 14

GROUP BY 1

), tab2 as (

SELECT

from_address,

bets,

case WHEN volume_out is NULL THEN -1 * volume_in ELSE volume_out - volume_in END as gain_loss

FROM vol_in

LEFT OUTER join vol_out

on to_address = from_address

HAVING NOT gain_loss is NULL

AND NOT from_address IS NULL

--ORDER BY 2 DESC