SELECT

contract_address,

-- symbol,

tx_hash as txh_1

-- tx_hash

-- 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 not symbol LIKE 'sUSD'

AND block_timestamp > CURRENT_DATE - 14

), tab2 as (

SELECT

tx_hash as txh_2,

block_timestamp,

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,2

date_trunc('day', block_timestamp) as day,

CASE WHEN contract_address LIKE lower('0x110e053cd62763bad8a7591e2f6818ab35fd526d') THEN 'Boston Red Sox vs New York Yankees (AWAY)'

WHEN contract_address LIKE lower('0x6f50fc73057feca16ed86c32192e76fb79d90606') THEN 'Boston Red Sox vs New York Yankees (HOME)'

WHEN contract_address LIKE lower('0xb7b84b5c84c57ecd881623b6bf3d16b995c7a8e9') THEN 'Leon Edwards vs Kamaru Usman (AWAY)'