observerdqUntitled Query
    Updated 2022-03-27
    select * from terra.swaps order by block_timestamp desc limit 100;
    select * from terra.swaps where block_timestamp <= '2022-03-30 00:00:00.000' order by block_timestamp desc limit 100;

    /* Q1 - oracle update到底是个啥频度?看文档显示是5个block,但从数据库来看,并没有更新这么快。这事重要么?
    Q2 - 1月底有proposal提请修改update为36 blocks,所以在36个blocks内都不会变价格?所以其实不应该看voteperiod,而是应该数36个block?
    Q3 - 任意挑了某个时间段的100笔交易,大量的都是极低金额、来自同一个人在我看来属于无意义交易的范畴。这是为何?官方在造数据喽? 03/31
    各类交易的占比 抽取了03-29 12:00左右的100笔交易,发现price0_usd很久都没变(超过了36个block)。奇怪。此外,原本预计兑换价格会一路下滑,但实际上是有波动的,所以需要研究交易的占比。
    很多10000的交易都是咋回事,很假啊。
    直击底层,swap交易原理!金额分布等等。
    这个swap和DEX,哪个量大?
    */

    select block_id, msg_index, block_timestamp, token_0_currency,
    case when token_0_currency = 'LUNA' then token_1_amount/token_0_amount else token_0_amount/token_1_amount end as ratio_mint_ust,
    trader,
    offer_amount, offer_amount_usd,
    token_0_amount, token_0_amount_usd,
    token_1_amount, token_1_amount_usd,
    price0_usd, price1_usd,
    swap_fee_amount, swap_fee_amount_usd
    from terra.swaps where tx_status = 'SUCCEEDED' /*and swap_pair = 'LUNA to UST'*/
    and ((token_0_currency = 'LUNA' and token_1_currency = 'UST') or (token_0_currency = 'UST' and token_1_currency = 'LUNA'))
    order by block_timestamp desc limit 100;

    select date_part(day from block_timestamp),
    count(distinct trader), count(*), sum(offer_amount_usd)
    from terra.swaps where tx_status = 'SUCCEEDED' and swap_pair = 'LUNA to UST' and block_timestamp >= '2022-03-01 10:42:38.803'
    group by date_part(day from block_timestamp)
    order by date_part(day from block_timestamp);

    select trader, count(*), sum(offer_amount_usd) from terra.swaps
    where tx_status = 'SUCCEEDED' and swap_pair = 'LUNA to UST' and block_timestamp >= '2022-03-01 10:42:38.803'
    group by trader
    order by sum(offer_amount_usd) DESC
    limit 200;
    Run a query to Download Data