with token_pairs as (
select
coalesce(k1.symbol, 'AGI') || '-' || coalesce(k2.symbol, 'AGI') as pair_name,
p.pair,
p.evt_block_time,
p.token0,
p.token1,
p.stable
from auragi_arbitrum.PairFactory_evt_PairCreated p
left join tokens.erc20 k1 on p.token0 = k1.contract_address and k1.blockchain = 'arbitrum'
left join tokens.erc20 k2 on p.token1 = k1.contract_address and k2.blockchain = 'arbitrum'
),
token_transfer_detail as (
select date_trunc('minute', evt_block_time) as block_date,
evt_tx_hash as tx_hash,
contract_address,
"to" as user_address,
cast(value as decimal(38, 0)) as amount_raw
from erc20_arbitrum.evt_Transfer
where "to" in (select pair from token_pairs)
and evt_block_time >= date('2023-04-04')
union all
select date_trunc('minute', evt_block_time) as block_date,
evt_tx_hash as tx_hash,
contract_address,
"from" as user_address,
-1 * cast(value as decimal(38, 0)) as amount_raw
from erc20_arbitrum.evt_Transfer
where "from" in (select pair from token_pairs)
and evt_block_time >= date('2023-04-04')
),
token_price as (
select date_trunc('minute', minute) as block_date,
contract_address,
decimals,
symbol,
avg(price) as price
from prices.usd
where blockchain = 'arbitrum'
and contract_address in (select distinct contract_address from token_transfer_detail)
and minute >= date('2023-04-04')
group by 1, 2, 3, 4
union all
-- AGI price from swap trade
select date_trunc('minute', block_time) as block_date,
0xFF191514A9baba76BfD19e3943a4d37E8ec9a111 as contract_address,
18 as decimals,
'AGI' as symbol,
avg(case when token_in_address = 0xFF191514A9baba76BfD19e3943a4d37E8ec9a111 then token_in_price else token_out_price end) as price
from query_2337808
group by 1, 2, 3, 4
)
select p.symbol,
d.block_date,
d.tx_hash,
d.user_address,
d.contract_address,
d.amount_raw,
(d.amount_raw / power(10, p.decimals) * p.price) as amount_usd
from token_transfer_detail d
inner join token_price p on d.contract_address = p.contract_address and d.block_date = p.block_date
上面的查询逻辑如下：
先在token_pairs中得到这个项目的所有交易对（Pair）。
结合evt_Transfer表，查询出每一个交易对的资金转入转出详情。
在token_price中计算出哥哥Token的当前价格。因为这个是一个比较新的Token，Dune可能没有它的价格数据，所以我们使用了交易数据来换算价格。交易数据的详细列表在另外一个查询中，这里使用Query of Query的方式来引用。
最后，我们将时间序列和每一个小时的累积余额进行关联，补足缺失交易数据的时间段的值。请注意这里的关联条件：inner join date_series d on p.block_date <= d.block_date and d.block_date < p.next_date。这里使用了两个条件，限定累计余额的日期时间必须小于等于日期序列的日期时间值，同时序列的日期时间值必须小于下一个有记录的余额的日期时间值。这是一个很常见的处理技巧。因为并不是所有的Token在每一个时间段都有交易，如果遇到没有发生交易的时间段，我们需要用前一个时间段的余额来代表其在当前时间段的余额。这个应该不难理解，因为“当前时间段”内没有发生新的变化，所以余额自然跟上一个时间段相同。
查询代码如下：
with date_series as (
select block_date
from unnest(sequence(timestamp '2023-04-01 00:00:00', localtimestamp, interval '1' hour)) as tbl(block_date)
),
pool_balance_change as (
select symbol,
date_trunc('hour', block_date) as block_date,
sum(amount_usd) as amount
from query_2339248
group by 1, 2
),
pool_balance_summary as (
select symbol,
block_date,
sum(amount) over (partition by symbol order by block_date) as balance_amount,
lead(block_date, 1, current_date) over (partition by symbol order by block_date) as next_date
from pool_balance_change
order by 1, 2
)
select d.block_date,
p.symbol,
p.balance_amount
from pool_balance_summary p
inner join date_series d on p.block_date <= d.block_date and d.block_date < p.next_date
order by 1, 2
SELECT SUM(50/POWER(2, ROUND(height/210000))) as Supply,
SUM(50/POWER(2, ROUND(height/210000)) * p.price) /POWER(10, 9) AS "Market Cap"
FROM bitcoin.blocks
INNER JOIN (
SELECT price FROM prices.usd_latest
WHERE symbol='BTC'
AND contract_address IS NULL
) p ON TRUE
今天学习的下一个指标是日/月活跃用户 (Daily/Monthly Active User,DAU/MAU)。相对于绝对交易数额，活跃用户的数目更能反应一个协议受欢迎程度。由于少数用户的大额交互就可以拉高交易数额，活跃的用户数可以更客观的描述该协议的热度。它的计算方式比较简单，我们只要找出与某个合约交易的钱包地址，并按天/月统计频数即可得出。 我们以最近比较热门的Lens为例：
with daily_count as (
select date_trunc('day', block_time) as block_date,
count(*) as transaction_count,
count(distinct "from") as user_count
from polygon.transactions
where "to" = 0xdb46d1dc155634fbc732f92e853b10b288ad5a1d -- LensHub
and block_time >= date('2022-05-16') -- contract creation date
group by 1
order by 1
)
select block_date,
transaction_count,
user_count,
sum(transaction_count) over (order by block_date) as accumulate_transaction_count,
sum(user_count) over (order by block_date) as accumulate_user_count
from daily_count
order by block_date
with optimism_new_users as (
SELECT "from" as address,
min(block_time) as start_time
FROM optimism.transactions
GROUP BY 1
)
SELECT date_trunc('day', start_time) as block_date,
count(n.address) as new_users_count
FROM optimism_new_users n
WHERE start_time >= date('2022-10-01')
GROUP BY 1
