t1 as (

trunc(x.block_timestamp,'day') as dates,

count(distinct x.tx_id) as governance_props_submissions,

count(distinct x.proposer) as active_proposers,

count(distinct proposal_id) as proposals

from terra.core.fact_governance_submit_proposal x

where x.block_timestamp between '2023-01-07' and '2023-01-22'

group by 1

),

t2 as (

select

trunc(y.block_timestamp,'day') as dates,

count(distinct y.tx_id) as votes,

count(distinct voter) as voters,

votes/voters as avg_votes_per_voter

from terra.core.fact_governance_votes y

where y.block_timestamp between '2023-01-07' and '2023-01-22'

group by 1

),

t3 as (

select

trunc(z.recorded_hour,'day') as dates,

avg(close) as price_usd

from crosschain.core.fact_hourly_prices z

where z.recorded_hour between '2023-01-07' and '2023-01-22' and id='terra-luna-2'

group by 1

)

SELECT

t3.dates as date,

case when date<'2023-01-14' then 'Week previous to Station launch announcement'

when date>'2023-01-14' then 'Week after Station launch announcement'

else 'Station launch announcement' end as period,

governance_props_submissions,active_proposers,proposals,votes,voters,avg_votes_per_voter,price_usd