SELECT Date(a.block_timestamp) AS day,

a.tx_hash,

origin_from_address,

round(EVENT_INPUTS:value::float/pow(10,c.decimals),2) AS volume,

round((EVENT_INPUTS:value::float/pow(10,c.decimals) * d.price),2) AS volume_usd

FROM optimism.core.fact_event_logs a

JOIN optimism.core.dim_contracts c

ON c.address = a.contract_address

JOIN optimism.core.fact_hourly_token_prices d

ON c.symbol = d.symbol

WHERE a.block_timestamp >= dateadd(week, -2, getdate())

and origin_to_address = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and event_name = 'Transfer'

and event_inputs:to = '0x170a5714112daeff20e798b6e92e25b86ea603c1'

and event_inputs:from not in ('0x0000000000000000000000000000000000000000', '0x170a5714112daeff20e798b6e92e25b86ea603c1')

and event_inputs:to != '0x0000000000000000000000000000000000000000'

and event_inputs:to != origin_from_address

and event_inputs:from = origin_from_address

AND tx_status = 'SUCCESS' )

SELECT count(DISTINCT tx_hash) AS no_of_txns,

count(DISTINCT origin_from_address) AS no_of_users,

sum(volume) AS tot_volume,

sum(volume_usd) AS tot_volume_usd

FROM overtime_dtls