SELECT

tx_hash as tx1,

CASE WHEN to_a = 1 and txns = 1 THEN 'Bridge from OP'

WHEN from_a = 1 and txns = 1 THEN 'Bridge to OP'

WHEN from_a = 1 and txns = 2 THEN 'Withdraw Liquidity'

WHEN to_a = 1 and txns = 2 THEN 'Deposit Liquidity' end as event

FROM (

SELECT

tx_hash,

COUNT(CASE WHEN to_address LIKE lower('0xDecC0c09c3B5f6e92EF4184125D5648a66E35298') then 1 END) as to_a,

COUNT(CASE WHEN from_address LIKE lower('0xDecC0c09c3B5f6e92EF4184125D5648a66E35298') then 1 END) as from_a,

count(*) as txns

FROM optimism.core.fact_token_transfers

WHERE tx_hash in (

SELECT

tx_hash

FROM optimism.core.fact_token_transfers

WHERE to_address LIKE lower('0xDecC0c09c3B5f6e92EF4184125D5648a66E35298')

OR from_address LIKE lower('0xDecC0c09c3B5f6e92EF4184125D5648a66E35298')

GROUP BY 1

)

GROUP BY 1

)

), tab1 as (

SELECT

date_trunc('day', block_timestamp) as day,

COUNT(DISTINCT CASE WHEN event LIKE 'Bridge from OP' THEN from_address END) as Bridge_from_OP,

COUNT(DISTINCT CASE WHEN event LIKE 'Bridge to OP' THEN to_address END) as Bridge_to_OP,

COUNT(DISTINCT CASE WHEN event LIKE 'Withdraw Liquidity' THEN to_address END) as Withdraw_Liquidity,

COUNT(DISTINCT CASE WHEN event LIKE 'Deposit Liquidity' THEN from_address END) as Deposit_Liquidity

FROM optimism.core.fact_token_transfers

LEFT outer JOIN tx_labels

on tx1 = tx_hash

WHERE not tx1 is NULL