WITH tokens AS ( select 'dac17f958d2ee523a2206206994597c13d831ec7.factory.bridge.near' as contract_address, 'USDT' as symbol, 6 as decimal, 'stable' as type union select 'usn' as contract_address, 'USN' as symbol, 18 as decimal, 'stable' as type union select 'wrap.near' as contract_address, 'NEAR' as symbol, 24 as decimal, 'non-stable' as type union select 'a0b86991c6218b36c1d19d4a2e9eb0ce3606eb48.factory.bridge.near' as contract_address, 'USDC' as symbol, 6 as decimal, 'stable' as type union select 'aurora' as contract_address, 'ETH' as symbol, 18 as decimal, 'non-stable' as type union select '2260fac5e5542a773aa44fbcfedf7c193bc2c599.factory.bridge.near' as contract_address, 'WBTC' as symbol, 8 as decimal, 'non-stable' as type union select 'token.v2.ref-finance.near' as contract_address, 'REF' as symbol, 18 as decimal, 'non-stable' as type union select 'token.skyward.near' as contract_address, 'SKYWARD' as symbol, 18 as decimal, 'non-stable' as type union select 'cusd.token.a11bd.near' as contract_address, 'cUSD' as symbol, 24 as decimal, 'stable' as type union select 'celo.token.a11bd.near' as contract_address, 'CELO' as symbol, 24 as decimal, 'non-stable' as type union select '4691937a7508860f876c9c0a2a617e7d9e945d4b.factory.bridge.near', 'WOO' as symbol, 18 as decimal, 'non-stable' as type union select 'meta-pool.near' as contract_address, 'stNEAR' as symbol, 24 as decimal, 'non-stable' as type union select 'linear-protocol.near' as contract_address, 'LINEAR' as symbol, 24 as decimal, 'non-stable' as type union select 'aaaaaa20d9e0e2461697782ef11675f668207961.factory.bridge.near' as contract_address, 'AURORA' as symbol, 18 as decimal, 'non-stable' as type union select 'f5cfbc74057c610c8ef151a439252680ac68c6dc.factory.bridge.near' as contract_address, 'OCT' as symbol, 18 as decimal, 'non-stable' as type union select 'token.pembrock.near' as contract_address, 'PEM' as symbol, 18 as decimal, 'non-stable' as type union select 'sol.token.a11bd.near' as contract_address, 'SOL' as symbol, 24 as decimal, 'non-stable' as type union select '3ea8ea4237344c9931214796d9417af1a1180770.factory.bridge.near' as contract_address, 'FLX' as symbol, 18 as decimal, 'non-stable' as type union select 'token.burrow.near' as contract_address, 'BRRR' as symbol, 18 as decimal, 'non-stable' as type union select 'abr.a11bd.near' as contract_address, 'ABR' as symbol, 24 as decimal, 'non-stable' as type union select 'token.paras.near' as contract_address, 'PARAS' as symbol, 18 as decimal, 'non-stable' as type union select 'nearx.stader-labs.near' as contract_address, 'NearX' as symbol, 24 as decimal, 'non-stable' as type union select 'marmaj.tkn.near' as contract_address, 'marmaj' as symbol, 18 as decimal, 'non-stable' as type union select '52a047ee205701895ee06a375492490ec9c597ce.factory.bridge.near' as contract_address, 'PULSE' as symbol, 18 as decimal, 'non-stable' as type union select 'apys.token.a11bd.near' as contract_address, 'APYS' as symbol, 24 as decimal, 'non-stable' as type union select '6b175474e89094c44da98b954eedeac495271d0f.factory.bridge.near' as contract_address, 'DAI' as symbol, 18 as decimal, 'stable' as type union select 'pixeltoken.near' as contract_address, 'PXT' as symbol, 6 as decimal, 'non-stable' as type union select 'meta-token.near' as contract_address, 'META' as symbol, 24 as decimal, 'non-stable' as type union select 'nexp.near' as contract_address, 'NEXP' as symbol, 4 as decimal, 'non-stable' as type union select 'dbio.near' as contract_address, 'DBIO' as symbol, 18 as decimal, 'non-stable' as type union select 'utopia.secretskelliessociety.near' as contract_address, 'UTO' as symbol, 8 as decimal, 'non-stable' as type union select 'myriadcore.near' as contract_address, 'MYRIA' as symbol, 18 as decimal, 'non-stable' as type union select 'a4ef4b0b23c1fc81d3f9ecf93510e64f58a4a016.factory.bridge.near' as contract_address, '1MIL' as symbol, 18 as decimal, 'non-stable' as type union select 'gear.enleap.near' as contract_address, 'GEAR' as symbol, 18 as decimal, 'non-stable' as type union select 'token.cheddar.near' as contract_address, 'Cheddar' as symbol, 24 as decimal, 'non-stable' as type union select 'ndn.electrik.near' as contract_address, 'ELECTRIK' as symbol, 18 as decimal, 'non-stable' as type union select 'deip-token.near' as contract_address, 'DEIP' as symbol, 18 as decimal, 'non-stable' as type union select 'atocha-token.near' as contract_address, 'ATO' as symbol, 18 as decimal, 'non-stable' as type union select 'v3.oin_finance.near' as contract_address, 'nUSDO' as symbol, 8 as decimal, 'stable' as type union select 'xtoken.ref-finance.near' as contract_address, 'xREF' as symbol, 18 as decimal, 'non-stable' as type union select 'c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2.factory.bridge.near' as contract_address, 'WETH' as symbol, 18 as decimal, 'non-stable' as type union select 'hak.tkn.near' as contract_address, 'HAK' as symbol, 18 as decimal, 'non-stable' as type union select 'de30da39c46104798bb5aa3fe8b9e0e1f348163f.factory.bridge.near' as contract_address, 'GTC' as symbol, 18 as decimal, 'non-stable' as type union select '514910771af9ca656af840dff83e8264ecf986ca.factory.bridge.near' as contract_address, 'LINK' as symbol, 18 as decimal, 'non-stable' as type union select 'berryclub.ek.near' as contract_address, 'BANANA' as symbol, 18 as decimal, 'non-stable' as type union select 'farm.berryclub.ek.near' as contract_address, 'CUCUMBER' as symbol, 18 as decimal, 'non-stable' as type union select 'token.shrm.near' as contract_address, 'SHRM' as symbol, 18 as decimal, 'non-stable' as type union select '9aeb50f542050172359a0e1a25a9933bc8c01259.factory.bridge.near' as contract_address, 'OIN' as symbol, 8 as decimal, 'non-stable' as type union select 'd9c2d319cd7e6177336b0a9c93c21cb48d84fb54.factory.bridge.near' as contract_address, 'HAPI' as symbol, 18 as decimal, 'non-stable' as type ), first15d as ( SELECT IFF(t.symbol > t2.symbol, t2.symbol || '<>' || t.symbol, t.symbol || '<>' || t2.symbol) as pair, SUM( CASE WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) * p.price WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) * p2.price ELSE 0 END ) as volume FROM near.core.fact_receipts LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address LEFT JOIN ( SELECT DATE(timestamp) as date, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p ON DATE(block_timestamp) = date AND p.symbol ILIKE t.symbol LEFT JOIN ( SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p2 ON DATE(block_timestamp) = dated AND p2.symbol ILIKE t2.symbol WHERE receiver_id = 'v2.ref-finance.near' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL AND t.symbol <> t2.symbol AND (DATEDIFF('day', block_timestamp, CURRENT_DATE()) <= 60 AND DATEDIFF('day', block_timestamp, CURRENT_DATE()) > 30) GROUP BY 1 ORDER BY 2 DESC LIMIT 25 ), last15d as ( SELECT IFF(t.symbol > t2.symbol, t2.symbol || '<>' || t.symbol, t.symbol || '<>' || t2.symbol) as pair, SUM( CASE WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) * p.price WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) * p2.price ELSE 0 END ) as volume FROM near.core.fact_receipts LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address LEFT JOIN ( SELECT DATE(timestamp) as date, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p ON DATE(block_timestamp) = date AND p.symbol ILIKE t.symbol LEFT JOIN ( SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p2 ON DATE(block_timestamp) = dated AND p2.symbol ILIKE t2.symbol WHERE receiver_id = 'v2.ref-finance.near' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL --AND (t.symbol IN ('NEAR', 'USDC') AND t2.symbol IN ('NEAR', 'USDC')) AND t.symbol <> t2.symbol AND DATEDIFF('day', block_timestamp, CURRENT_DATE()) <= 30 GROUP BY 1 ORDER BY 2 DESC LIMIT 25 ), first7d as ( SELECT IFF(t.symbol > t2.symbol, t2.symbol || '<>' || t.symbol, t.symbol || '<>' || t2.symbol) as pair, SUM( CASE WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) * p.price WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) * p2.price ELSE 0 END ) as volume FROM near.core.fact_receipts LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address LEFT JOIN ( SELECT DATE(timestamp) as date, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p ON DATE(block_timestamp) = date AND p.symbol ILIKE t.symbol LEFT JOIN ( SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p2 ON DATE(block_timestamp) = dated AND p2.symbol ILIKE t2.symbol WHERE receiver_id = 'v2.ref-finance.near' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL --AND (t.symbol IN ('NEAR', 'USDC') AND t2.symbol IN ('NEAR', 'USDC')) AND t.symbol <> t2.symbol AND (DATEDIFF('day', block_timestamp, CURRENT_DATE()) <= 14 AND DATEDIFF('day', block_timestamp, CURRENT_DATE()) > 7) GROUP BY 1 ORDER BY 2 DESC LIMIT 25 ), last7d as ( SELECT IFF(t.symbol > t2.symbol, t2.symbol || '<>' || t.symbol, t.symbol || '<>' || t2.symbol) as pair, SUM( CASE WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) * p.price WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) * p2.price ELSE 0 END ) as volume FROM near.core.fact_receipts LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address LEFT JOIN ( SELECT DATE(timestamp) as date, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p ON DATE(block_timestamp) = date AND p.symbol ILIKE t.symbol LEFT JOIN ( SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p2 ON DATE(block_timestamp) = dated AND p2.symbol ILIKE t2.symbol WHERE receiver_id = 'v2.ref-finance.near' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL --AND (t.symbol IN ('NEAR', 'USDC') AND t2.symbol IN ('NEAR', 'USDC')) AND t.symbol <> t2.symbol AND DATEDIFF('day', block_timestamp, CURRENT_DATE()) <= 7 GROUP BY 1 ORDER BY 2 DESC LIMIT 25 ), first24h as ( SELECT IFF(t.symbol > t2.symbol, t2.symbol || '<>' || t.symbol, t.symbol || '<>' || t2.symbol) as pair, SUM( CASE WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) * p.price WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) * p2.price ELSE 0 END ) as volume FROM near.core.fact_receipts LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address LEFT JOIN ( SELECT DATE(timestamp) as date, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p ON DATE(block_timestamp) = date AND p.symbol ILIKE t.symbol LEFT JOIN ( SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p2 ON DATE(block_timestamp) = dated AND p2.symbol ILIKE t2.symbol WHERE receiver_id = 'v2.ref-finance.near' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL --AND (t.symbol IN ('NEAR', 'USDC') AND t2.symbol IN ('NEAR', 'USDC')) AND t.symbol <> t2.symbol AND (DATEDIFF('hour', block_timestamp, CURRENT_DATE()) <= 48 AND DATEDIFF('hour', block_timestamp, CURRENT_DATE()) > 24) GROUP BY 1 ORDER BY 2 DESC LIMIT 25 ), last24h as ( SELECT IFF(t.symbol > t2.symbol, t2.symbol || '<>' || t.symbol, t.symbol || '<>' || t2.symbol) as pair, SUM( CASE WHEN t.type = 'stable' THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) WHEN t2.type = 'stable' THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) WHEN p.price IS NOT NULL THEN SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 1) / POW(10, t.decimal) * p.price WHEN p2.price IS NOT NULL THEN COALESCE(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 1) / POW(10, t2.decimal)) * p2.price ELSE 0 END ) as volume FROM near.core.fact_receipts LEFT JOIN tokens t ON SPLIT_PART(REGEXP_SUBSTR(logs[0], 'swapped (.+) for' , 1, 1, 'ei'), ' ', 2) ILIKE t.contract_address LEFT JOIN tokens t2 ON NVL(SPLIT_PART(REGEXP_SUBSTR(logs[ARRAY_SIZE(logs) - 1], 'for (.+)' , 1, 1, 'ei'), ' ', 2), SPLIT_PART(REGEXP_SUBSTR(logs[0], 'for ([a-zA-Z0-9\. ]+)\,' , 1, 1, 'ei'), ' ', 2)) ILIKE t2.contract_address LEFT JOIN ( SELECT DATE(timestamp) as date, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p ON DATE(block_timestamp) = date AND p.symbol ILIKE t.symbol LEFT JOIN ( SELECT DATE(timestamp) as dated, symbol, AVG(price_usd) as price FROM near.core.fact_prices GROUP BY 1, 2 ) p2 ON DATE(block_timestamp) = dated AND p2.symbol ILIKE t2.symbol WHERE receiver_id = 'v2.ref-finance.near' AND REGEXP_SUBSTR(status_value, 'Success') IS NOT NULL AND t.symbol <> t2.symbol AND DATEDIFF('hour', block_timestamp, CURRENT_DATE()) <= 24 GROUP BY 1 ORDER BY 2 DESC LIMIT 25 ) SELECT f.pair, '$' || to_varchar(f.volume, '999,999,999.99') || ' (' || IFF((f.volume - e.volume) * 100 / e.volume > 0, '🟢 ', '🔴 ') || ROUND((f.volume - e.volume) * 100 / e.volume, 2) || '%)' as "24h Volume", '$' || to_varchar(d.volume, '999,999,999.99') || ' (' || IFF((d.volume - c.volume) * 100 / c.volume > 0, '🟢 ', '🔴 ') || ROUND((d.volume - c.volume) * 100 / c.volume, 2) || '%)' as "7d Volume", '$' || to_varchar(b.volume, '999,999,999.99') || ' (' || IFF((b.volume - a.volume) * 100 / a.volume > 0, '🟢 ', '🔴 ') || ROUND((b.volume - a.volume) * 100 / a.volume, 2) || '%)' as "30d Volume" FROM first15d a JOIN last15d b ON a.pair = b.pair JOIN first7d c ON a.pair = c.pair JOIN last7d d ON a.pair = d.pair JOIN first24h e ON a.pair = e.pair JOIN last24h f ON a.pair = f.pair ORDER BY 2 DESC

The Financial District