SELECT *

FROM optimism.core.dim_labels

WHERE project_name LIKE 'perpetual protocol'

), tab1 as (

SELECT DISTINCT tx_hash

FROM optimism.core.fact_event_logs

WHERE contract_address LIKE lower('0x82ac2CE43e33683c58BE4cDc40975E73aA50f459')

), tab2 as (

SELECT

ORIGIN_FROM_ADDRESS,

count(DISTINCT tx_hash) as events

-- date_trunc('day', block_timestamp)

--FROM optimism.core.fact_event_logs

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts

ON address = contract_address

--WHERE contract_address LIKE lower('0x82ac2CE43e33683c58BE4cDc40975E73aA50f459')

--WHERE contract_address LIKE lower('0x3Fb3282e3BA34A0Bff94845f1800Eb93CC6850d4')

WHERE tx_hash in (SELECT * from tab1)

AND symbol like '%v%'

GROUP BY 1

ORDER BY 2 DESC

LIMIT 30

SELECT

date_trunc('week', block_timestamp) as week,

CASE WHEN ORIGIN_FROM_ADDRESS IN (SELECT ORIGIN_FROM_ADDRESS FROM tab2) THEN 'Top User' ELSE 'Normal User' END,

count(DISTINCT tx_hash) as events,

count(DISTINCT ORIGIN_FROM_ADDRESS) as users,

sum(raw_amount / power(10, 18)) as volume

FROM optimism.core.fact_token_transfers

LEFT outer JOIN optimism.core.dim_contracts