with tx_event AS (select distinct tx_id as tx_id_event, 'Superfluid Staking' as event from osmosis.core.fact_superfluid_staking

union select distinct tx_id, 'Staking' as event from osmosis.core.fact_staking

union select distinct tx_id, 'Staking Rewards' as event from osmosis.core.fact_staking_rewards

union select distinct tx_id, 'Swaps' as event from osmosis.core.fact_swaps

union select distinct tx_id, 'LP Action' as event from osmosis.core.fact_liquidity_provider_actions

union select distinct tx_id, 'Governance Vote' as event from osmosis.core.fact_governance_votes

union select distinct tx_id, 'IBC Transfer' as event from osmosis.core.fact_transfers WHERE transfer_Type !='OSMOSIS' ),

labelled_tx AS ( SELECT block_timestamp, tx_id, tx_from, nvl(event, 'Others') as event, TRIM(REGEXP_REPLACE(fee, '[^[:digit:]]', ' ')) AS numeric_fee

FROM osmosis.core.fact_transactions

LEFT JOIN tx_event ON tx_id = tx_id_event

WHERE tx_status = 'SUCCEEDED')

SELECT date_trunc('day', block_timestamp) as date, event, count(tx_id) as tx_count, count(distinct tx_from) as users

FROM labelled_tx

GROUP BY 1,2

ORDER BY 1,2