Select

block_timestamp,

tx_id,--:"1_contract_address"

Event_attributes:"to" as UserAddress,

Event_attributes:"offer_amount"::float/1e6 as USTUsedForPurchase,

Event_attributes:"offer_asset" as CurrencyForPurchase,

Event_attributes:"return_amount"::float/1e6 as HaloPurchased,

USTUsedForPurchase/HaloPurchased as Halo_Price_In_UST

from terra.msg_events

where tx_status = 'SUCCEEDED'

--AND tx_id = 'F36F93C0F4DF8284ECAC8C56D07FDA9E6DA08BA2FCD6CBD783A6F9C3DA12BB07'

and Event_attributes:"from" = 'terra1hhpgcp2stvzx952zfxtxg4dhgf60yfzchesj3e'

AND Event_attributes:"0_contract_address" = 'terra1hhpgcp2stvzx952zfxtxg4dhgf60yfzchesj3e'

AND Event_attributes:"1_contract_address" = 'terra1w8kvd6cqpsthupsk4l0clwnmek4l3zr7c84kwq'

AND Event_index = 6)

SELECT

distinct UserAddress,

sum(HaloPurchased) over (Partition by UserAddress) as Total_Halo_Purchased,

SUM(USTUsedForPurchase) over (Partition by UserAddress) as Total_UST_UsedToPurchase,

Total_UST_UsedToPurchase/Total_Halo_Purchased as AvgBuyPrice

from AngelLBP