flipside

    AXL-1Y
    DrsimonAXL-1Y
    Updated 2023-03-16
    --code 1
    SELECT
    'AXL Staking' as source,
    date_trunc('YEAR', s.block_timestamp) as day,
    sum(CASE when s.action LIKE 'delegate' then s.amount/ power(10, 6) end) as delegation_volume,
    sum(CASE when s.action LIKE 'undelegate' then s.amount/ power(10, 6) end) as undelegation_volume,
    sum(CASE when s.action LIKE 'delegate' then s.amount/ power(10, 6) end) - sum(CASE when s.action LIKE 'undelegate' then s.amount/ power(10, 6) end) as net_delegation_volume,
    p.median_price as MEDIAN_PRICE,
    'AXL' as token
    FROM axelar.core.fact_staking s
    INNER JOIN (
    SELECT
    date_trunc('YEAR', recorded_at) as day,
    AVG(price) as median_price
    FROM osmosis.core.dim_prices
    WHERE symbol LIKE 'axl' AND day < '2023-03-16'
    GROUP BY 1
    ) p ON date_trunc('YEAR', s.block_timestamp) = p.day
    WHERE s.action in ('delegate', 'undelegate') AND day < '2023-03-16'
    --AND s.currency IN ('uaxl', 'uatoms', 'uiris')
    GROUP BY 1,2,6

    UNION

    SELECT
    'ATOM Staking' as source,
    date_trunc('YEAR', s.block_timestamp) as day,
    sum(CASE when s.action LIKE 'delegate' then s.amount/ power(10, 6) end) as delegation_volume,
    sum(CASE when s.action LIKE 'undelegate' then s.amount/ power(10, 6) end) as undelegation_volume,
    sum(CASE when s.action LIKE 'delegate' then s.amount/ power(10, 6) end) - sum(CASE when s.action LIKE 'undelegate' then s.amount/ power(10, 6) end) as net_delegation_volume,
    p.median_price as MEDIAN_PRICE,
    'ATOM' as token
    FROM axelar.core.fact_staking s
    INNER JOIN (
    SELECT
    date_trunc('YEAR', recorded_at) as day,
    Run a query to Download Data