with tx_event AS (

select distinct tx_id as tx_id_event, 'Superfluid Staking' as event from osmosis.core.fact_superfluid_staking where current_date - date(block_timestamp) < 365

union all

select distinct tx_id, 'Osmosis Staking' as event from osmosis.core.fact_staking where current_date - date(block_timestamp) < 365

union all

select distinct tx_id, 'Staking Rewards' as event from osmosis.core.fact_staking_rewards where current_date - date(block_timestamp) < 365

union all

select distinct tx_id, 'Swaps' as event from osmosis.core.fact_swaps where current_date - date(block_timestamp) < 365

union all

select distinct tx_id, 'LP Action' as event from osmosis.core.fact_liquidity_provider_actions where current_date - date(block_timestamp) < 365

union all

select distinct tx_id, 'Vote' as event from osmosis.core.fact_governance_votes where current_date - date(block_timestamp) < 365

union all

select distinct tx_id, 'IBC Transfer' as event from osmosis.core.fact_transfers where transfer_Type !='OSMOSIS' -- exclude osmosis transfers

and current_date - date(block_timestamp) < 365

),

events_aggregated AS (

SELECT

tx_id_event,

listagg(distinct event, ', ') as events_performed,

count(tx_id_event) as counts

FROM tx_event

GROUP BY 1

),

labelled_tx AS (

SELECT

block_timestamp,

tx_id,

tx_from,

nvl(events_performed, 'Others') as event,

TRIM(REGEXP_REPLACE(fee, '[^[:digit:]]', ' ')) AS numeric_fee

FROM osmosis.core.fact_transactions

LEFT JOIN events_aggregated ON tx_id = tx_id_event