with near_projects as (

select

ADDRESS, initcap(ADDRESS_NAME) as ADDRESS_NAME,

initcap(PROJECT_NAME) as PROJECT_NAME,

initcap(LABEL_TYPE) as LABEL_TYPE

from near.core.dim_address_labels

where LABEL_TYPE not in ('cex','token') and PROJECT_NAME is not null

, projects_events as (

select

BLOCK_TIMESTAMP::date as ddate,

SIGNER_ID,

METHOD_NAME,

RECEIVER_ID,

ADDRESS_NAME,

PROJECT_NAME,

LABEL_TYPE,

TX_HASH,

DEPOSIT/1e24 as Volume

from near.core.fact_actions_events_function_call

LEFT join near_projects ON RECEIVER_ID=ADDRESS

where 1=1

AND block_timestamp::date BETWEEN '2024-01-01' AND '2024-11-30'

AND RECEIPT_SUCCEEDED=true

and PROJECT_NAME is not null

select

PROJECT_NAME as "Project",

round(100 * (count(DISTINCT ddate)/(DATEDIFF(day, '2024-01-01'::DATE, '2024-11-30'::DATE) +1) ), 1) || ' %' as "Active Days %",

count(*) as "Contract Calls",