Dune简易学习笔记-查询特定时间内的交易额

**学习目的:**掌握dune里面给定的表名使用方法.

遇到的问题:

1. 在看教程过程中, fork别人的语句run后发现不能使用, 猜测是dune的表名更新导致无法使用.

2. 一些代码规范也改变了, 需要查阅文档后修改.

查询以太坊过去30天的交易总值

单个表有时无法满足要的所有信息, 比如查询发送eth的价值(以美元计价). 这时结合`ethereum.transactions`提供的eth交易数量和dune提供的eth的美元计价来计算.

以下是教程示范的代码:

with txs as (select block_time, value, price
from ethereum.”transactions” e
join prices.”layer1_usd” p
on p.minute = date_trunc(‘minute’, e.block_time)
where block_time > now() — interval ’10 days’
and symbol = ‘ETH’
)select date_trunc(‘day’, block_time) as “Date”, sum(value * price / 1e18) as “Value” from txs
group by 1 order by 1

问题:

join prices.”layer1_usd” p

此段代码报错: 请教ppt得知,是由于dune提供的查询有跟新,菜导致无法引用该方法. 查询后修改为

join prices.usd AS p

参看gpt后修改的代码:

WITH txs AS (
  SELECT
    e.block_time,
    e.value,
    p.price
  FROM ethereum.transactions AS e
  JOIN prices.usd AS p
    ON p.minute = DATE_TRUNC('minute', e.block_time)
  WHERE
    e.block_time > CURRENT_DATE - INTERVAL '10' day 
    AND p.symbol = 'ETH'
)
SELECT
  DATE_TRUNC('day', block_time) AS "Date",
  SUM(value * price / 1e18) AS "Value"
FROM txs
GROUP BY
  DATE_TRUNC('day', block_time)
ORDER BY
  "Date"

dune链接:

https://dune.com/queries/3789384/6371111/

总结: 明白dune提供的表如何使用, fork别人的代码有解决思路.