Multipartite2022-05-08 Coinsource (rough) check ver4
Updated 2022-05-08
999
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
›
⌄
--VERY ROUGH QUERY, not taking into account exact fee amounts or to_e8 output amounts!
--Build in slashing amounts too (wrong spending)
WITH
cointype AS
(
SELECT
'BTC.BTC' AS coin,
),
swapsin AS
(
SELECT block_timestamp, block_id,
from_address AS address,
CAST(POWER(10,-8) * from_e8 AS DECIMAL(17,8)) AS net_change,
net_change AS swapped_in,
0 AS swapped_out,
0 AS deposited,
0 AS withdrawn,
0 AS donated,
0 AS gas_spent,
0 AS slashes
FROM cointype, thorchain.swap_events
WHERE (from_asset = coin)
),
outs AS
(
SELECT block_timestamp, block_id,
to_address AS address,
CAST(-1 * POWER(10, -8) * asset_e8 AS DECIMAL(17,8)) AS net_change,
0 AS swapped_in,
(CASE WHEN in_tx IN (SELECT tx_id FROM thorchain.swap_events) THEN CAST(-1 * net_change AS DECIMAL(17,8)) ELSE 0 END) AS swapped_out,
0 AS deposited,
(CASE WHEN in_tx IN (SELECT tx_id FROM thorchain.unstake_events) THEN CAST(-1 * net_change AS DECIMAL(17,8)) ELSE 0 END) AS withdrawn,
0 AS donated,
Run a query to Download Data