SELECT

contract_address,

tx_hash AS transaction_Hash_first_table

FROM optimism.core.fact_token_transfers

LEFT JOIN optimism.core.dim_contracts

ON address = contract_address

AND NOT symbol LIKE 'sUSD'

AND block_timestamp > CURRENT_DATE -14 ),

Second_table AS

( SELECT

tx_hash as transaction_Hash_second_table,

block_timestamp,

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

FROM optimism.core.fact_token_transfers

LEFT JOIN optimism.core.dim_contracts

ON address = contract_address

WHERE to_address LIKE lower('0x170a5714112daEfF20E798B6e92e25B86Ea603C1')

AND symbol LIKE 'sUSD'

AND block_timestamp > CURRENT_DATE - 14

GROUP BY 1,2 )

SELECT

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)'

WHEN contract_address LIKE lower('0x144a0f95f6062f07e6f0ef20b54c8e70208d0f60') THEN 'Leon Edwards vs Kamaru Usman (HOME)'

WHEN contract_address LIKE lower('0xb4a6939cc5683c00e83bca6017e1cd0a99c0d0a1') THEN 'Nottingham Forest vs West Ham United (DRAW)'

WHEN contract_address LIKE lower('0x9509596197a55e514EF79c2dAE6960324a980324') THEN 'Nottingham Forest vs West Ham United (HOME)'

WHEN contract_address LIKE lower('0xc648febb78826a60242160b3bcc0425fd58c70a6') THEN 'Buffalo Bills vs Indianapolis Colts (AWAY)'

WHEN contract_address LIKE lower('0xa7eecb95bcefaf1ab7fc2e5777ceaa6e4aec1642') THEN 'Buffalo Bills vs Indianapolis Colts (HOME)'

WHEN contract_address LIKE lower('0xd76b9176a7594c06f063ba3e9e636c0c8542f0c2') THEN 'Philadelphia Phillies vs Miami Marlins (HOME)'

WHEN contract_address LIKE lower('0xeed9c9984bf4cc793263279ca2e7b4036c20a2da') THEN 'Nottingham Forest vs West Ham United (AWAY)'

ELSE 'Other' END as token,

sum(volume_out) as volume,