WITH events AS ( --All EVENT TYPES
Select date_trunc('day', block_timestamp) As DATE,
EVENT_TYPE
FROM flow.core.fact_events ),
TOP_events AS ( -- Top 10 Events of past 5 days
SELECT EVENT_TYPE AS EV, COUNT(EVENT_TYPE)
FROM events
WHERE Date >= CURRENT_DATE - 2
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10)
-- EVENTS By date and count
SELECT Date, EVENT_TYPE, COUNT(EVENT_TYPE) AS event_count
FROM events
JOIN TOP_events ON events.event_type = TOP_events.ev
GROUP BY 1,2