Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DeFi Dune Analytic Snippets
WITH
vars AS (
SELECT
'\x4215606a720477178AdFCd5A59775C63138711e8'::bytea AS fee_address,
'\xdac17f958d2ee523a2206206994597c13d831ec7'::bytea AS usdt_underlying_token, --USDT
'\xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'::bytea AS usdc_underlying_token, -- USDC
'\x6b175474e89094c44da98b954eedeac495271d0f'::bytea AS dai_underlying_token, -- DAI
'\x57ab1ec28d129707052df4df418d58a2d46d5f51'::bytea AS susd_underlying_token, -- SUSD
'\x0000000000085d4780b73119b644ae5ecd22b376'::bytea AS tusd_underlying_token, -- TUSD
'\x2260fac5e5542a773aa44fbcfedf7c193bc2c599'::bytea AS wbtc_underlying_token -- WBTC
),
idle_usdt_risk AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT usdt_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 USDT Risk Adjusted' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleUSDT_v4_Risk_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleUSDT_v4_Risk_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT usdt_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_usdt_risk -- Change name here
WHERE idle_token <> 0),
idle_usdc_risk AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT usdc_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 USDC Risk Adjusted' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleUSDC_v4_Risk_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleUSDC_v4_Risk_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT usdc_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_usdc_risk -- Change name here
WHERE idle_token <> 0),
idle_dai_risk AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT dai_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 DAI Risk Adjusted' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleDAI_v4_Risk_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleDAI_v4_Risk_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT dai_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_dai_risk -- Change name here
WHERE idle_token <> 0),
idle_wbtc_yield AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT wbtc_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 WBTC Best Yield' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleWBTC_v4_Yield_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleWBTC_v4_Yield_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT wbtc_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_wbtc_yield -- Change name here
WHERE idle_token <> 0),
idle_tusd_yield AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT tusd_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 TUSD Best Yield' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleTUSD_v4_Yield_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleTUSD_v4_Yield_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT tusd_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_tusd_yield -- Change name here
WHERE idle_token <> 0),
idle_usdt_yield AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT usdt_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 USDT Best Yield' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleUSDT_v4_Yield_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleUSDT_v4_Yield_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT usdt_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_usdt_yield -- Change name here
WHERE idle_token <> 0),
idle_susd_yield AS ( -- Change name here
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT susd_underlying_token FROM vars) as underlying_token_address, -- Change var here
success,
contract_address,
'IdleV4 SUSD Best Yield' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleSUSD_v4_Yield_call_mintIdleToken" mi -- Change table here
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleSUSD_v4_Yield_call_redeemIdleToken" re -- Change table here
LEFT OUTER JOIN (
SELECT value, evt_tx_hash, contract_address
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT susd_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash -- Change var here
) idle_susd_yield -- Change name here
WHERE idle_token <> 0),
idle_usdc_yield AS (
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT usdc_underlying_token FROM vars) as underlying_token_address,
success,
contract_address,
'IdleV4 USDC Best Yield' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleUSDC_v4_Yield_call_mintIdleToken" mi
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleUSDC_v4_Yield_call_redeemIdleToken" re
LEFT OUTER JOIN (
SELECT value, evt_tx_hash
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT usdc_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash
) idle_usdc_yield
WHERE idle_token <> 0),
idle_dai_yield AS (
SELECT
blocktime,
idle_token/1e18 as idle_token,
source_token as source_token,
fee as fee,
(SELECT dai_underlying_token FROM vars) as underlying_token_address,
success,
contract_address,
'IdleV4 DAI Best Yield' as contract_name
FROM
(
SELECT
mi.call_block_time AS blocktime,
mi."output_mintedTokens" AS idle_token,
mi."_amount" AS source_token,
mi.call_success as success,
mi.contract_address,
0 as fee
FROM idle_v4."IdleDAI_v4_Yield_call_mintIdleToken" mi
UNION
SELECT
re.call_block_time AS blocktime,
-re."_amount" AS idle_token,
-re."output_redeemedTokens" AS source_token,
re.call_success as success,
re.contract_address,
-fee.value as fee
FROM idle_v4."IdleDAI_v4_Yield_call_redeemIdleToken" re
LEFT OUTER JOIN (
SELECT value, evt_tx_hash
FROM erc20."ERC20_evt_Transfer"
WHERE "to" IN(SELECT fee_address FROM vars)
AND contract_address IN (SELECT dai_underlying_token from vars) ) fee ON fee.evt_tx_hash=re.call_tx_hash
) idle_dai_yield
WHERE idle_token <> 0),
idle_combined as (
SELECT * FROM idle_dai_yield
UNION
SELECT * FROM idle_usdc_yield
UNION
SELECT * FROM idle_usdt_yield
UNION
SELECT * FROM idle_tusd_yield
UNION
SELECT * FROM idle_susd_yield
UNION
SELECT * FROM idle_wbtc_yield
UNION
SELECT * FROM idle_dai_risk
UNION
SELECT * FROM idle_usdc_risk
UNION
SELECT * FROM idle_usdt_risk
),
idle_token_movements as (
SELECT
idle_token,
date_trunc('day', blocktime) as blockday,
underlying_token_address,
contract_address,
contract_name,
(source_token+fee)/idle_token as token_price_no_decimals
FROM
idle_combined
WHERE success=True
),
idle_token_daily_movement as (
SELECT
blockday,
underlying_token_address,
contract_address,
contract_name,
SUM(idle_token * token_price_no_decimals) as underlying_tokens_movement
FROM idle_token_movements
GROUP BY underlying_token_address, contract_address, contract_name, blockday
),
days AS (
SELECT
generate_series AS ts
FROM
generate_series(
'08-11-2020',
(SELECT max(blockday) FROM idle_token_daily_movement),
'1 day')
),
tokens AS (SELECT contract_name FROM idle_token_daily_movement GROUP BY contract_name),
idle_usd_movement as (
SELECT
blockday,
contract_name,
idle_token_daily_movement.contract_address,
p.price * underlying_tokens_movement / 10^p.decimals as usd_movement
FROM idle_token_daily_movement
LEFT OUTER JOIN prices.usd p ON
p.minute = blockday
AND p.contract_address=underlying_token_address
)
SELECT
-- *,
day.ts as blockday,
day.contract_name,
SUM(COALESCE(usd_movement, 0)) over (PARTITION BY day.contract_name ORDER BY day.ts) AS aum
FROM (
SELECT * FROM days CROSS JOIN tokens
) day
LEFT JOIN idle_usd_movement ON day.ts=idle_usd_movement.blockday AND day.contract_name=idle_usd_movement.contract_name
SELECT
DATE_TRUNC('day',block_time) AS dt
, (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gas_price)) / 1e9 AS median_gas_price_gwei
FROM ethereum.transactions
WHERE
block_time >= (DATE_TRUNC('day',CURRENT_TIMESTAMP) - '90 days'::INTERVAL)
GROUP BY 1
ORDER BY 1;
WITH n AS (
SELECT '\x095ea7b3' as hash, 'approve' as name
UNION
SELECT '\xa457c2d7' as hash, 'decreaseAllowance' as name
UNION
SELECT '\xb6b55f25' as hash, 'deposit' as name
UNION
SELECT '\xde5f6268' as hash, 'depositAll' as name
UNION
SELECT '\xf6326fb3' as hash, 'depositETH' as name
UNION
SELECT '\xd389800f' as hash, 'earn' as name
UNION
SELECT '\x018ee9b7' as hash, 'harvest' as name
UNION
SELECT '\x39509351' as hash, 'increaseAllowance' as name
UNION
SELECT '\x92eefe9b' as hash, 'setController' as name
UNION
SELECT '\xab033ea9' as hash, 'setGovernance' as name
UNION
SELECT '\x45dc3dd8' as hash, 'setMin' as name
UNION
SELECT '\xa9059cbb' as hash, 'transfer' as name
UNION
SELECT '\x23b872dd' as hash, 'transferFrom' as name
UNION
SELECT '\x2e1a7d4d' as hash, 'withdraw' as name
UNION
SELECT '\x853828b6' as hash, 'withdrawAll' as name
UNION
SELECT '\x90386bbf' as hash, 'withdrawAllETH' as name
UNION
SELECT '\xf14210a6' as hash, 'withdrawETH' as name
), p AS (
SELECT price, minute FROM prices.layer1_usd WHERE symbol='ETH'
)
SELECT date_trunc('day', block_time) AS time, name, count(*) as count,
count(nullif(success = false, true)) as success,
count(nullif(success = true, true)) as failure,
count(nullif(success = true, true)) * 100 / count(*) as "failure rate (%)",
ROUND(avg(gas_price) / power(10, 9)::numeric, 3) as "avg gas price(gwei)",
ROUND(avg(gas_used)::numeric, 3) as "avg gas used",
ROUND(avg(gas_spent)::numeric, 3) as "avg gas spent(USD)",
ROUND(max(gas_spent)::numeric, 3) as "max gas spent(USD)"
FROM (
SELECT l.block_time, l.hash, n.name, success, gas_price, gas_used, gas_spent FROM (
SELECT block_time, cast(substring(data for 4) as text) as hash, success, gas_price, gas_used, (gas_price * gas_used / power(10, 18) * p.price)
as gas_spent FROM ethereum.transactions AS t
LEFT JOIN p ON p.minute = date_trunc('minute', t.block_time)
WHERE "to" IN (
'\xe1237aA7f535b0CC33Fd973D66cBf830354D16c7'
) AND p.minute > current_date - interval '1 week'
) as l INNER JOIN n ON l.hash = n.hash
) as r group by 1,2
time reward
17/07/20 00:00
40.477402764104454
18/07/20 00:00
1902.2046148273341
19/07/20 00:00
3050.1477305149037
20/07/20 00:00
4565.457539796877
21/07/20 00:00
4317.846538491829
22/07/20 00:00
7422.029683006186
23/07/20 00:00
6614.241363403734
(SELECT date_trunc('day', evt_block_time) as time,
sum(reward / 1e18) as reward
from yearn."YearnGovernance_YFI_evt_RewardPaid"
group by time)
UNION
(SELECT date_trunc('day', evt_block_time) as time,
sum(reward / 1e18) as reward
from yearn."YearnRewards_evt_RewardPaid"
group by time)
order by time asc
select '10' as numtrades, avg(ethratio) as priceineth, sum(token_a_amount) as yfibought from (
SELECT 1/token_a_amount * token_b_amount as ethratio, token_a_amount
FROM dex."trades"
where token_a_symbol = 'YFI'
and token_b_symbol = 'WETH'
order by block_time limit 10
)x union all
select '100' as numtrades, avg(ethratio) as priceineth, sum(token_a_amount) as yfibought from (
SELECT 1/token_a_amount * token_b_amount as ethratio, token_a_amount
FROM dex."trades"
where token_a_symbol = 'YFI'
and token_b_symbol = 'WETH'
order by block_time limit 100
)x union all
select '1000' as numtrades, avg(ethratio) as priceineth, sum(token_a_amount) as yfibought from (
SELECT 1/token_a_amount * token_b_amount as ethratio, token_a_amount
FROM dex."trades"
where token_a_symbol = 'YFI'
and token_b_symbol = 'WETH'
order by block_time limit 1000
)x union all
select '10000' as numtrades, avg(ethratio) as priceineth, sum(token_a_amount) as yfibought from (
SELECT 1/token_a_amount * token_b_amount as ethratio, token_a_amount
FROM dex."trades"
where token_a_symbol = 'YFI'
and token_b_symbol = 'WETH'
order by block_time limit 10000
)x union all
select '100000' as numtrades, avg(ethratio) as priceineth, sum(token_a_amount) as yfibought from (
SELECT 1/token_a_amount * token_b_amount as ethratio, token_a_amount
FROM dex."trades"
where token_a_symbol = 'YFI'
and token_b_symbol = 'WETH'
order by block_time limit 100000
)x
(SELECT date_trunc('week', evt_block_time) as time,
sum(reward / 1e18) as reward
from yearn."YearnGovernance_YFI_evt_RewardAdded"
group by time)
UNION
(SELECT date_trunc('week', evt_block_time) as time,
sum(reward / 1e18) as reward
from yearn."YearnRewards_evt_RewardAdded"
group by time)
order by time asc
time reward
13/07/20 00:00
20000
20/07/20 00:00
102076.15198941132
27/07/20 00:00
25625.516193815543
10/08/20 00:00
32128.678309074778
17/08/20 00:00
63503.13779233285
24/08/20 00:00
268902.6394317515
31/08/20 00:00
415239.55153764156
07/09/20 00:00
222698.34374389326
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.