winnie-fs2023-09-02 block_id versus timestamp second behaviour copy
Updated 2023-09-07
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
›
⌄
-- forked from Multipartite / 2023-09-02 block_id versus timestamp second behaviour @ https://flipsidecrypto.xyz/Multipartite/q/oC8PzcMVCzyx/2023-09-02-block_id-versus-timestamp-second-behaviour
WITH
minted AS (
SELECT DATE_TRUNC('second', block_timestamp) AS time,
to_e8 AS synth_minted_e8,
1e8 * to_e8 / from_e8 AS sats_per_rune
FROM thorchain.defi.fact_swaps_events
WHERE to_asset = 'BTC/BTC'
AND block_timestamp IS NOT NULL
),
burnt AS (
SELECT DATE_TRUNC('second', block_timestamp) AS time,
-1 * from_e8 AS synth_minted_e8,
1e8 * from_e8 / to_e8 AS sats_per_rune
FROM thorchain.defi.fact_swaps_events
WHERE from_asset = 'BTC/BTC'
AND block_timestamp IS NOT NULL
)
SELECT DISTINCT block_id, time, SUM(synth_minted_e8) OVER(ORDER BY block_id ASC) / 1e8 AS cumulative_synth_minted,
AVG(sats_per_rune) OVER(PARTITION BY time) AS sats_per_rune
FROM ((SELECT * FROM minted) UNION ALL (SELECT * FROM burnt)) AS reftable INNER JOIN thorchain.core.dim_block
ON reftable.time = DATE_TRUNC('second', dim_block.block_timestamp)
WHERE block_id BETWEEN 10129000 AND 10131000
ORDER BY time DESC
Run a query to Download Data