dune平台 sql整合

一、查询以太坊代币每日持币地址数

WITH addresses AS
  ( SELECT "to" AS adr
   FROM erc20."ERC20_evt_Transfer" tr
   WHERE contract_address = '/x0000000000' ) ,
     transfers AS
  ( SELECT DAY,
           address,
           token_address,
           sum(amount) AS amount -- Net inflow or outflow per day

   FROM
     ( SELECT date_trunc('day', evt_block_time) AS DAY,
              "to" AS address,
              tr.contract_address AS token_address,
              value AS amount
      FROM erc20."ERC20_evt_Transfer" tr --INNER JOIN addresses ad ON tr."to" = ad.adr

      WHERE contract_address = CONCAT('\x', substring('{{Token Address}}'
                                                      FROM 3))::bytea --Token address

      UNION ALL SELECT date_trunc('day', evt_block_time) AS DAY,
                       "from" AS address,
                       tr.contract_address AS token_address, -value AS amount
      FROM erc20."ERC20_evt_Transfer" tr --INNER JOIN addresses ad ON tr."from" = ad.adr

      WHERE contract_address = CONCAT('\x', substring('{{Token Address}}'
                                                      FROM 3))::bytea --Token address
 ) t
   GROUP BY 1,
            2,
            3 ) ,
     balances_with_gap_days AS
  ( SELECT t.day,
           address,
           SUM(amount) OVER (PARTITION BY address
                             ORDER BY t.day) AS balance, -- balance per day with a transfer
 lead(DAY, 1, now()) OVER (PARTITION BY address
                           ORDER BY t.day) AS next_day -- the day after a day with a transfer

   FROM transfers t ) ,
     days AS
  ( SELECT generate_series('2016-01-20'::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY -- Generate all days since the first contract
 ) ,
     balance_all_days AS
  ( SELECT d.day,
           address,
           SUM(balance/10^0) AS balance
   FROM balances_with_gap_days b
   INNER JOIN days d ON b.day <= d.day
   AND d.day < b.next_day -- Yields an observation for every day after the first transfer until the next day with transfer
 --INNER JOIN erc20.tokens erc ON b.token_address = erc.contract_address

   GROUP BY 1,
            2
   ORDER BY 1,
            2 )

SELECT b.day AS "Date",
       COUNT(address) AS "Holders",
       COUNT(address) - lag(COUNT(address)) OVER (
                                                  ORDER BY b.day) AS CHANGE
FROM balance_all_days b
WHERE balance > 0
GROUP BY 1
ORDER BY 1 ;

--SELECT date_trunc('month', tx.block_time) AS Month,
--COUNT(evt_tx_hash) AS "New wallets",
--100 * (count(distinct evt_tx_hash) - lag(count(distinct evt_tx_hash), 1) over (order by date_trunc('month', tx.block_time))) / lag(count(distinct evt_tx_hash), 1) over (order by date_trunc('month', tx.block_time)) as growth
--FROM instadapp_v2."InstaRegistry_evt_Created" c
--LEFT JOIN ethereum.transactions tx ON c.evt_tx_hash = tx.hash
--GROUP BY 1
--ORDER BY 1;
 --SELECT b.day,
--       COUNT(address),
--       COUNT(address) - lag(COUNT(address)) OVER (PARTITION BY address ORDER BY b.day) as diff
--FROM balance_all_days b
--WHERE balance > 0
--GROUP BY diff
--;