with re as (select msg_value,

block_timestamp as dates,

msg_value:execute_msg:ledger_proxy:msg:execute_order:order:order:maker_asset:info:nft:contract_addr::string as nft_maker,

msg_value:execute_msg:ledger_proxy:msg:execute_order:order:order:taker_asset:info:nft:contract_addr::string as nft_taker, case

when nft_maker is not null then nft_maker

when nft_maker is null then nft_taker end as nft_collections,

(msg_value:execute_msg:ledger_proxy:msg:execute_order:order:order:taker_asset:amount::int)/1000000 as amount

from terra.msgs

where msg_value:contract::string = 'terra1eek0ymmhyzja60830xhzm7k7jkrk99a60q2z2t'

and date(block_timestamp) >= CURRENT_DATE - 7

and tx_status = 'SUCCEEDED'

and (msg_value:execute_msg:execute_order is not null or msg_value:execute_msg:ledger_proxy:msg:execute_order is not null)

)

select

date_trunc('hour', dates) as dt, nft_collections, case

when nft_collections = 'terra103z9cnqm8psy0nyxqtugg6m7xnwvlkqdzm4s4k' then 'Galactic_Punks'

when nft_collections = 'terra1trn7mhgc9e2wfkm5mhr65p3eu7a2lc526uwny2' then 'Lunabulls'

else null end as NFT,

min(amount) as floor_sale, max(amount) as ceiling_sale, avg(amount) as average_sale,

sum(round(amount,2)) as Total_sale_Volume, SUM(Total_sale_Volume) OVER(ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales

from re

where nft_collections in ('terra103z9cnqm8psy0nyxqtugg6m7xnwvlkqdzm4s4k', 'terra1trn7mhgc9e2wfkm5mhr65p3eu7a2lc526uwny2') and amount > 0.1

group by 1,2

order by 1 desc