'Terra' AS Chain,

A.BLOCK_TIMESTAMP AS "Submit Date",

B.BLOCK_TIMESTAMP AS "Vote Date",

timediff('HOUR',"Submit Date","Vote Date") AS "Votimg Time"

from terra.core.fact_governance_submit_proposal A inner join terra.core.fact_governance_votes B on A.PROPOSAL_ID=B.PROPOSAL_ID

union ALL

Select

'Cosmos' AS Chain,

A.BLOCK_TIMESTAMP AS "Submit Date",

B.BLOCK_TIMESTAMP AS "Vote Date",

timediff('HOUR',"Submit Date","Vote Date") AS "Votimg Time"

from cosmos.core.fact_governance_submit_proposal A inner join cosmos.core.fact_governance_votes B on A.PROPOSAL_ID=B.PROPOSAL_ID

UNION ALL

Select

'Osmosis' AS Chain,

A.BLOCK_TIMESTAMP AS "Submit Date",

B.BLOCK_TIMESTAMP AS "Vote Date",

timediff('HOUR',"Submit Date","Vote Date") AS "Votimg Time"

from osmosis.core.fact_governance_submit_proposal A inner join osmosis.core.fact_governance_votes B on A.PROPOSAL_ID=B.PROPOSAL_ID )

select

chain,

case when "Votimg Time" <1 then 'less than 1-HOUR'

when "Votimg Time">=1 and "Votimg Time"<=4 then '1-4 HOUR'

when "Votimg Time"> 4 and "Votimg Time"<=8 then '4-8 HOUR'

when "Votimg Time"> 8 and "Votimg Time"<=12 then '8-12 HOUR'

when "Votimg Time"> 12 and "Votimg Time"<=24 then '12-24 HOUR'

when "Votimg Time" > 24 then 'more than 1-day'

end AS Time,

count(*) AS "Votes number"

from tb group by 1,2