-- --registry

-- SELECT FID, CREATED_AT as ts , 'registered' as label

-- from external.farcaster.dim_fids

-- UNION ALL

--casts/reply casts

SELECT

FID , TIMESTAMP as ts , 'casts' as label

from external.farcaster.fact_casts

UNION ALL

--links: follows/subs

SELECT

FID , TIMESTAMP as ts, 'links' as label

from external.farcaster.fact_links

UNION ALL

-- reactions: emotes/likes or quoting

SELECT

FID, TIMESTAMP as ts , 'reactions' as label

from external.farcaster.fact_reactions

,

counter as (

SELECT

fid

, COUNT(DISTINCT ts::date) as active_days

, COUNT(*) as total_events

, SUM(CASE WHEN label='registered' THEN 1 ELSE 0 END) as registry

, SUM(CASE WHEN label='casts' THEN 1 ELSE 0 END) as casts

, SUM(CASE WHEN label='links' THEN 1 ELSE 0 END) as links

, SUM(CASE WHEN label='reactions' THEN 1 ELSE 0 END) as reactions

from all_activity