-- forked from Aephia / R4_prices @ https://flipsidecrypto.xyz/Aephia/q/6QwitG9NECpH/r4_prices WITH r4_trade_volume AS ( select --* date_trunc('day', block_timestamp::date) as date, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS fuel_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS fuel_atlas_amount, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS food_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS food_atlas_amount, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS ammo_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS ammo_atlas_amount, ------------------------------------------------------ sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL' then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) AS tool_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL' then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) AS tool_atlas_amount from solana.core.fact_events--, lateral flatten(input => inner_instruction) WHERE program_id = 'traderDnaR5w6Tcoi3NFm53i48FTDNbGjBSZwWXDRrg' AND succeeded = 'true' AND inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount != 0 AND inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount != 0 AND inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount != 0 --AND inner_instruction[0]:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' --AND log_messages::string LIKE '%ProcessExchange%' --AND tx_id = 'yVyU5pWLe5Uh8gMj2nwMSWZp1G8sGFdhpurG9mirgNNHTm8kG6Benk7m1gPParfZJvY8W8vTiaZWZchkDZKmVjL' --AND inner_instructions[0]:instructions[1]:parsed:info:mint::string = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND block_timestamp > current_date-60 --'2023-06-01' --AND block_timestamp < '2023-06-01' --limit 100 group by 1 order by 1 desc ), r4_prices AS ( SELECT --* date_trunc('day', block_timestamp::date) as date, ---------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) as fuel_atlas_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) as fuel_amount, fuel_atlas_amount / fuel_amount as fuel_price, -------------------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) as food_atlas_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'foodQJAztMzX1DKpLaiounNe2BDMds5RNuPC6jsNrDG' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) as food_amount, food_atlas_amount / food_amount as food_price, -------------------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) as ammo_atlas_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'ammoK8AkX2wnebQb35cDAZtTkvsXQbi82cGeTnUvvfK' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) as ammo_amount, ammo_atlas_amount / ammo_amount as ammo_price, -------------------------------------------------------------- sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) end) as tool_atlas_amount, sum(case when inner_instruction:instructions[2]:parsed:info:mint::string = 'tooLsNYLiVqzg8o4m3L2Uetbn62mvMWRqkog6PQeYKL' AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount > 0.0001 AND (inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount + inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount) / inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount < 0.01 then inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount end) as tool_amount, tool_atlas_amount / tool_amount as tool_price, ------------------------------------------------------ (fuel_price / 0.00144336 -1) *100 as fuel_price_increase, (food_price / 0.0006144 -1) *100 as food_price_increase, (ammo_price / 0.00215039 -1) *100 as ammo_price_increase, (tool_price / 0.0017408 -1) *100 as tool_price_increase from solana.core.fact_events--, lateral flatten(input => inner_instruction) WHERE program_id = 'traderDnaR5w6Tcoi3NFm53i48FTDNbGjBSZwWXDRrg' AND succeeded = 'true' AND inner_instruction:instructions[0]:parsed:info:tokenAmount:uiAmount != 0 AND inner_instruction:instructions[1]:parsed:info:tokenAmount:uiAmount != 0 AND inner_instruction:instructions[2]:parsed:info:tokenAmount:uiAmount != 0 --AND inner_instruction[0]:instructions[2]:parsed:info:mint::string = 'fueL3hBZjLLLJHiFH9cqZoozTG3XQZ53diwFPwbzNim' --AND log_messages::string LIKE '%ProcessExchange%' --AND tx_id = 'yVyU5pWLe5Uh8gMj2nwMSWZp1G8sGFdhpurG9mirgNNHTm8kG6Benk7m1gPParfZJvY8W8vTiaZWZchkDZKmVjL' --AND inner_instructions[0]:instructions[1]:parsed:info:mint::string = 'ATLASXmbPQxBUYbxPsV97usA3fPQYEqzQBUHgiFCUsXx' AND block_timestamp > current_date-60 --'2023-06-01' --AND block_timestamp < '2023-06-01' --limit 100 group by 1--,2,4,6,8 order by 1 desc ) SELECT x.date, x.fuel_amount, x.food_amount, x.ammo_amount, x.tool_amount, x.fuel_atlas_amount as fuel_atlas_amount, x.food_atlas_amount as food_atlas_amount, x.ammo_atlas_amount as ammo_atlas_amount, x.tool_atlas_amount as tool_atlas_amount, y.fuel_price, y.food_price, y.ammo_price, y.tool_price, y.fuel_price_increase, y.food_price_increase, y.ammo_price_increase, y.tool_price_increase FROM r4_trade_volume x left outer join r4_prices y on x.date = y.date --group by 1,2,3,4,5,6,7,8,9,10,11,12,13 order by 1 desc

Query	Creator	Created
Getting Started	jh8nn	Aug 4, 2023
R4_prices copy	jh8nn	Aug 4, 2023

jh8nn