-- forked from Net New Users @ https://flipsidecrypto.xyz/edit/queries/95fe897e-497d-468b-aaea-5fb05f40d134

--Satellite/Squid/

with axelscore_users AS (

select distinct

lower(wallet_address) as wallet_address

from BI_ANALYTICS.BRONZE_API.DYNAMIC_API

where environment_id = '88e7cf93-cd57-4664-b5da-9682b46074e0' --AXELscore environment

),

campaigns AS (

select distinct

start_at::DATE as start_date,

end_at::DATE as end_date,

split_part(slug, '-', 2) AS protocol

from bi_analytics.silver.bounties

where slug like '%axelscore%'

),

prices AS (

date_trunc('day', hour) AS day,

token_address,

CASE WHEN token_address like '%uusdc' or token_address = '0xeb466342c4d449bc9f53a865d5cb90586f405215'

then 1 else avg(price) END AS avg_price

from CROSSCHAIN.price.ez_hourly_token_prices

where hour::DATE >= '2023-05-01'

group by 1,2

),

new_users AS (

start_date,

'btcb' AS protocol,

lower(sender) AS user_address,