flipside

    2024-01-26 03:41 AM
    onchaindetective2024-01-26 03:41 AM
    Updated 2024-01-26
    WITH
    tokenList AS (
    SELECT tokenSymbol, l1Address, starknetAddressWith0s,starknetAddress, decimals
    FROM (
    VALUES('ETH', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7','0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7', 18)
    , ('BTC', '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599', '0x03fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac', '0x3fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac', 8)
    , ('DAI', '0x6b175474e89094c44da98b954eedeac495271d0f', '0x00da114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3', '0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3', 18)
    , ('USDT', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0x068f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8', '0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8', 6)
    , ('USDC', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0x053c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8', '0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8', 6)
    , ('wstETH', '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0', '0x042b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2', '0x42b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2', 18)
    -- , ('LORDS', '0x686f2404e77Ab0d9070a46cdfb0B7feCDD2318b0', '0x0124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49', '0x124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49', 18)

    ) AS t(tokenSymbol, l1Address, starknetAddressWith0s, starknetAddress, decimals)
    )

    , prices AS (
    SELECT
    DATE_TRUNC('HOUR', HOUR) AS HOUR
    , TOKEN_ADDRESS
    , AVG(price) AS price
    FROM crosschain.price.fact_hourly_token_prices
    WHERE BLOCKCHAIN = 'ethereum'
    AND HOUR > '2023-11-29'
    AND TOKEN_ADDRESS IN (
    '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --WETH18DECIMALS
    , '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' --USDC6DECIMALS
    , '0x6b175474e89094c44da98b954eedeac495271d0f' --DAI18DECIMALS
    , '0xdac17f958d2ee523a2206206994597c13d831ec7' --USDT6DECIMALS
    , '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' --WBTC8DECIMALS
    , '0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0' --LORDS18DECIMALS
    , '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0' --wstETH18DECIMALS
    )
    GROUP BY 1,2
    )

    , pairList AS (
    Last run: about 1 year ago
    PAIRADDRESS
    PAIRADDRESSWITH0S
    TOKEN0SYMBOL
    TOKEN0ADDRESS
    TOKEN0L1ADDRESS
    TOKEN0DECIMALS
    TOKEN1SYMBOL
    TOKEN1ADDRESS
    TOKEN1L1ADDRESS
    TOKEN1DECIMALS
    NUMBEROFPAIRS
    PAIRCREATEDDATE
    HASH
    1
    0x6a6171b9a74643a17a7ad7040f669f570d9c98ad04b05c7418bdc99c686860e
    		0x06a6171b9a74643a17a7ad7040f669f570d9c98ad04b05c7418bdc99c686860eUSDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486USDT
    0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8
    		0xdac17f958d2ee523a2206206994597c13d831ec76
    13
    		2023-12-15 17:43:24.0000x00c4adb0bb23312caf3ee7516b7d2ab77249399038ff997c7e1969c8be7708e0
    2
    0x2503c0dde2488efa3f4fd0d4cbf16924e75c6c69365d73fb32968d25eb58233
    		0x02503c0dde2488efa3f4fd0d4cbf16924e75c6c69365d73fb32968d25eb58233USDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486USDT
    0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8
    		0xdac17f958d2ee523a2206206994597c13d831ec76
    34
    		2023-12-29 11:26:19.0000x005f0a670443856b5f718d365b7b1e0f799c6a5b2d02ebcaa50da79d60633c61
    3
    0x9204e2a9ca0c2ec431c506df58ca7a5ee71c8da531972295e01ffd2ed9a29f
    		0x009204e2a9ca0c2ec431c506df58ca7a5ee71c8da531972295e01ffd2ed9a29fDAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18USDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486
    10
    		2023-12-01 00:26:41.0000x045dd9f409bd90f76e0ca564d5ba16557f2e3700b450b6044fcac5ce42bd0ba4
    4
    0x36da0c0cf272f3201fdfbd198c6a8be4612d4a796c546fbdfa01f652d1562bb
    		0x036da0c0cf272f3201fdfbd198c6a8be4612d4a796c546fbdfa01f652d1562bbUSDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486
    0x6c2fd1bcc03e54e4e23b3ffd5f6a4c65a9397996b718e633283d6d4e77a5257
    57
    		2024-01-15 12:12:08.0000x06bf6be8dacb76d99bcb18c2d332a92c7bb1deffebf803a63e29ba42291a716e
    5
    0xa8f1b8fb1c207d287a5f4501e71eb74e45f119ad97f4c36dc8e5962c944ab1
    		0x00a8f1b8fb1c207d287a5f4501e71eb74e45f119ad97f4c36dc8e5962c944ab1wstETH
    0x42b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2
    		0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca018USDT
    0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8
    		0xdac17f958d2ee523a2206206994597c13d831ec76
    46
    		2024-01-05 07:16:31.0000x06da0cdcf347a1ca1897a033756635f6ef54f1a9587dd5fcf4783f3946dad02c
    6
    0x4b7a14b72cac72ad42cbd645e90432437ee9907bf9925bd683426f3cb8eaeab
    		0x04b7a14b72cac72ad42cbd645e90432437ee9907bf9925bd683426f3cb8eaeabETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    0x6c2fd1bcc03e54e4e23b3ffd5f6a4c65a9397996b718e633283d6d4e77a5257
    42
    		2024-01-01 15:59:00.0000x00ac29a6ab20bec190738a4ac467731b2f9189ad1e046186f513276788c81305
    7
    0x522e1112acb96754a5300b52094c2fda7a0b597d0f02bf0e3fc83050c015ab6
    		0x0522e1112acb96754a5300b52094c2fda7a0b597d0f02bf0e3fc83050c015ab6wstETH
    0x42b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2
    		0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca018ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    11
    		2023-12-15 12:17:17.0000x034cc82156ce35f8127020018cbba129855fc12f65a11892ab7fba92dbd87afb
    8
    0x57399c43cf3578f8d2601e3985ef49e6d469c047ac3c508165f500e2110cee7
    		0x057399c43cf3578f8d2601e3985ef49e6d469c047ac3c508165f500e2110cee7DAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18USDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486
    47
    		2024-01-05 19:59:14.0000x05283134a49ed99e3b743c3b3b85c6e829d92e3facb4bfe6c6ffe325842def9d
    9
    0x51d507f0afa0fd5a111dff9b22151dd970e9755c42bc405a45a9f3b433771e7
    		0x051d507f0afa0fd5a111dff9b22151dd970e9755c42bc405a45a9f3b433771e7DAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    36
    		2023-12-29 12:48:03.0000x00c241d3510c0df132685a90cfa5ccea060c9db067b60cd4b54f7b344770dc7f
    10
    0x376b888be7c97382203bcecaa4afc965a7e4274ece4838ce024f74a08c1ce23
    		0x0376b888be7c97382203bcecaa4afc965a7e4274ece4838ce024f74a08c1ce23DAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    25
    		2023-12-28 03:02:35.0000x05fc52cc192577fd24e1553856616ab434ecd078a3c308c2332508db59d1e6e0
    11
    0xd0778809710cecd727b805ca92267ad17d0e6f35f1877086b9a7ca12b98c4c
    		0x00d0778809710cecd727b805ca92267ad17d0e6f35f1877086b9a7ca12b98c4cETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218USDT
    0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8
    		0xdac17f958d2ee523a2206206994597c13d831ec76
    8
    		2023-11-30 19:50:41.0000x043f9c0fab21bf39072b8936a9c5b01ae6014301390b83e65a787e34979a6359
    12
    0x55e2b866b60e89b44251249ac8ccca44e84ad902f3362a5b6ed8d2da3761a8b
    		0x055e2b866b60e89b44251249ac8ccca44e84ad902f3362a5b6ed8d2da3761a8bDAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    40
    		2023-12-31 17:18:53.0000x07b5a3ae37b0033c75de341d652d63c0f12ca97e809567178ccfc8ba1d47cbdb
    13
    0x2017e177972adb0c903e051207a92b261bd1874e463f76ad526ad3c7718966e
    		0x02017e177972adb0c903e051207a92b261bd1874e463f76ad526ad3c7718966eETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218USDT
    0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8
    		0xdac17f958d2ee523a2206206994597c13d831ec76
    19
    		2023-12-27 21:19:28.0000x04f2b13573a2988b053983f595aa5b328b075a9c08192d9e3fea1a620a9f901a
    14
    0x293ccb1955f8ff3517162edb10fd1d56c7b3813c26cbdcb9d6cbfd5522185ac
    		0x0293ccb1955f8ff3517162edb10fd1d56c7b3813c26cbdcb9d6cbfd5522185acETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    0x6c2fd1bcc03e54e4e23b3ffd5f6a4c65a9397996b718e633283d6d4e77a5257
    50
    		2024-01-09 14:14:12.0000x011ad884db4bb271697f85688117da738b2fa06b7f954a5ca1ff01554e8856bc
    15
    0x165a68b05bb3724917bbc2c2dfe4f6b4836928e52b5e41276e1b3b204be93e4
    		0x0165a68b05bb3724917bbc2c2dfe4f6b4836928e52b5e41276e1b3b204be93e4DAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18USDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486
    52
    		2024-01-13 23:02:34.0000x06d38428a235561e4e81aad50fa8892e1182b9ac9ed764e438506cc3dff8e23c
    16
    0x2f7b89acbff65f3f3525d50bdb34b698b6af8fd5b4d30a84489fce2adecee97
    		0x02f7b89acbff65f3f3525d50bdb34b698b6af8fd5b4d30a84489fce2adecee97ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218USDC
    0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8
    		0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb486
    21
    		2023-12-27 22:25:46.0000x05e0679e5d60e79d017252d133e2dcb1b5bc619ae56ddb4f22c2ea81ec9a8365
    17
    0x1fa06b54cc055be1afcad4e3acc72f4f144da6e31f3b902d1bd2256372885d7
    		0x01fa06b54cc055be1afcad4e3acc72f4f144da6e31f3b902d1bd2256372885d7DAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    27
    		2023-12-28 12:37:37.0000x045e553de8da091f3d70ec0185dc82b433d2cf8be9d085385e705d45d033d66d
    18
    0xa03fc39bd98e9991f52e9173a0b56e27e5e5551eda3415ba6dfb93c537e410
    		0x00a03fc39bd98e9991f52e9173a0b56e27e5e5551eda3415ba6dfb93c537e410
    0x319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610
    		ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    26
    		2023-12-28 08:55:01.0000x01d8f113818c4dcf3c2d8cfc44df406bb4cbc9bc948dc2895bc8eae9c434fb21
    19
    0x27b683a8b3a1f386bf5949448fef94fb9b7a004e76467491c4ccd4c9989ebbb
    		0x027b683a8b3a1f386bf5949448fef94fb9b7a004e76467491c4ccd4c9989ebbbDAI
    0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3
    		0x6b175474e89094c44da98b954eedeac495271d0f18USDT
    0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8
    		0xdac17f958d2ee523a2206206994597c13d831ec76
    7
    		2023-11-30 19:26:12.0000x01478ae2c50b8eebf883d34dcdba0a98072ec369957606b783252f3c0f27df58
    20
    0x1d98b1dd7b200d0540a76fe94ff3ab1148b1935c6cbff894e77347777cceca7
    		0x01d98b1dd7b200d0540a76fe94ff3ab1148b1935c6cbff894e77347777cceca7BTC
    0x3fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac
    		0x2260fac5e5542a773aa44fbcfedf7c193bc2c5998ETH
    0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7
    		0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc218
    29
    		2023-12-28 17:35:55.0000x0785eee677f2582d1a83ecef70420a44e2599422296cd9e447bbd8c75b3576ee
    56
    26KB
    9s