winnie-fsDB Terra 2 ReWrite
Updated 2023-12-13
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 DB Terra 2 Original @ https://flipsidecrypto.xyz/edit/queries/0fb4f633-7d9c-42bc-b823-0a28555896b2
WITH all_data AS (
SELECT
date_trunc(day, block_timestamp)::date AS date
, iff(RECEIVER = 'terra1qa5a8fkynmthlrh9wsae4sqnuxdrespsls7c7acayqqvs5s63eksm53snj', 'bond', 'unbond') AS tx_type
, iff(CURRENCY = 'ibc/B3F639855EE7478750CC8F82072307ED6E131A8EFF20345E1D136B50C4E5EC36', 'amp', 'b') AS cur_type
, count(1) AS actions
, sum(amount) / pow (10, 6) AS amount
FROM terra.core.ez_transfers
WHERE 1 = 1
AND (RECEIVER = 'terra1qa5a8fkynmthlrh9wsae4sqnuxdrespsls7c7acayqqvs5s63eksm53snj'
OR
SENDER = 'terra1qa5a8fkynmthlrh9wsae4sqnuxdrespsls7c7acayqqvs5s63eksm53snj')
AND CURRENCY IN ('ibc/B3F639855EE7478750CC8F82072307ED6E131A8EFF20345E1D136B50C4E5EC36'
, 'ibc/517E13F14A1245D4DE8CF467ADD4DA0058974CDCC880FA6AE536DBCA1D16D84E')
AND date > current_date - 30
GROUP BY all
)
SELECT
date
-- count_if won't work because i grouped on all_data
, sum(iff(tx_type = 'bond' AND cur_type = 'amp', actions, 0)) AS AMP_BONDED_COUNT
, sum(iff(tx_type = 'bond' AND cur_type = 'amp', amount, 0)) AS ampWHALE_BONDED
, count_if(tx_type = 'bond' AND cur_type = 'b') AS B_BONDED_COUNT
, sum(iff(tx_type = 'bond' AND cur_type = 'b', amount, 0)) AS bWHALE_BONDED
, count_if(tx_type = 'unbond' AND cur_type = 'amp') AS AMP_WITHDRAWN_COUNT
, sum(iff(tx_type = 'unbond' AND cur_type = 'amp', amount, 0)) AS ampWHALE_WITHDRAWN
, count_if(tx_type = 'unbond' AND cur_type = 'b') AS B_WITHDRAWN_COUNT
, sum(iff(tx_type = 'unbond' AND cur_type = 'b', amount, 0)) AS bWHALE_WITHDRAWN
FROM all_data
GROUP BY 1
ORDER BY date DESC
QueryRunArchived: QueryRun has been archived