Sbhn_NPUntitled Query
Updated 2023-02-17
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
›
⌄
with voting as (select
proposal_id,
VOTE_OPTION_TEXT,
count(DISTINCT tx_id) as vote,
count(DISTINCT voter) as voter
from terra.core.fact_governance_votes
where tx_succeeded = TRUE
group by 1,2
),
price as (
select
date_trunc('day', recorded_hour) as date,
avg(close) as luna_price
from crosschain.core.fact_hourly_prices
where id = 'terra-luna-2'
group by 1
)
SELECT
count(DISTINCT tx:body:messages[0]:content:title::string) AS title,
sum(tx:body:messages[0]:initial_deposit[0]:amount::int / pow(10,6)) AS deposit_luna,
sum(tx:body:messages[0]:initial_deposit[0]:amount::int / pow(10,6)*luna_price) as deposit_usd
FROM terra.core.fact_governance_submit_proposal pr
JOIN terra.core.fact_transactions tr ON pr.tx_id = tr.tx_id
join voting using(proposal_id)
join price c on pr.block_timestamp::date=c.date
WHERE proposal_type = 'CommunityPoolSpend'
AND pr.tx_succeeded = 'TRUE'
Run a query to Download Data