WITH transfers AS (

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date

,SUM(CASE WHEN contract_address IN ('0xff970a61a04b1ca14834a43f5de4533ebddb5cc8') AND TO_ADDRESS IN ('0x489ee077994b6658eafa855c308275ead8097c4a', '0x7f90122bf0700f9e7e1f688fe926940e8839f353', '0xc9b8a3fdecb9d5b218d02555a8baf332e5b740d5', '0x625e7708f30ca75bfd92586e17077590c60eb4cd', '0x805ba50001779ced4f59cff63aea527d12b94829', '0xba12222222228d8ba445958a75a0704d566bf2c8', '0xfa82f1ba00b0697227e2ad6c668abb4c50ca0b1f', '0xe4b2dfc82977dd2dce7e8d37895a6a8f50cbb4fb', '0x958cc92297e6f087f41a86125ba8e121f0fbecf2', '0xc9032419aa502fafa107775dca8b7d07575d9db5', '0xfcd8570ad81e6c77b8d252bebeba62ed980bd64d', '0xcf4d2994088a8cde52fb584fe29608b63ec063b2', '0xfe176a2b1e1f67250d2903b8d25f56c0dabcd6b2', '0xb88690461ddbab6f04dfad7df66b7725942feb9c', '0x04208f296039f482810b550ae0d68c3e1a5eb719', '0x59ee12714dfcfc300344f0256a4308c1c48e56ed', '0x283c41b726634fbd6b72aa22741b202db7e56aac', '0xa3af00a4ca8a11b840b1cb190d1c1d66da1546fc', '0xb42a054d950dafd872808b3c839fbb7afb86e14c', '0xc31e54c7a869b9fcbecc14363cf510d1c41fa443', '0x17c14d2c404d167802b16c450d3c99f88f2c4f4d', '0x2039f8c9cd32ba9cd2ea7e575d5b1abea93f7527', '0x50450351517117cb58189edba6bbad6284d45902', '0xd37af656abf91c7f548fffc0133175b5e4d3d5e6', '0x13398e27a21be1218b6900cbedf677571df42a48', '0x5151c83ad4e1c8c4ea0d1eaf91c246a8c6dab2a4', '0xad397a0472503b066ab4b311d66fa1f659f4cb61', '0x78727cc9e4bcbbceb15e3c4b7155f997fa365ce1', '0xbed2589fefae17d62a8a4fdac92fa5895cae90d2', '0xe4d9faddd9bca5d8393bee915dc56e916ab94d27', '0x7bb5e71fab18feba077b28355f20f5643ceece28', '0xa62ad78825e3a55a77823f00fe0050f567c1e4ee', '0xd7dc2b38a72ddfbf76a8d8f1776b83997d67a1ce', '0x8278591bbbbe7d86e783507e7f6320ffc9c021e5', '0x02c79a7e3d8a9cb773cd5f69be00a64f48308508', '0x99c2ce89030d53556c8ee4b57a8c51fb93760237', '0x7e5e4a3f855f19cc1a45b9eff1c8b2419036ce85', '0xc80956377cea73f64aaa3613d6692eabdf4fd4c8', '0x3396e2cb9e4f1eb4fc06028ab07a8db0ef63b486', '0x9340e3296121507318874ce9c04afb4492af0284', '0x6a58c68ff5c4e4d90eb6561449cc74a64f818da5', '0x34851ea13bde818b1efe26d31377906b47c9bbe2', '0x905dfcd5649217c42684f23958568e533c711aa3', '0x01de11cfd3a7261a954db411f02c86d8b826e5d2', '0xf81ebbc00b9bbc3a0b0cb1bc4e87ac157028698f', '0x8165

,SUM(CASE WHEN contract_address IN ('0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9') AND TO_ADDRESS IN ('0x489ee077994b6658eafa855c308275ead8097c4a', '0x7f90122bf0700f9e7e1f688fe926940e8839f353', '0xc9b8a3fdecb9d5b218d02555a8baf332e5b740d5', '0x625e7708f30ca75bfd92586e17077590c60eb4cd', '0x805ba50001779ced4f59cff63aea527d12b94829', '0xba12222222228d8ba445958a75a0704d566bf2c8', '0xfa82f1ba00b0697227e2ad6c668abb4c50ca0b1f', '0xe4b2dfc82977dd2dce7e8d37895a6a8f50cbb4fb', '0x958cc92297e6f087f41a86125ba8e121f0fbecf2', '0xc9032419aa502fafa107775dca8b7d07575d9db5', '0xfcd8570ad81e6c77b8d252bebeba62ed980bd64d', '0xcf4d2994088a8cde52fb584fe29608b63ec063b2', '0xfe176a2b1e1f67250d2903b8d25f56c0dabcd6b2', '0xb88690461ddbab6f04dfad7df66b7725942feb9c', '0x04208f296039f482810b550ae0d68c3e1a5eb719', '0x59ee12714dfcfc300344f0256a4308c1c48e56ed', '0x283c41b726634fbd6b72aa22741b202db7e56aac', '0xa3af00a4ca8a11b840b1cb190d1c1d66da1546fc', '0xb42a054d950dafd872808b3c839fbb7afb86e14c', '0xc31e54c7a869b9fcbecc14363cf510d1c41fa443', '0x17c14d2c404d167802b16c450d3c99f88f2c4f4d', '0x2039f8c9cd32ba9cd2ea7e575d5b1abea93f7527', '0x50450351517117cb58189edba6bbad6284d45902', '0xd37af656abf91c7f548fffc0133175b5e4d3d5e6', '0x13398e27a21be1218b6900cbedf677571df42a48', '0x5151c83ad4e1c8c4ea0d1eaf91c246a8c6dab2a4', '0xad397a0472503b066ab4b311d66fa1f659f4cb61', '0x78727cc9e4bcbbceb15e3c4b7155f997fa365ce1', '0xbed2589fefae17d62a8a4fdac92fa5895cae90d2', '0xe4d9faddd9bca5d8393bee915dc56e916ab94d27', '0x7bb5e71fab18feba077b28355f20f5643ceece28', '0xa62ad78825e3a55a77823f00fe0050f567c1e4ee', '0xd7dc2b38a72ddfbf76a8d8f1776b83997d67a1ce', '0x8278591bbbbe7d86e783507e7f6320ffc9c021e5', '0x02c79a7e3d8a9cb773cd5f69be00a64f48308508', '0x99c2ce89030d53556c8ee4b57a8c51fb93760237', '0x7e5e4a3f855f19cc1a45b9eff1c8b2419036ce85', '0xc80956377cea73f64aaa3613d6692eabdf4fd4c8', '0x3396e2cb9e4f1eb4fc06028ab07a8db0ef63b486', '0x9340e3296121507318874ce9c04afb4492af0284', '0x6a58c68ff5c4e4d90eb6561449cc74a64f818da5', '0x34851ea13bde818b1efe26d31377906b47c9bbe2', '0x905dfcd5649217c42684f23958568e533c711aa3', '0x01de11cfd3a7261a954db411f02c86d8b826e5d2', '0xf81ebbc00b9bbc3a0b0cb1bc4e87ac157028698f', '0x8165

,SUM(CASE WHEN contract_address IN ('0xda10009cbd5d07dd0cecc66161fc93d7c9000da1') AND TO_ADDRESS IN ('0x489ee077994b6658eafa855c308275ead8097c4a', '0x7f90122bf0700f9e7e1f688fe926940e8839f353', '0xc9b8a3fdecb9d5b218d02555a8baf332e5b740d5', '0x625e7708f30ca75bfd92586e17077590c60eb4cd', '0x805ba50001779ced4f59cff63aea527d12b94829', '0xba12222222228d8ba445958a75a0704d566bf2c8', '0xfa82f1ba00b0697227e2ad6c668abb4c50ca0b1f', '0xe4b2dfc82977dd2dce7e8d37895a6a8f50cbb4fb', '0x958cc92297e6f087f41a86125ba8e121f0fbecf2', '0xc9032419aa502fafa107775dca8b7d07575d9db5', '0xfcd8570ad81e6c77b8d252bebeba62ed980bd64d', '0xcf4d2994088a8cde52fb584fe29608b63ec063b2', '0xfe176a2b1e1f67250d2903b8d25f56c0dabcd6b2', '0xb88690461ddbab6f04dfad7df66b7725942feb9c', '0x04208f296039f482810b550ae0d68c3e1a5eb719', '0x59ee12714dfcfc300344f0256a4308c1c48e56ed', '0x283c41b726634fbd6b72aa22741b202db7e56aac', '0xa3af00a4ca8a11b840b1cb190d1c1d66da1546fc', '0xb42a054d950dafd872808b3c839fbb7afb86e14c', '0xc31e54c7a869b9fcbecc14363cf510d1c41fa443', '0x17c14d2c404d167802b16c450d3c99f88f2c4f4d', '0x2039f8c9cd32ba9cd2ea7e575d5b1abea93f7527', '0x50450351517117cb58189edba6bbad6284d45902', '0xd37af656abf91c7f548fffc0133175b5e4d3d5e6', '0x13398e27a21be1218b6900cbedf677571df42a48', '0x5151c83ad4e1c8c4ea0d1eaf91c246a8c6dab2a4', '0xad397a0472503b066ab4b311d66fa1f659f4cb61', '0x78727cc9e4bcbbceb15e3c4b7155f997fa365ce1', '0xbed2589fefae17d62a8a4fdac92fa5895cae90d2', '0xe4d9faddd9bca5d8393bee915dc56e916ab94d27', '0x7bb5e71fab18feba077b28355f20f5643ceece28', '0xa62ad78825e3a55a77823f00fe0050f567c1e4ee', '0xd7dc2b38a72ddfbf76a8d8f1776b83997d67a1ce', '0x8278591bbbbe7d86e783507e7f6320ffc9c021e5', '0x02c79a7e3d8a9cb773cd5f69be00a64f48308508', '0x99c2ce89030d53556c8ee4b57a8c51fb93760237', '0x7e5e4a3f855f19cc1a45b9eff1c8b2419036ce85', '0xc80956377cea73f64aaa3613d6692eabdf4fd4c8', '0x3396e2cb9e4f1eb4fc06028ab07a8db0ef63b486', '0x9340e3296121507318874ce9c04afb4492af0284', '0x6a58c68ff5c4e4d90eb6561449cc74a64f818da5', '0x34851ea13bde818b1efe26d31377906b47c9bbe2', '0x905dfcd5649217c42684f23958568e533c711aa3', '0x01de11cfd3a7261a954db411f02c86d8b826e5d2', '0xf81ebbc00b9bbc3a0b0cb1bc4e87ac157028698f', '0x8165

,SUM(CASE WHEN contract_address IN ('0x17fc002b466eec40dae837fc4be5c67993ddbd6f') AND TO_ADDRESS IN ('0x489ee077994b6658eafa855c308275ead8097c4a', '0x7f90122bf0700f9e7e1f688fe926940e8839f353', '0xc9b8a3fdecb9d5b218d02555a8baf332e5b740d5', '0x625e7708f30ca75bfd92586e17077590c60eb4cd', '0x805ba50001779ced4f59cff63aea527d12b94829', '0xba12222222228d8ba445958a75a0704d566bf2c8', '0xfa82f1ba00b0697227e2ad6c668abb4c50ca0b1f', '0xe4b2dfc82977dd2dce7e8d37895a6a8f50cbb4fb', '0x958cc92297e6f087f41a86125ba8e121f0fbecf2', '0xc9032419aa502fafa107775dca8b7d07575d9db5', '0xfcd8570ad81e6c77b8d252bebeba62ed980bd64d', '0xcf4d2994088a8cde52fb584fe29608b63ec063b2', '0xfe176a2b1e1f67250d2903b8d25f56c0dabcd6b2', '0xb88690461ddbab6f04dfad7df66b7725942feb9c', '0x04208f296039f482810b550ae0d68c3e1a5eb719', '0x59ee12714dfcfc300344f0256a4308c1c48e56ed', '0x283c41b726634fbd6b72aa22741b202db7e56aac', '0xa3af00a4ca8a11b840b1cb190d1c1d66da1546fc', '0xb42a054d950dafd872808b3c839fbb7afb86e14c', '0xc31e54c7a869b9fcbecc14363cf510d1c41fa443', '0x17c14d2c404d167802b16c450d3c99f88f2c4f4d', '0x2039f8c9cd32ba9cd2ea7e575d5b1abea93f7527', '0x50450351517117cb58189edba6bbad6284d45902', '0xd37af656abf91c7f548fffc0133175b5e4d3d5e6', '0x13398e27a21be1218b6900cbedf677571df42a48', '0x5151c83ad4e1c8c4ea0d1eaf91c246a8c6dab2a4', '0xad397a0472503b066ab4b311d66fa1f659f4cb61', '0x78727cc9e4bcbbceb15e3c4b7155f997fa365ce1', '0xbed2589fefae17d62a8a4fdac92fa5895cae90d2', '0xe4d9faddd9bca5d8393bee915dc56e916ab94d27', '0x7bb5e71fab18feba077b28355f20f5643ceece28', '0xa62ad78825e3a55a77823f00fe0050f567c1e4ee', '0xd7dc2b38a72ddfbf76a8d8f1776b83997d67a1ce', '0x8278591bbbbe7d86e783507e7f6320ffc9c021e5', '0x02c79a7e3d8a9cb773cd5f69be00a64f48308508', '0x99c2ce89030d53556c8ee4b57a8c51fb93760237', '0x7e5e4a3f855f19cc1a45b9eff1c8b2419036ce85', '0xc80956377cea73f64aaa3613d6692eabdf4fd4c8', '0x3396e2cb9e4f1eb4fc06028ab07a8db0ef63b486', '0x9340e3296121507318874ce9c04afb4492af0284', '0x6a58c68ff5c4e4d90eb6561449cc74a64f818da5', '0x34851ea13bde818b1efe26d31377906b47c9bbe2', '0x905dfcd5649217c42684f23958568e533c711aa3', '0x01de11cfd3a7261a954db411f02c86d8b826e5d2', '0xf81ebbc00b9bbc3a0b0cb1bc4e87ac157028698f', '0x8165

FROM arbitrum.core.fact_token_transfers

WHERE contract_address IN ('0xff970a61a04b1ca14834a43f5de4533ebddb5cc8'

,'0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9'

,'0xda10009cbd5d07dd0cecc66161fc93d7c9000da1'

,'0x17fc002b466eec40dae837fc4be5c67993ddbd6f'

)

GROUP BY 1

)

*

,(usdc_tvl / total_tvl) * 100 AS usdc_dominance

,((total_tvl - lag(total_tvl, 7) over (order by date ASC)) / lag(total_tvl, 7) over (order by date ASC)) *100 AS seven_d_chng

,((total_tvl - lag(total_tvl, 30) over (order by date ASC)) / lag(total_tvl, 30) over (order by date ASC)) *100 AS thirty_d_chng

FROM (

SELECT

*

,SUM(usdt_netflow) OVER (order by date) AS usdt_tvl

,SUM(usdc_netflow) OVER (order by date) AS usdc_tvl

,SUM(frax_netflow) OVER (order by date) AS frax_tvl

,SUM(dai_netflow) OVER (order by date) AS dai_tvl

,SUM(usdc_netflow + usdt_netflow + dai_netflow + frax_netflow) OVER (order by date) AS total_tvl

,usdc_netflow + usdt_netflow + dai_netflow + frax_netflow AS total_netflow

FROM (

SELECT

date

,SUM(to_usdt + from_usdt) AS usdt_netflow