Multipartite2023-07-12 ETH.ETH PUVI and SynthPerPoolDepth comparison
Updated 2023-07-12
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
›
⌄
-- forked from 2023-07-12 BTC.BTC PUVI and SynthPerPoolDepth comparison @ https://flipsidecrypto.xyz/edit/queries/dfa8b79a-9d80-493e-aa04-35fce60f4403
--Note that the first synth transaction was in block 4610718 (2022-03-09).
-- Check unit amounts with this URL:
-- https://thornode.ninerealms.com/thorchain/pool/[pool_name]?height=[block_id]
WITH
details AS (
SELECT 'ETH.ETH' AS pool_name
),
depths AS
(
SELECT DATE(reftable.block_timestamp) AS date, block_id, pool_name,
rune_e8, asset_e8, synth_e8,
0 AS LPunitchange
FROM (thorchain.core.fact_block_pool_depths AS reftable INNER JOIN thorchain.core.dim_block
ON reftable.dim_block_id = dim_block.dim_block_id)
WHERE pool_name = (SELECT pool_name FROM details)
AND reftable.block_timestamp IS NOT NULL
AND asset_e8 > 0
AND rune_e8 > 0
QUALIFY (block_id = MAX(block_id) OVER(PARTITION BY pool_name, date))
),
stakes AS
(
SELECT NULL AS date, block_id, pool_name,
NULL AS rune_e8, NULL AS asset_e8, NULL AS synth_e8,
stake_units AS LPunitchange
FROM (thorchain.core.fact_stake_events AS reftable INNER JOIN thorchain.core.dim_block
ON reftable.dim_block_id = dim_block.dim_block_id)
WHERE pool_name IN (SELECT pool_name FROM details)
Run a query to Download Data