-- Active Contracts Criteria:

-- Last 30 days snapshot of all transactions and their dependencies/imports to identify contract addresses.

-- Union with contracts that have emitted at least one event in the last 30 days.

WITH ContractInit AS (

SELECT

event_contract,

MIN(block_timestamp) AS ContractInitialized_Date,

MIN(inserted_timestamp) AS InsertedTimestampInit_Date

FROM flow.core.fact_events

WHERE event_type = 'ContractInitialized'

GROUP BY event_contract

),

recent_transactions AS (

SELECT

tx_id, block_timestamp, authorizers, script

FROM

flow.core.fact_transactions

WHERE

block_timestamp >= DATEADD(day, -30, CURRENT_DATE)

),

EventsCount0 AS (

SELECT

distinct event_contract,

COUNT(DISTINCT x.tx_id) AS events_emitted,

count(distinct AUTHORIZERS[0]) as active_users,

max(x.block_timestamp) as last_event_emitted_date,

(count(distinct trunc(x.block_timestamp,'day'))/30)*100 as pcg_active_days

FROM flow.core.fact_events x join flow.core.fact_transactions y

on x.tx_id=y.tx_id

where x.block_timestamp>current_date-INTERVAL '30 DAYS'

GROUP BY event_contract

),