alleriaLevana Marathon Hunters: Legendary Meteors earned and Total UST Deposited
    Updated 2021-11-14
    WITH
    meteor_hour as (
    SELECT
    block_timestamp as transaction_time,
    minute(block_timestamp) as transaction_minute,
    CASE
    WHEN transaction_time < TO_TIMESTAMP('2021-11-08T06:00:00Z') THEN 1
    ELSE TIMEDIFF(hour, TO_TIMESTAMP('2021-11-08T06:00:00Z'), transaction_time) + 1
    END as meteor_shower_hour,
    tx_id,
    msg_value:amount[0]:amount / POW(10,6) as UST_donated,
    msg_value:from_address::string as wallet_address
    FROM terra.msgs
    WHERE msg_value:to_address::string = 'terra1dax9mddsycvzkc429wwy494vhzhggw3d5594rt'
    AND tx_status = 'SUCCEEDED'
    AND meteor_shower_hour <= 44
    ORDER BY meteor_shower_hour ASC
    ),

    per_hour_address as (
    SELECT
    meteor_shower_hour,
    wallet_address,
    SUM(UST_donated) as total_UST_donated,
    COUNT(tx_id) as number_tx_done_per_shower
    FROM meteor_hour
    GROUP BY meteor_shower_hour, wallet_address
    ),

    hall_of_fame as (
    SELECT
    wallet_address,
    COUNT(meteor_shower_hour) as meteor_showers_involved,
    SUM(total_UST_donated) as all_shower_UST_donated,
    SUM(number_tx_done_per_shower) as total_tx_done
    FROM per_hour_address
    Run a query to Download Data