duneanalytics 入门介绍

select * from <表> where <查询范围> order by <排序> limit <数量>

select * from articles  -- 显示所有数据

select author from articles -- 只显示 author

select * from articles limit 1 -- 只显示1条数据

select * from articles order by time desc  -- 根据 time 倒序排列，显示所有数据

select * from articles order by time desc limit 2  -- 根据 time 倒序排列数据，只显示2条数据

select * from articles where author= 'bob'  -- 只显示 author= bob 的数据，必须是单引号

select * from articles where author= 'bob' and time > 2020-05-01

author= 'bob' -- 正确

author= "bob" -- 错误

-- 查询 duneanalytics 所有数据库的所有表
SELECT * FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
    schemaname != 'information_schema';

-- https://duneanalytics.com/queries/50446
-- 查询表结构
SELECT
  table_name,
  column_name,
  data_type
FROM information_schema.columns WHERE table_name = 'blocks';

-- 查询 uniswap_v2 数据库的所有表
SELECT * FROM pg_catalog.pg_tables
  WHERE schemaname = 'uniswap_v2';

-- 模糊查询，查询数据库名类似 aave 的数据库
SELECT DISTINCT(schemaname) FROM pg_catalog.pg_tables
  WHERE schemaname LIKE '%aave%';

-- 模糊查询，查询表名类似 evt 的表
SELECT DISTINCT(tablename) FROM pg_catalog.pg_tables
  WHERE tablename LIKE '%evt%';

-- 模糊查询，查询uniswap_v2数据库中表名类似 evt 的表
SELECT DISTINCT(tablename) FROM pg_catalog.pg_tables
  WHERE tablename LIKE '%evt%' and schemaname = 'uniswap_v2';

-- https://duneanalytics.com/queries/50446

select * from ethereum.blocks order by time desc limit 1;

select * from ethereum.transactions order by block_time desc limit 1;

-- https://duneanalytics.com/queries/50439

-- https://duneanalytics.com/queries/50448

select * from  ethereum.contracts order by created_at desc limit 1;

SELECT
  table_name,
  column_name,
  data_type
FROM information_schema.columns WHERE table_name = 'contracts';

-- https://duneanalytics.com/queries/50451

SELECT
  table_name,
  column_name,
  data_type
FROM information_schema.columns WHERE table_name = 'logs';

select * from ethereum.logs limit 1;

SELECT * FROM pg_catalog.pg_tables
  WHERE schemaname = 'prices';

select * from prices.layer1_usd order by minute desc limit 1;

select * from prices.layer1_usd where symbol='ETH' order by minute desc limit 5;

SELECT DISTINCT(symbol) from prices.layer1_usd limit 10;

SELECT * FROM pg_catalog.pg_tables
  WHERE schemaname = 'erc20';

select * from erc20.tokens limit 10;

select * from erc20."ERC20_evt_Transfer" limit 10;

select * from erc20."ERC20_evt_Transfer" order by evt_block_time limit 10; -- 表名必须是 erc20."ERC20_evt_Transfer"

// https://github.com/Uniswap/uniswap-v2-core/blob/v1.0.1/contracts/UniswapV2Pair.sol#L51-L58

event Swap(
  address indexed sender,
  uint amount0In,
  uint amount1In,
  uint amount0Out,
  uint amount1Out,
  address indexed to
);

function swap(uint amount0Out, uint amount1Out, address to, bytes calldata data) external lock {
    // ...
  emit Swap(msg.sender, amount0In, amount1In, amount0Out, amount1Out, to); // 触发 Swap 事件
}

PairCreated(address,address,address,uint256) 0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9

Swap() 0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822

Sync() 0x1c411e9a96e071241c2f21f7726b17ae89e3cab4c78be50e062b03a9fffbbad1

Mint() 0x4c209b5fc8ad50758f13e2e1088ba56a560dff690a1c6fef26394f4c03821c4f

Burn() 0xdccd412f0b1252819cb1fd330b93224ca42612892bb3f4f789976e6d81936496

-- 交易对产生了多少事件？
-- 结果 6124700
select count(*) from ethereum.logs where
  "contract_address"= '\x0d4a11d5eeaac28ec3f61d100daf4d40471f1852';

-- 合约创建日期
-- 运行了10分钟 还没有结果
select block_time, block_number, tx_index from ethereum.logs
  where "contract_address"= '\x0d4a11d5eeaac28ec3f61d100daf4d40471f1852'
  order by "block_time" asc limit 1;

-- pair 添加流动性次数
-- 结果 37176
select count(*) from ethereum.logs
  where "contract_address"= '\x0d4a11d5eeaac28ec3f61d100daf4d40471f1852'
  and "topic1" = '\x4c209b5fc8ad50758f13e2e1088ba56a560dff690a1c6fef26394f4c03821c4f';

select count(*) from ethereum.logs where
  "contract_address"= CONCAT('\x', substring('{{token_addr}}' from 3))::bytea;

select date_trunc('day', block_time) as "Date", sum(value/1e18) as "Value"
from ethereum."transactions"
where block_time > now() - interval '10 days'
group by 1 order by 1

--group by 1 按照 第1个字段分组
--order by 1 按照 第1个字段排序

WITH transfers AS (
  SELECT
    evt_block_time,
    tr."from" AS address,
    -tr.value AS amount,
    contract_address
     FROM erc20."ERC20_evt_Transfer" tr
    WHERE contract_address = CONCAT('\x', substring('{{Token Address}}' from 3))::bytea -- Allow user to input 0x... format and convert to \x... format

UNION ALL

    SELECT
    evt_block_time,
    tr."to" AS address,
    tr.value AS amount,
      contract_address
     FROM erc20."ERC20_evt_Transfer" tr
     WHERE contract_address = CONCAT('\x', substring('{{Token Address}}' from 3))::bytea -- Allow user to input 0x... format and convert to \x... format
)

SELECT
  address,
  sum(amount/10^decimals) as balance
  FROM transfers tr
  LEFT JOIN erc20.tokens tok ON tr.contract_address = tok.contract_address
  GROUP BY 1
  ORDER BY 2 DESC
  LIMIT 10

-- 查询 uniswap_v3 数据库的所有表
SELECT * FROM pg_catalog.pg_tables
  WHERE schemaname = 'uniswap_v3';

  -- 模糊查询，查询表名类似 evt 的表
SELECT DISTINCT(tablename) FROM pg_catalog.pg_tables
  WHERE tablename LIKE '%evt%' and schemaname = 'uniswap_v3';

 select * from  uniswap_v3."NonfungibleTokenPositionManager_evt_IncreaseLiquidity" where
   contract_address = '\xc36442b4a4522e871399cd717abdd847ab11fe88';