ioanUntitled Query
Updated 2022-12-07Copy Reference Fork
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 base_table as (select block_timestamp :: date as balance_date,
case when symbol = 'eth' then 'eth' else contract_address end as contract_address,
user_address,
symbol,
current_bal
from ethereum.core.ez_balance_deltas
where user_address = lower('0xF0E1015C33dA062E53fEF545631e75eAD91a5456')),
all_days as (select date_day as balance_date from ethereum.core.dim_dates),
address_ranges as (select user_address,
contract_address,
symbol,
min(balance_date :: date) as min_block_date,
current_date() :: date as max_block_date
from base_table
group by user_address, contract_address, symbol, max_block_date),
all_dates as (select c.balance_date, a.user_address, a.contract_address, a.symbol
from all_days c
left join address_ranges a
on c.balance_date between a.min_block_date and a.max_block_date
where a.user_address is not null),
eth_balances as (select user_address, contract_address, balance_date, current_bal, true as daily_activity
from base_table),
balance_tmp as (select d.balance_date,
d.user_address,
d.contract_address,
d.symbol,
b.current_bal,
b.daily_activity
from all_dates d
left join eth_balances b
on d.balance_date = b.balance_date and d.user_address = b.user_address and
d.contract_address = b.contract_address),
final as (select balance_date,
user_address,
contract_address,
Run a query to Download Data