-- forked from theericstone-pine / orca-trades-5bp copy @ https://flipsidecrypto.xyz/theericstone-pine/q/orca-trades-5-bp-mjcNbS -- forked from jarry / orca-trades-5bp @ https://flipsidecrypto.xyz/jarry/q/2023-05-29-11-57-pm-uC39UA with raw_trades as ( select block_id, block_timestamp, date_trunc("minute", block_timestamp) as minute, tx_id, trader, 'CPI' as type, case when payload[i + 1]:parsed:info:destination = '9RfZwn2Prux6QesG1Noo4HzMEBv3rPndJ2bN2Wwd6a7p' then -1 else 1 end as direction, case when direction < 0 then payload[i + 1]:parsed:info:amount / 1e9 else payload[i + 2]:parsed:info:amount / 1e9 end as sol_amount, case when direction < 0 then payload[i + 2]:parsed:info:amount / 1e6 else payload[i + 1]:parsed:info:amount / 1e6 end as usdc_amount, usdc_amount * 5 / 10000. as fee, case when direction < 0 then payload[i + 1]:parsed:info:amount / 1e9 else payload[i + 1]:parsed:info:amount / 1e6 end as amount_in, case when direction < 0 then payload[i + 2]:parsed:info:amount / 1e6 else payload[i + 2]:parsed:info:amount / 1e9 end as amount_out, case when direction < 0 then amount_out / amount_in else amount_in / amount_out end as price, price as fair from ( SELECT e.block_id, e.block_timestamp, e.tx_id, e.signers[0] as trader, i.index as i, i.this as payload FROM solana.core.fact_events e, lateral flatten(input => e.inner_instruction:instructions) i where i.value:programId = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc' and e.succeeded and substr(i.value:data, 0, 3) = '59p' and e.block_timestamp > {{start_date}} and e.block_timestamp < {{end_date}} and i.value:accounts[2] = '7qbRF6YsyGuLUVs6Y1q64bdVrfe4ZcUUz1JRdoVNUJnm' ) as swaps where payload[i + 1]:program = 'spl-token' and payload[i + 2]:program = 'spl-token' and payload[i + 1]:parsed: type = 'transfer' and payload[i + 2]:parsed: type = 'transfer' and amount_in != '0' and amount_out != '0' and payload[i + 2]:parsed:info:authority = '7qbRF6YsyGuLUVs6Y1q64bdVrfe4ZcUUz1JRdoVNUJnm' UNION select block_id, block_timestamp, date_trunc("minute", block_timestamp) as minute, tx_id, signers[0] as trader, 'RAW' as type, case when inner_instruction:instructions[0]:parsed:info:destination = '9RfZwn2Prux6QesG1Noo4HzMEBv3rPndJ2bN2Wwd6a7p' then -1 else 1 end as direction, case when direction < 0 then inner_instruction:instructions[0]:parsed:info:amount / 1e9 else inner_instruction:instructions[1]:parsed:info:amount / 1e9 end as sol_amount, case when direction < 0 then inner_instruction:instructions[1]:parsed:info:amount / 1e6 else inner_instruction:instructions[0]:parsed:info:amount / 1e6 end as usdc_amount, usdc_amount * 5 / 10000. as fee, case when direction < 0 then inner_instruction:instructions[0]:parsed:info:amount / 1e9 else inner_instruction:instructions[0]:parsed:info:amount / 1e6 end as amount_in, case when direction < 0 then inner_instruction:instructions[1]:parsed:info:amount / 1e6 else inner_instruction:instructions[1]:parsed:info:amount / 1e9 end as amount_out, case when direction < 0 then amount_out / amount_in else amount_in / amount_out end as price, price as fair from solana.core.fact_events where program_id = 'whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc' and succeeded and substr(instruction:data, 0, 3) = '59p' and inner_instruction:instructions[0]:program = 'spl-token' and inner_instruction:instructions[1]:program = 'spl-token' and inner_instruction:instructions[0]:parsed: type = 'transfer' and inner_instruction:instructions[1]:parsed: type = 'transfer' and amount_in != '0' and amount_out != '0' and inner_instruction:instructions[1]:parsed:info:authority = '7qbRF6YsyGuLUVs6Y1q64bdVrfe4ZcUUz1JRdoVNUJnm' and block_timestamp > {{start_date}} and block_timestamp < {{end_date}} ), timestamps as ( SELECT DATEADD(MIN, - seq, to_timestamp({{end_date}})) AS timestamp FROM LATERAL ( SELECT ROW_NUMBER() OVER ( ORDER BY 1 ) - 1 AS seq FROM TABLE (GENERATOR(ROWCOUNT => 1440 * 365)) ) s WHERE timestamp >= to_timestamp({{start_date}}) order by timestamp ), raw_mark_prices as ( SELECT t.timestamp, avg(fair) as fair from timestamps t LEFT JOIN raw_trades r on t.timestamp = r.minute group by timestamp order by timestamp ), mark_prices as ( SELECT timestamp, coalesce( fair, AVG(fair) OVER ( ORDER BY timestamp ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) ) AS mark_price FROM raw_mark_prices WHERE fair is not NULL order by timestamp ), markouts as ( select r.*, mp.mark_price, - r.direction * (mp.mark_price - r.price) * r.sol_amount as markout from raw_trades r INNER JOIN mark_prices mp on r.minute = DATEADD( MIN, - {{markout_period_in_minutes}}, mp.timestamp ) ), hourly_data as ( select date_trunc("H", minute) as hour, avg(mark_price) as price, sum(usdc_amount) as total_volume, sum(markout) as total_markout from markouts group by hour order by hour ) select DISTINCT hour, price, ((price / FIRST_VALUE(price) OVER (ORDER BY hour)) - 1) * 100 as price_pct_change, total_volume as volume_by_hour, sum(total_volume) over ( order by hour ) as cumulative_volume, sum(total_markout) over ( order by hour ) as cumulative_markout from hourly_data;

hello with parameters2eee