0xHaM-dThorchain Swap Flows To/From BTC (2nd query)
Updated 2024-11-08
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 Zook / Thorchain Swap Flows To/From BTC (2nd query) @ https://flipsidecrypto.xyz/Zook/q/XC2cY2SW7nji/thorchain-swap-flows-to-from-btc-2nd-query
with result as(
SELECT
month,
swap_type,
n_swaps,
n_swappers,
ROUND(total_volume) AS total_volume,
SUM(n_swaps) OVER (ORDER BY month) AS cumulative_n_swaps,
SUM(ROUND(total_volume)) OVER (ORDER BY month) AS cumulative_volume_to_BTC
FROM (
SELECT
DATE_TRUNC('month', BLOCK_TIMESTAMP) AS month,
CASE
WHEN FROM_ASSET ilike '%BTC%' THEN 'Swap from BTC'
WHEN TO_ASSET ilike '%BTC%' THEN 'Swap to BTC'
END AS swap_type,
count(*) as n_swaps,
count(DISTINCT FROM_ADDRESS) as n_swappers,
SUM(CASE
WHEN FROM_ASSET ilike '%BTC%' THEN -FROM_AMOUNT_USD
WHEN TO_ASSET ilike '%BTC%' THEN TO_AMOUNT_USD
END) AS total_volume
FROM
thorchain.defi.fact_swaps
WHERE
(FROM_ASSET ilike '%BTC%' OR TO_ASSET ilike '%BTC%')
AND BLOCK_TIMESTAMP >= '2023-01-01'
GROUP BY
month,
swap_type
) AS monthly_volumes
ORDER BY
month DESC)
SELECT
QueryRunArchived: QueryRun has been archived