-- forked from Saleh / Meme Season_total_meme @ https://flipsidecrypto.xyz/Saleh/q/aYEkliCOJZFb/meme-season_total_meme with lst_price_raw_one_day as ( with lst_near_price as ( select top 1 date_trunc(day,HOUR)::date as price_date ,avg(CLOSE) as NEAR_price from near.price.fact_prices_ohlc_hourly where ASSET_ID='wrapped-near' and price_date is not null and hour::date = current_date-1 group by 1 order by price_date desc ) , lst_all_meme as ( select ASSET_ID ,case when ASSET_ID='shitzu' then 'SHITZU' when ASSET_ID='near-tinker-union-gear' then 'GEAR' when ASSET_ID='lonk-on-near' then 'LONK' when ASSET_ID='black-dragon' then 'BLACKDRAGON' when ASSET_ID='ref-finance' then 'xREF' when ASSET_ID='neko' then 'NEKO' end as token_name ,0 as NEAR_price ,date_trunc(day,HOUR)::date as price_date ,avg(CLOSE) as avg_price from near.price.fact_prices_ohlc_hourly where ASSET_ID in ('shitzu','near-tinker-union-gear','lonk-on-near','black-dragon','ref-finance','neko') and price_date is not null and hour::date = current_date-1 group by 1 ,2 ,3 ,4 order by price_date desc ) ,lst_Other as ( select top 1 symbol_out as ASSET_ID ,ASSET_ID as token_name ,NEAR_price ,date_trunc(day,block_timestamp::date) as price_date ,sum(amount_in)/sum(amount_out) * NEAR_price as avg_price from near.defi.ez_dex_swaps join lst_near_price on date_trunc(day,block_timestamp::date) = price_date where symbol_out in('TOUCHED','KAT','BENDOG','GP','UWON') and symbol_in ='wNEAR' and (amount_out > 0 and amount_in > 0) and price_date = current_date-1 group by 1,2,3,4 order by price_date desc ) select * from lst_all_meme union all select * from lst_Other union all select 'CHILL' as ASSET_ID ,'CHILL' as token_name ,0 as NEAR_price ,(current_date-1)::date price_date ,0.000521 as avg_price ) ,lst_meme_contracts as ( select 'BLACKDRAGON' as meme_name , 'blackdragon.tkn.near' as contract , 1e24 as decimal ,'

BLACKDRAGON ' as image ,'memefarm-xref-blackdragon.ref-labs.near' as xref_contract union all select 'TOUCHED' as meme_name ,'touched.tkn.near' as contract , 1e24 as decimal ,'

TOUCHED ' as image1 ,'memefarm-xref-touched.ref-labs.near' as xref_contract union all select 'LONK' as meme_name ,'token.lonkingnearbackto2024.near' as contract , 1e8 as decimal ,'

LONK ' as image ,'memefarm-xref-nkok.ref-labs.near' as xref_contract union all select 'GEAR' as meme_name ,'gear.enleap.near' as contract , 1e18 as decimal ,'

GEAR ' as image ,'memefarm-xref-gear.ref-labs.near' as xref_contract union all select 'SHITZU' as meme_name ,'token.0xshitzu.near' as contract , 1e18 as decimal ,'

SHITZU ' as image ,'memefarm-xref-shitzu.ref-labs.near' as xref_contract union all ---------------------------------------------------NEW-------------------------------------------------- select 'BENDOG' as meme_name ,'benthedog.near' as contract , 1e9 as decimal ,'

BENDOG ' as image ,'memefarm-xref-benthedog.ref-labs.near' as xref_contract union all select 'CHILL' as meme_name ,'chill-129.meme-cooking.near' as contract , 1e18 as decimal ,'

CHILL ' as image ,'memefarm-xref-chill.ref-labs.near' as xref_contract union all select 'GP' as meme_name ,'gp.token0.near' as contract , 1e18 as decimal ,'

GP ' as image ,'memefarm-xref-gp.ref-labs.near' as xref_contract union all select 'UWON' as meme_name ,'438e48ed4ce6beecf503d43b9dbd3c30d516e7fd.factory.bridge.near' as contract , 1e18 as decimal ,'

UWON ' as image ,'memefarm-xref-uwon.ref-labs.near' as xref_contract union all select 'KAT' as meme_name ,'kat.token0.near' as contract , 1e18 as decimal ,'

KAT ' as image ,'memefarm-xref-kat.ref-labs.near' as xref_contract union all select 'NEKO' as meme_name ,'ftv2.nekotoken.near' as contract , 1e24 as decimal ,'

