with call_contracts as (

select

block_timestamp::date as date,

lower(trim(project_name)) as project_name,

count(method_name) as call_contracts,

count(

DISTINCT(tx_hash)

) as transactions,

count(

DISTINCT(signer_id)

) as users

from

near.core.fact_actions_events_function_call a

join near.core.dim_address_labels b on a.receiver_id = b.address

where

block_timestamp::date >= '2021-01-01'

and block_timestamp::date <= CURRENT_DATE

and (lower(trim(project_name)) IN ('nearcrowd') or lower(trim(b.address)) IN ('nearcrowd'))

group by

1,2

),

volume as (

select

block_timestamp::date as date,

lower(trim(b.project_name)) as project_name,

sum(

deposit / pow(10, 24)

) as near_amount,

avg(

deposit / pow(10, 24)

) as avg_near_amount

from

NEAR.core.fact_transfers a

join near.core.dim_address_labels b on a.tx_receiver = b.address