ASSET_FROM | ASSET_TO | AVERAGE_TRANSACTION_USD | SWAP_COUNT | |
|---|---|---|---|---|
1 | ETH.DAI-0X6B175474E89094C44DA98B954EEDEAC495271D0F | THOR.RUNE | 431.307690182 | 1974170 |
2 | THOR.RUNE | BSC.BNB | 31.474780315 | 1 |
3 | ETH.ETH | GAIA.ATOM | 468.440931491 | 1 |
4 | ETH.USDC-0XA0B86991C6218B36C1D19D4A2E9EB0CE3606EB48 | THOR.RUNE | 142.864048442 | 49 |
5 | THOR.RUNE | BCH.BCH | 15.538121579 | 1 |
6 | BCH.BCH | ETH.ETH | 16.109797222 | 1 |
7 | BCH.BCH | THOR.RUNE | 16.109804553 | 1 |
8 | ETH.ETH | THOR.RUNE | 468.441154304 | 1 |
9 | THOR.RUNE | DOGE.DOGE | 4.008146388 | 1 |
10 | THOR.RUNE | BSC.USDC-0X8AC76A51CC950D9822D68B83FE1AD97B32CD580D | 166.179456149 | 36 |
11 | LTC.LTC | DOGE.DOGE | 4.008148164 | 1 |
12 | ETH.DAI-0X6B175474E89094C44DA98B954EEDEAC495271D0F | ETH.ETH | 431.240571154 | 1972800 |
13 | LTC.LTC | THOR.RUNE | 4.008146712 | 1 |
14 | THOR.RUNE | GAIA.ATOM | 468.440895185 | 1 |
15 | ETH.USDC-0XA0B86991C6218B36C1D19D4A2E9EB0CE3606EB48 | BSC.USDC-0X8AC76A51CC950D9822D68B83FE1AD97B32CD580D | 154.519400321 | 42 |
16 | THOR.RUNE | ETH.ETH | 431.116936726 | 1972802 |
ShapeShift[WIP] Combined table Aggregated Data
Updated 2024-02-06
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH paired_swaps AS (
SELECT
fs1.TX_ID,
fs1.FROM_ASSET as asset_from,
fs2.TO_ASSET as asset_to,
(fs1.FROM_AMOUNT_USD + fs2.TO_AMOUNT_USD) / 2 as avg_transaction_usd,
(fs1.AFFILIATE_FEE_BASIS_POINTS + fs2.AFFILIATE_FEE_BASIS_POINTS) / 2 as avg_affiliate_fee_basis_points,
fs1.BLOCK_TIMESTAMP,
fs1.AFFILIATE_ADDRESS
FROM thorchain.defi.fact_swaps fs1
JOIN thorchain.defi.fact_swaps fs2
ON fs1.TX_ID = fs2.TX_ID
AND fs1.FROM_ASSET != fs2.TO_ASSET
AND fs1.FROM_ASSET != 'RUNE'
AND fs2.TO_ASSET != 'RUNE'
WHERE fs1.BLOCK_TIMESTAMP::DATE BETWEEN '{{start_date}}' AND '{{end_date}}'
AND fs1.AFFILIATE_ADDRESS = 'ss'
),
joined_swaps AS (
SELECT
ps.TX_ID,
ps.asset_from,
ps.asset_to,
ps.avg_transaction_usd,
ps.avg_affiliate_fee_basis_points,
ps.BLOCK_TIMESTAMP,
ps.AFFILIATE_ADDRESS,
fse.MEMO
FROM paired_swaps ps
JOIN thorchain.defi.fact_swaps_events fse
ON ps.TX_ID = fse.TX_ID
),
aggregated_swaps AS (
SELECT
asset_from,
asset_to,
Last run: over 1 year ago
16
912B
25s