0 reply
20 recasts
20 reactions
Do we think much of client diversity @mvr @cryptonjal @pichi ?
Ran this on Dune, suggesting that only 28.3% of casts came from warpcast in the last 30 days. Does super truly have 12924 unique users? Firefly at 20325? Coinbase wallet at 1968? Recaster at 223, with more casts, but Herocast at 505 (more users, less casts).
This suggests that we're more diverse than we thought, client-wise, but I wonder if I made some mistake, so asking for a sanity check!
WITH client_names AS (
SELECT
u.fid as app_fid,
u.value as client_name
FROM dune.neynar.dataset_farcaster_user_data u
WHERE u.type = 6 -- display name type
)
SELECT
s.app_fid,
COALESCE(cn.client_name,
CASE
WHEN s.app_fid = 9152 THEN 'Warpcast'
ELSE CONCAT('Unknown FID ', CAST(s.app_fid AS VARCHAR))
END
) as client_name,
COUNT(DISTINCT s.fid) as unique_users,
COUNT(DISTINCT c.hash) as total_casts,
ROUND(COUNT(DISTINCT c.hash) * 100.0 / SUM(COUNT(DISTINCT c.hash)) OVER(), 2) as percentage
FROM dune.neynar.dataset_farcaster_signers s
LEFT JOIN client_names cn ON s.app_fid = cn.app_fid
LEFT JOIN dune.neynar.dataset_farcaster_casts c
ON s.fid = c.fid
AND c.timestamp >= CURRENT_DATE - INTERVAL '30' DAY
WHERE s.app_fid IS NOT NULL
GROUP BY s.app_fid, cn.client_name
ORDER BY total_casts DESC; 4 replies
0 recast
4 reactions
1 reply
0 recast
2 reactions
0 reply
0 recast
0 reaction
