with table1 as (with tab1 as (select date_trunc('day',block_timestamp) as date, sum(event_inputs:value) as deposit_volume, contract_address, case when contract_address='0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' then 'sUSD' when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' then 'USDC' --6 when contract_address='0xfe8b128ba8c78aabc59d4c64cee7ff28e9379921' then 'BAL' when contract_address='0x8700daec35af8ff88c16bdf0418774cb3d7599b4' then 'SNX' when contract_address='0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'DAI' when contract_address='0x4200000000000000000000000000000000000042' then 'OP' when contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then 'USDT' --6 when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then 'WBTC' --8 end as token, case when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' or contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then deposit_volume/pow(10,6) when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then deposit_volume/pow(10,8) else deposit_volume/pow(10,18) end as Deposit_vol from optimism.core.fact_event_logs where (ORIGIN_FUNCTION_SIGNATURE='0xe8eda9df' and origin_to_address='0x8fd4af47e4e63d1d2d45582c3286b4bd9bb95dfe' and event_name='Transfer' and event_inputs:from=origin_from_address) -- deposit -------------------------- group by 1,3 order by 1), tab2 as (select date_trunc('day',hour) as date, avg(price) as avg_price, token_address from optimism.core.fact_hourly_token_prices group by 1,3 order by 1) select tab1.date as date_, sum(Deposit_vol*avg_price) as deposit_volume, 'Deposit' as event from tab1 left join tab2 on tab1.date=tab2.date and tab1.contract_address=tab2.token_address group by 1 order by 1), table2 as (with tab1 as (select date_trunc('day',block_timestamp) as date, sum(event_inputs:value) as withdraw_volume, contract_address, case when contract_address='0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' then 'sUSD' when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' then 'USDC' --6 when contract_address='0xfe8b128ba8c78aabc59d4c64cee7ff28e9379921' then 'BAL' when contract_address='0x8700daec35af8ff88c16bdf0418774cb3d7599b4' then 'SNX' when contract_address='0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'DAI' when contract_address='0x4200000000000000000000000000000000000042' then 'OP' when contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then 'USDT' --6 when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then 'WBTC' --8 end as token, case when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' or contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then withdraw_volume/pow(10,6) when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then withdraw_volume/pow(10,8) else withdraw_volume/pow(10,18) end as withdraw_vol from optimism.core.fact_event_logs where (ORIGIN_FUNCTION_SIGNATURE='0x69328dec' and origin_to_address='0x8fd4af47e4e63d1d2d45582c3286b4bd9bb95dfe' and event_name='Transfer' and event_inputs:to=origin_from_address) -- withdraw group by 1,3 order by 1), tab2 as (select date_trunc('day',hour) as date, avg(price) as avg_price, token_address from optimism.core.fact_hourly_token_prices group by 1,3 order by 1) select tab1.date as date_, sum(withdraw_vol*avg_price) as withdraw_volume, 'Withdraw' as event from tab1 left join tab2 on tab1.date=tab2.date and tab1.contract_address=tab2.token_address group by 1 order by 1), table3 as (with tab1 as (select date_trunc('day',block_timestamp) as date, sum(event_inputs:value) as borrow_volume, contract_address, case when contract_address='0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' then 'sUSD' when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' then 'USDC' --6 when contract_address='0xfe8b128ba8c78aabc59d4c64cee7ff28e9379921' then 'BAL' when contract_address='0x8700daec35af8ff88c16bdf0418774cb3d7599b4' then 'SNX' when contract_address='0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'DAI' when contract_address='0x4200000000000000000000000000000000000042' then 'OP' when contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then 'USDT' --6 when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then 'WBTC' --8 end as token, case when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' or contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then borrow_volume/pow(10,6) when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then borrow_volume/pow(10,8) else borrow_volume/pow(10,18) end as borrow_vol from optimism.core.fact_event_logs where (ORIGIN_FUNCTION_SIGNATURE='0xa415bcad' and origin_to_address='0x8fd4af47e4e63d1d2d45582c3286b4bd9bb95dfe' and event_name='Transfer' and event_inputs:to=origin_from_address) -- Borrow and contract_address<>'0x62bbfaef552522be2bda7f69cc5b2c36c1879600' and contract_address<>'0xbabdd3e2231990b1f47844536e19b2f1cc1d5077' and contract_address<>'0x5c4acfcba420f8a0e14b7aada3d8726452642fbb' and contract_address<>'0xb271973b367e50fcde5ee5e426944c37045dd0bf' and contract_address<>'0x49e03c399f0f84083d6f6549383fc80d11701bd4' and contract_address<>'0x9dd559b1d7454979b1699d710885ba5c658277e3' and contract_address<>'0xb1afe7c8d6d94e8ef04ab3c99848a3b21a33d9ef' and contract_address<>'0xc0031304549e494f1f48a9ac568242b1a6ca1804' group by 1,3 order by 1), tab2 as (select date_trunc('day',hour) as date, avg(price) as avg_price, token_address from optimism.core.fact_hourly_token_prices group by 1,3 order by 1) select tab1.date as date_, -sum(borrow_vol*avg_price) as borrow_volume, 'Borrow' as event from tab1 left join tab2 on tab1.date=tab2.date and tab1.contract_address=tab2.token_address group by 1 order by 1), table4 as (with tab1 as (select date_trunc('day',block_timestamp) as date, sum(event_inputs:value) as repay_volume, contract_address, case when contract_address='0x8c6f28f2f1a3c87f0f938b96d27520d9751ec8d9' then 'sUSD' when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' then 'USDC' --6 when contract_address='0xfe8b128ba8c78aabc59d4c64cee7ff28e9379921' then 'BAL' when contract_address='0x8700daec35af8ff88c16bdf0418774cb3d7599b4' then 'SNX' when contract_address='0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'DAI' when contract_address='0x4200000000000000000000000000000000000042' then 'OP' when contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then 'USDT' --6 when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then 'WBTC' --8 end as token, case when contract_address='0x7f5c764cbc14f9669b88837ca1490cca17c31607' or contract_address='0x94b008aa00579c1307b0ef2c499ad98a8ce58e58' then repay_volume/pow(10,6) when contract_address='0x68f180fcce6836688e9084f035309e29bf0a2095' then repay_volume/pow(10,8) else repay_volume/pow(10,18) end as repay_vol from optimism.core.fact_event_logs where (ORIGIN_FUNCTION_SIGNATURE='0x573ade81' and origin_to_address='0x8fd4af47e4e63d1d2d45582c3286b4bd9bb95dfe' and event_name='Transfer' and event_inputs:from=origin_from_address) -- Repay and contract_address<>'0x62bbfaef552522be2bda7f69cc5b2c36c1879600' and contract_address<>'0xbabdd3e2231990b1f47844536e19b2f1cc1d5077' and contract_address<>'0x5c4acfcba420f8a0e14b7aada3d8726452642fbb' and contract_address<>'0xb271973b367e50fcde5ee5e426944c37045dd0bf' and contract_address<>'0x49e03c399f0f84083d6f6549383fc80d11701bd4' and contract_address<>'0x9dd559b1d7454979b1699d710885ba5c658277e3' and contract_address<>'0xb1afe7c8d6d94e8ef04ab3c99848a3b21a33d9ef' and contract_address<>'0xc0031304549e494f1f48a9ac568242b1a6ca1804' group by 1,3 order by 1), tab2 as (select date_trunc('day',hour) as date, avg(price) as avg_price, token_address from optimism.core.fact_hourly_token_prices group by 1,3 order by 1) select tab1.date as date_, -sum(repay_vol*avg_price) as repay_volume, 'Repay' as event from tab1 left join tab2 on tab1.date=tab2.date and tab1.contract_address=tab2.token_address group by 1 order by 1) select * from table1 union all select * from table2 union all select * from table3 union all select * from table4

Analysis of Granary Finance on Optimism