Dragonswap 2

Updated 2024-09-05

with datet as (

select

block_timestamp,

tx_hash,

ORIGIN_FROM_ADDRESS,

regexp_substr_all(SUBSTR(data, 3, len(data)), '.{64}') AS segmented,

abs(ethereum.public.udf_hex_to_int('s2c', segmented[0]::string)::int/1e18) as amount0

--ethereum.public.udf_hex_to_int('s2c', segmented[1]::string) as amount1

FROM kaia.core.fact_event_logs

WHERE topics[0] = '0x19b47279256b2a23a1665c810c8d55a1758940ee09377d4f8d26497a3577dc83'

--origin_to_address='0x5ea3e22c41b08dd7dc7217549939d987ed410354'

--and origin_from_address = lower('0xDfF9D1Cff3c37B84704EEd4465A83E6c453C35F6')

and contract_address = '0xb64ba987ed3bd9808dbcc19ee3c2a3c79a977e66'

and tx_succeeded = 'TRUE'

order by block_timestamp desc),

pricet as (

select

HOUR::date as p_date,

avg(PRICE) as price

from kaia.price.ez_prices_hourly

where SYMBOL = 'KLAY'

and hour::date >= '2024-01-01'

group by 1)

select

--date_trunc('day',block_timestamp) as date,

count(distinct tx_hash) as txs,

--sum(count(distinct tx_hash)) over (order by date_trunc('day',block_timestamp)) as cum_txs,

count(distinct ORIGIN_FROM_ADDRESS) as users,

--sum(count(distinct ORIGIN_FROM_ADDRESS)) over (order by date_trunc('day',block_timestamp)) as cum_users,

round(sum(amount0),2) as volume_klay,

round(sum(amount0 * price),0) as volume_usd,

--round(sum(sum(amount0 * price)) over (order by date_trunc('day',block_timestamp)),2) as cum_usd_volume,

from datet d left join pricet p on block_timestamp::date = p_date::date

--where block_timestamp::date < current_date

QueryRunArchived: QueryRun has been archived

flipside