MLDZMNMost active users on Struct finance by volume
    Updated 2023-11-07
    WITH main_vault AS
    (
    SELECT
    to_timestamp(decoded_log:endTimeTranche) as tranche_end_time,
    to_timestamp(decoded_log:startTimeTranche) as tranche_start_time,
    to_timestamp(decoded_log:startTimeDeposit) as deposit_start_time,
    decoded_log:productAddress as vault_address,
    CONCAT('%',decoded_log:fixedRate / POW(10,4)) as fixed_rate
    FROM
    avalanche.core.ez_decoded_event_logs
    WHERE
    contract_address = lower('0x46f8765781Ac36E5e8F9937658fA311aF9D735d7')
    AND
    event_name ilike '%Product%'
    AND
    tx_status = 'SUCCESS'
    ),

    raw_transactions_tab AS
    (
    SELECT
    CASE WHEN decoded_log:_tranche = '0' then 'Fixed' ELSE 'Variable' END as inv_type,
    'Deposit' AS tx_type,
    b.block_timestamp as tx_timestamp,
    a.symbol,
    CASE WHEN a.SYMBOL = 'USDC' then a.raw_amount / pow(10, 6) ELSE a.raw_amount / pow(10, 7) END as raw_amount, a.amount_usd,
    a.from_address as user_address,
    a.tx_hash
    FROM
    avalanche.core.ez_token_transfers a join avalanche.core.ez_decoded_event_logs b on (a.tx_hash = b.tx_hash)
    WHERE
    event_name = 'Deposited' AND a.to_address IN (SELECT vault_Address FROM main_vault)

    UNION ALL

    SELECT
    Run a query to Download Data