WITH all_swap_transactions as ( SELECT block_timestamp, tx_id, event_attributes:return_amount / event_attributes:offer_amount as swap_price -- LUNA divide bLUNA price FROM terra.msg_events --WHERE tx_id = '4D479F4E2695967156FDBAFB640965340250928397F57C17B33B0BEE38D59100' --WHERE tx_id = 'B8BCEDCE0F8456B51F4FFFD647268A43F3E7A95C8C1A3C963940ABC95F4992EB' WHERE event_attributes:to::string = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' AND event_attributes:offer_asset::string = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' --bLUNA AND event_attributes:ask_asset::string = 'uluna' --to LUNA AND swap_price < 2 --Prevent Outliers AND tx_status = 'SUCCEEDED' AND event_type = 'from_contract' UNION ALL SELECT block_timestamp, tx_id, msg_value:execute_msg:swap:belief_price::DOUBLE as swap_price FROM terra.msgs WHERE msg_value:contract::string = 'terra1jxazgm67et0ce260kvrpfv50acuushpjsz2y0p' AND msg_value:execute_msg:swap is not NULL AND tx_status = 'SUCCEEDED' --AND swap_price < 2 --Prevent Outliers ), bLUNA_prices_unfiltered as ( SELECT date(block_timestamp) as time_date, avg(swap_price) as average_swap_price FROM all_swap_transactions GROUP BY time_date ORDER BY time_date ASC ), bLUNA_prices_per_day as ( SELECT *, CASE WHEN average_swap_price >= 1 THEN 'a. Higher Than 1' WHEN average_swap_price < 1 AND average_swap_price >= 0.99 THEN 'b. Between 0.99 and 1.00' WHEN average_swap_price < 0.99 AND average_swap_price >= 0.98 THEN 'c. Between 0.98 and 0.99' WHEN average_swap_price < 0.98 AND average_swap_price >= 0.97 THEN 'd. Between 0.97 and 0.98' WHEN average_swap_price < 0.97 AND average_swap_price >= 0.96 THEN 'e. Between 0.96 and 0.97' WHEN average_swap_price < 0.96 AND average_swap_price >= 0.95 THEN 'f. Between 0.95 and 0.96' WHEN average_swap_price < 0.95 AND average_swap_price >= 0.94 THEN 'g.Between 0.94 and 0.95' WHEN average_swap_price < 0.94 THEN 'h. Lower Than 0.94' END as category FROM bLUNA_prices_unfiltered WHERE average_swap_price < 1.5 --Based on Coinhall, the highest was 1.3 for the bLUNA/LUNA ratio, so put 1.5 as safe margin AND average_swap_price != 0 ORDER BY time_date ASC ) SELECT category, count(category) FROM bLUNA_prices_per_day GROUP BY category

117. [Hard] Terrarbitage, Part 1