glass_blossomsTVL on mirror
    Updated 2022-03-19
    WITH collateral_deposited as (
    SELECT DATE(block_timestamp) as date,
    CASE
    WHEN msg_value:execute_msg:deposit:collateral:info:native_token IS NOT NULL THEN msg_value:execute_msg:deposit:collateral:info:native_token:denom::string
    WHEN msg_value:execute_msg:deposit:collateral:info:token IS NOT NULL THEN msg_value:execute_msg:deposit:collateral:info:token:contract_addr::string
    END as currency,
    SUM(msg_value:execute_msg:deposit:collateral:amount::float / POW(10, 6)) as deposited
    FROM terra.msgs
    WHERE msg_value:contract::string = 'terra1wfz7h3aqf4cjmjcvc6s8lxdhh7k30nkczyf0mj'
    AND msg_value:execute_msg:deposit:collateral IS NOT NULL
    AND tx_status = 'SUCCEEDED'
    GROUP BY 1, 2
    ORDER BY 1
    ),
    collateral_deposited_total as (
    SELECT date,
    SUM(deposited * (
    CASE
    WHEN d.currency = 'uluna' THEN p.price
    WHEN a.price IS NOT NULL THEN a.price
    ELSE 1
    END
    )) as total_deposited
    FROM collateral_deposited d
    LEFT JOIN (
    SELECT msg_value:execute_msg:feed_price:prices[0][0]::string as asset_address, AVG(msg_value:execute_msg:feed_price:prices[0][1]::float) as price
    FROM terra.msgs
    WHERE msg_value:contract::string = 'terra1t6xe0txzywdg85n6k8c960cuwgh6l8esw6lau9'
    AND msg_value:execute_msg:feed_price IS NOT NULL
    AND DATE(block_timestamp) > CURRENT_DATE() - 2
    GROUP BY 1
    ) a
    ON a.asset_address = currency
    LEFT JOIN (
    SELECT currency, price_usd::float as price
    FROM terra.oracle_prices
    Run a query to Download Data