NEKO ' as image ,'memefarm-xref-neko.ref-labs.near' as xref_contract ) ,lst_stake_con1 as ( select tx_hash from near.core.fact_actions_events_function_call where block_timestamp::date>='2024-01-29' and RECEIVER_ID='meme-farming_011.ref-labs.near' and METHOD_NAME='ft_on_transfer' and RECEIPT_SUCCEEDED=true ) ,lst_stake as ( select block_timestamp::date as date ,tx_hash ,signer_id as staker ,args:amount/decimal as amount ,meme_name ,image ,meme_name as token_temp from near.core.fact_actions_events_function_call join lst_stake_con1 using (tx_hash) join lst_meme_contracts on RECEIVER_ID = contract where block_timestamp::date>='2024-01-29' and METHOD_NAME='ft_transfer_call' and RECEIPT_SUCCEEDED=true group by all ) ,lst_unstake as ( select block_timestamp::date as date ,tx_hash ,signer_id as staker ,-1*ARGS:unstake_amount/decimal as amount ,meme_name ,image ,meme_name as token_temp from near.core.fact_actions_events_function_call join lst_meme_contracts on args:seed_id = contract where block_timestamp::date>='2024-01-29' and RECEIVER_ID='meme-farming_011.ref-labs.near' and METHOD_NAME='unlock_and_unstake_seed' and RECEIPT_SUCCEEDED=true group by all ) ,lst_stake_xref as ( select block_timestamp::date as date ,tx_hash ,signer_id as staker ,ARGS:amount/1e18 as amount ,meme_name ,image ,'xREF' as token_temp from near.core.fact_actions_events_function_call join lst_meme_contracts on RECEIVER_ID = xref_contract where block_timestamp::date >='2024-04-28' and RECEIPT_SUCCEEDED=true and PREDECESSOR_ID='xtoken.ref-finance.near' and METHOD_NAME='ft_on_transfer' ) ,lst_unstake_xref as ( select block_timestamp::date as date ,tx_hash ,signer_id as staker ,-1*ARGS:amount/1e18 as amount ,meme_name ,image ,'xREF' as token_temp from near.core.fact_actions_events_function_call join lst_meme_contracts on RECEIVER_ID = xref_contract where block_timestamp::date >='2024-04-28' and RECEIPT_SUCCEEDED=true and METHOD_NAME='withdraw_seed' and args:seed_id='xtoken.ref-finance.near' ) ,lst_all_meme as ( select 'Stake' as type,'MEME' as token_type,* from lst_stake union all select 'UnStake' as type,'MEME' as token_type,* from lst_unstake ) ,lst_all_xref as ( select 'Stake_Ref' as type,'xREF' as token_type,* from lst_stake_xref union all select 'UnStake_Ref' as type,'xREF' as token_type,* from lst_unstake_xref ) ,lst_group_meme as ( select meme_name ,avg_price ,image ,datediff(day,min(date),max(date)) as "Active days" ,count(DISTINCT tx_hash) as Stakes ,count(DISTINCT staker) as Stakers ,sum(amount) as "Stake TVL" ,avg(amount) as "Avg Stake Volume" ,sum(amount) * avg_price as "Stake TVL($)" ,avg(amount) * avg_price as "Avg Stake Volume($)" from lst_all_meme join lst_price_raw_one_day on token_name=meme_name group by 1,2,3 ) ,lst_group_xref as ( select meme_name ,avg_price ,image ,datediff(day,min(date),max(date)) as "Active days" ,count(DISTINCT tx_hash) as Stakes ,count(DISTINCT staker) as Stakers ,sum(amount) as "Stake TVL" ,avg(amount) as "Avg Stake Volume" ,sum(amount) * avg_price as "Stake TVL($)" ,avg(amount) * avg_price as "Avg Stake Volume($)" from lst_all_xref join lst_price_raw_one_day on token_name=token_temp group by 1,2,3 ) ,lst_group_all as ( select 'MEME' as type,* from lst_group_meme union all select 'xREF' as type,* from lst_group_xref ) select * from lst_group_all

Query	Creator	Created
handicapped-aquamarine near	damidez	Oct 4, 2024
zealous-turquoise near swaps defi	damidez	Oct 4, 2024
distribution of amount users near	damidez	Oct 4, 2024
Meme Season_total_meme near swaps defi	damidez	Oct 31, 2024
Ref meme staking summm near	damidez	Oct 3, 2024
play near	damidez	Oct 4, 2024
Ref meme staking trend near	damidez	Oct 4, 2024
Xref staking total near	damidez	Oct 4, 2024
Ref staking and unstaking trend copy near	damidez	Oct 4, 2024

damidez