Afonso_DiazOvertime
Updated 2024-10-26
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with
pricet as (
select
hour::date as date,
avg(price) as price_usd
from ethereum.price.ez_prices_hourly
where symbol = 'WETH'
group by 1
),
main as (
select
tx_hash,
block_timestamp,
origin_from_address as user,
nvl(amount_in_usd, amount_out_usd) as amount_usd,
symbol_in,
symbol_out,
tx_fee * price_usd as tx_fee_usd
from arbitrum.defi.ez_dex_swaps
join arbitrum.core.fact_transactions
using (tx_hash, block_timestamp)
left join pricet on date = block_timestamp::date
where platform ilike 'camelot%'
and block_timestamp::date between '{{ start_date }}' and '{{ end_date }}'
and amount_usd > 0
and amount_usd < 1e6
),
overtime as (
select
date_trunc('{{ period }}', block_timestamp) as date,
count(distinct tx_hash) as txns,
count(distinct user) as users,
sum(tx_fee_usd) as fee_usd,
QueryRunArchived: QueryRun has been archived