-- SELECT COUNT(DISTINCT(ADDRESS)) -- FROM flipside_prod_db.algorand.account -- WHERE ACCOUNT_CLOSED = FALSE -- AND -- BALANCE > 1 -- LIMIT 10; -- SELECT * -- FROM ethereum.core.ez_token_transfers -- WHERE FROM_ADDRESS='0x90bfe03e6c81adbd83398eab4fd028e1605efa1d' AND TO_ADDRESS='0x5b3256965e7c3cf26e11fcaf296dfc8807c01073'; -- SELECT * FROM solana.core.fact_nft_sales WHERE mint IN ( -- SELECT address FROM solana.core.dim_labels WHERE LOWER(address_name) LIKE '%bubblegoose%' -- ); --SELECT * FROM solana.core.dim_labels WHERE LOWER(address_name) LIKE '%bubblegoose%'; --SELECT * FROM solana.core.fact_nft_mints WHERE mint = 'MgGuYqNRL4ALXHDFcNXJYJ496GWBBBt58uE2v7j3wcz'; -- SELECT distinct(address) FROM solana.core.dim_labels WHERE LOWER(address_name) LIKE '%bubblegoose%'; --SELECT * FROM solana.core.dim_labels WHERE LOWER(address_name) LIKE '%bubble%'; -- SELECT * -- FROM solana.core.fact_transfers -- WHERE mint in (SELECT mint FROM solana.core.dim_nft_metadata WHERE LOWER(TOKEN_NAME) LIKE '%bubble%' ORDER BY TOKEN_ID); -- SELECT * -- FROM solana.core.fact_nft_mints -- WHERE tx_id in ('5qoV8PfxJx1KQKrZVjBYdrKFKbr27nUFBKWz9iwPyLzqQQVRTEJN7qjccPUfEvevCwQJZKdxTQWxDacnVBphbUsu'); -- WITH i as ( -- SELECT marketplace, -- block_timestamp, -- tx_id, -- purchaser, -- nm.mint, -- sales_amount, -- blockchain, -- contract_address, -- project_name, -- token_id -- FROM solana.core.fact_nft_sales nm -- RIGHT JOIN solana.core.dim_nft_metadata dnm ON nm.mint = dnm.mint -- WHERE LOWER(TOKEN_NAME) LIKE '%bubble%' -- ) -- SELECT * from i; -- SELECT * -- FROM solana.core.fact_nft_mints -- WHERE mint_currency != 'So11111111111111111111111111111111111111111' -- AND -- BLOCK_TIMESTAMP::date = '2022-06-13'; -- SELECT * -- FROM solana.core.fact_nft_mints nm INNER JOIN solana.core.fact_nft_sales ns ON nm.mint = ns.mint LIMIT 5; -- --WHERE ns.mint IN (SELECT address FROM solana.core.dim_labels WHERE LOWER(address_name) LIKE '%bubblegoose%'); -- SELECT nm.* -- FROM solana.core.fact_nft_mints nm -- WHERE succeeded=TRUE AND block_timestamp::date>'2022-05-05' -- LIMIT 100; -- SELECT * -- FROM solana.core.fact_nft_sales s -- LEFT JOIN solana.core.fact_nft_mints n USING(mint) -- LEFT JOIN solana.core.dim_labels l ON s.mint = l.address -- WHERE n.succeeded=TRUE AND LOWER(label) LIKE '%suites%' -- LIMIT 10; /* Q87. What is the average and median return on investment for candy machine NFT creators? As in how much revenue are NFT creators making off mint and then off royalties? How has that trended over time and how do you see this shaping the NFT landscape on Solana? */ /* NFT sales: number of transactions & transaction volume */ -- SELECT label, COUNT(label) AS "number_of_transactions", SUM(sales_amount) AS "transaction_volume" -- FROM solana.core.fact_nft_sales ns -- LEFT JOIN solana.core.dim_labels l ON ns.mint = l.address -- WHERE ns.succeeded=TRUE AND block_timestamp::date >= '2022-01-01' AND label!='' -- GROUP BY LABEL -- ORDER BY 3 DESC; -- SELECT LOWER(token_name), COUNT(DISTINCT(n.mint)) AS "total_supply" -- FROM solana.core.dim_nft_metadata dl -- RIGHT JOIN solana.core.fact_nft_mints n ON dl.mint = n.mint -- WHERE block_timestamp::date>='2022-01-01' -- AND succeeded=TRUE AND LOWER(token_name)!='' -- --AND mint_currency='So11111111111111111111111111111111111111111' -- GROUP BY LOWER(token_name) -- ORDER BY 2 DESC; --------------AVAX------------------------ /* How frequently block are added */ -- WITH lag as ( -- SELECT *, -- LAG(block_timestamp, 1) over(ORDER BY block_number ASC) AS lag1 -- FROM avalanche.core.fact_blocks -- WHERE block_timestamp::date >= '2022-06-20' -- AND network='mainnet' -- ORDER BY 1 -- ) -- SELECT block_number, -- block_timestamp, -- tx_count -- difficulty, -- total_difficulty, -- gas_used, -- size, -- lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) -- FROM lag -- LIMIT 100; /* Average Number of Txs in a block */ -- SELECT AVG(size) AS "Avg Size of a block", -- MAX(size) AS "Max Size of a block", -- MIN(size) AS "Min Size of a block" -- FROM avalanche.core.fact_blocks -- WHERE block_timestamp::date >= '2022-06-20' -- AND network='mainnet' -- ORDER BY 1 -- LIMIT 100; /* GAS */ -- relationship btwn difficulty (and size) and gas -- WITH h AS ( -- SELECT block_number, -- block_timestamp, -- EXTRACT (HOUR FROM block_timestamp) as hour_v, -- tx_count -- difficulty, -- total_difficulty, -- gas_used, -- size -- FROM avalanche.core.fact_blocks -- WHERE block_timestamp::date >= '2022-06-20' -- AND network='mainnet' -- ORDER BY 1 -- ) -- SELECT * -- FROM h -- ORDER BY 1 -- LIMIT 1000; -- WITH eth_block_table AS ( -- SELECT block_timestamp::date AS "date", -- SUM(tx_count) AS "total_txns_eth" -- FROM ethereum.core.fact_blocks -- WHERE block_timestamp::date >= '2022-06-11' AND block_timestamp::date <= '2022-06-25' -- AND network='mainnet' -- GROUP BY 1 -- ), -- avax_block_table AS ( -- SELECT block_timestamp::date AS "date", -- SUM(tx_count) AS "total_txns_avax" -- FROM avalanche.core.fact_blocks a -- WHERE block_timestamp::date >= '2022-06-11' AND block_timestamp::date <= '2022-06-25' -- AND network='mainnet' -- GROUP BY 1 -- ) -- SELECT * -- FROM eth_block_table e -- INNER JOIN avax_block_table a ON a."date"=e."date" -- ORDER BY e."date"; ------------------ALGO------------------------- -- SELECT * -- FROM flipside_prod_db.algorand.prices_swap -- WHERE ASSET_ID=0 -- ORDER BY BLOCK_HOUR -- LIMIT 100; -- SELECT * -- FROM ethereum.core.fact_hourly_token_prices -- WHERE symbol IN ('WBTC') AND hour::date >= '2022-01-01' -- ORDER BY hour -- LIMIT 100; -- WITH eth_price AS ( -- SELECT *, -- ROW_NUMBER() OVER(PARTITION BY hour::date ORDER BY hour) as rn -- FROM ethereum.core.fact_hourly_token_prices -- WHERE symbol = 'WETH' AND hour::date >= '2022-1-1' -- ORDER BY hour -- ), -- btc_price AS ( -- SELECT *, -- ROW_NUMBER() OVER(PARTITION BY hour::date ORDER BY hour) as rn -- FROM ethereum.core.fact_hourly_token_prices -- WHERE symbol = 'WBTC' AND hour::date >= '2022-1-1' -- ORDER BY hour -- ), -- algo_price AS ( -- SELECT *, -- ROW_NUMBER() OVER(PARTITION BY block_hour::date ORDER BY block_hour) as rn -- FROM flipside_prod_db.algorand.prices_swap -- WHERE ASSET_ID=0 -- ORDER BY BLOCK_HOUR -- ) -- SELECT e.hour::date AS date, -- ROUND(e.price, 2) AS eth_price, -- ROUND(b.price, 2) AS btc_price, -- ROUND(a.price_usd, 2) AS algo_price -- FROM eth_price e -- INNER JOIN btc_price b ON e.hour::date=b.hour::date AND e.rn=b.rn -- INNER JOIN algo_price a ON e.hour::date=a.block_hour::date AND e.rn=a.rn -- WHERE e.rn=1 -- ORDER BY 1; -- WITH -- algo_price AS ( -- SELECT block_hour::date AS date, -- ROUND(PRICE_USD, 2) AS algo_price, -- ROW_NUMBER() OVER(PARTITION BY block_hour::date ORDER BY block_hour) as rn -- FROM flipside_prod_db.algorand.prices_swap -- WHERE ASSET_ID=0 AND block_hour::date >= '2022-01-01' -- ORDER BY BLOCK_HOUR -- ), -- /*Number of total txns per day*/ -- total_algo_txn AS ( -- SELECT block_timestamp::date AS date, -- COUNT(DISTINCT(tx_id)) AS txn_count -- FROM flipside_prod_db.algorand.transactions -- WHERE block_timestamp::date >= '2022-01-01' -- GROUP BY 1 -- ORDER BY 1 -- ) -- SELECT ap.date, algo_price, txn_count -- FROM algo_price ap -- INNER JOIN total_algo_txn tat ON ap.date = tat.date -- ORDER BY ap.date; -- SELECT DISTINCT(tx_type_name) -- FROM flipside_prod_db.algorand.transactions -- WHERE block_timestamp::date >= '2022-02-01'; --SOL Price -- SELECT hour::date, -- price, -- ROW_NUMBER() OVER(PARTITION BY hour::date ORDER BY hour DESC) AS rn -- FROM ethereum.core.fact_hourly_token_prices -- WHERE token_address ILIKE '0xD31a59c85aE9D8edEFeC411D448f90841571b89c' AND hour::date = '2022-06-27' -- ORDER BY hour -- LIMIT 100; --SOL Total Transactions -- SELECT block_timestamp::date AS date, -- SUM(TX_COUNT) AS txn_count -- FROM solana.core.fact_blocks -- WHERE block_timestamp >= '2022-1-1' -- AND network = 'mainnet' -- GROUP BY 1 -- ORDER BY 1; -- WITH eth_price_table AS ( -- SELECT hour,--::date AS date, -- ROUND(price, 2) AS eth_price, -- ROW_NUMBER() OVER(PARTITION BY hour::date ORDER BY hour DESC) AS rn -- FROM ethereum.core.fact_hourly_token_prices -- WHERE symbol = 'WETH' AND hour::date >= '2022-1-1' -- ORDER BY hour -- ), -- algo_price_table AS ( -- SELECT block_hour::date AS date, -- ROUND(PRICE_USD, 2) AS algo_price, -- ROW_NUMBER() OVER(PARTITION BY block_hour::date ORDER BY block_hour DESC) AS rn -- FROM flipside_prod_db.algorand.prices_swap -- WHERE ASSET_ID = 0 AND block_hour::date >= '2021-1-1' -- ORDER BY BLOCK_HOUR -- ) -- SELECT * -- FROM algo_price_table -- WHERE date = '2022-01-14' -- LIMIT 100; ----------------SHROOMDK------------------ -- SELECT * -- FROM ethereum.core.ez_nft_sales -- WHERE nft_address ILIKE '0xdfb57B6E16DDB97Aeb8847386989f4DCA7202146' -- ORDER BY block_timestamp DESC; -- SELECT block_timestamp, -- price AS eth_price -- FROM ethereum.core.ez_nft_sales -- WHERE nft_address ILIKE '0xdfb57B6E16DDB97Aeb8847386989f4DCA7202146' -- ORDER BY block_timestamp; --Number of unique Owners -- SELECT * -- FROM ethereum.core.ez_nft_mints -- WHERE nft_address ILIKE '0xdfb57B6E16DDB97Aeb8847386989f4DCA7202146' -- AND BLOCK_TIMESTAMP::date = '2022-06-29' -- ORDER BY block_timestamp DESC; -- SELECT block_number, -- block_timestamp -- block_hash, -- tx_hash, -- from_address, -- to_address, -- tx_fee, -- status -- FROM ethereum.core.fact_transactions -- WHERE to_address ILIKE '0xdfb57b6e16ddb97aeb8847386989f4dca7202146' -- AND status = 'FAIL' -- ORDER BY block_timestamp DESC; ------------------ALGO, ALAMMEX-------------------- -- WITH alm AS ( -- SELECT * -- --BASE64_DECODE_string(tx_message:txn:note) AS decoded -- FROM flipside_prod_db.algorand.transfers t -- WHERE t.block_timestamp::date >= '2022-06-27' -- AND tx_id = 'FXPF4ONAQ6GWQZIBKMPA5QYVJFCJECKPCXLXEJ7AAJYVL6STKYOQ' -- ) -- SELECT * -- FROM alm; ---------------SWAP PROFILES ON ALGO-------------------- -- total swappers in May -- SELECT COUNT(DISTINCT(swapper)) -- FROM flipside_prod_db.algorand.swaps -- WHERE block_timestamp::date BETWEEN '2022-05-01' AND '2022-07-05' -- ORDER BY block_timestamp -- LIMIT 5 -- How often are they swapping -- SELECT swapper, -- COUNT(*) AS number_of_swaps -- FROM flipside_prod_db.algorand.swaps -- WHERE block_timestamp::date BETWEEN '2022-05-01' AND '2022-07-05' -- GROUP BY 1 -- ORDER BY 2 DESC -- LIMIT 5 -- WITH j AS ( -- -- ALGO Balances for swappers -- SELECT * -- FROM flipside_prod_db.algorand.account -- WHERE address IN ( -- SELECT DISTINCT(swapper) -- FROM flipside_prod_db.algorand.swaps -- WHERE block_timestamp::date BETWEEN '2022-05-01' AND '2022-07-05' -- ) -- ) -- -- When are swappers wallets created -- SELECT j.*, -- b.block_id, -- b.block_timestamp::date as date -- FROM j -- JOIN flipside_prod_db.algorand.block b ON j.created_at = block_id; ---what assets do swappers hold -- SELECT address, -- asset_name, -- SUM(amount) -- FROM flipside_prod_db.algorand.account_asset -- WHERE address IN ( -- SELECT DISTINCT(swapper) -- FROM flipside_prod_db.algorand.swaps -- WHERE block_timestamp::date BETWEEN '2022-05-01' AND '2022-07-05' -- ) AND asset_closed = FALSE -- AND address='RWZPX4PP2J73WQZVPDV2HSVD65YJYFJLDDG3NLN75AO6W7SGMCQWK3WJNE' -- GROUP BY 1, 2 -- HAVING SUM(amount) > 0; -- WITH binned_swap AS ( -- SELECT address, -- balance, -- CASE -- WHEN balance > 0 AND balance <= 10 THEN '1-10' -- WHEN balance > 10 AND balance <= 20 THEN '11-20' -- WHEN balance > 20 AND balance <= 30 THEN '21-30' -- WHEN balance > 30 AND balance <= 40 THEN '31-40' -- WHEN balance > 40 AND balance <= 50 THEN '41-50' -- WHEN balance > 50 AND balance <= 60 THEN '51-60' -- WHEN balance > 60 AND balance <= 70 THEN '61-70' -- WHEN balance > 70 AND balance <= 80 THEN '71-80' -- WHEN balance > 80 AND balance <= 90 THEN '81-90' -- WHEN balance > 90 AND balance <= 100 THEN '91-100' -- END AS bin -- FROM flipside_prod_db.algorand.account -- WHERE address IN ( -- SELECT DISTINCT(swapper) -- FROM flipside_prod_db.algorand.swaps -- WHERE block_timestamp::date BETWEEN '2022-05-01' AND '2022-06-30' -- ) -- AND balance > 0 AND balance <= 100 -- ) -- Select bin, -- COUNT(*) as avg_number_of_swaps -- FROM flipside_prod_db.algorand.swaps s -- JOIN binned_swap b ON s.swapper = b.address -- WHERE block_timestamp::date BETWEEN '2022-05-01' AND '2022-06-30' -- GROUP BY bin; ----------------POLYGON------------------ -- SELECT block_timestamp::date, -- COUNT(*), -- COUNT(DISTINCT(from_address)) -- FROM polygon.core.fact_transactions -- WHERE block_timestamp::date >= '2022-07-1' -- GROUP BY 1 -- ORDER BY 1; ------Sol--------- -- WITH p AS ( -- SELECT fs.*, -- d1.address AS swap_from_address, -- --d1.label_type, -- d1.label AS swap_from_name, -- d2.address AS swap_to_address, -- --d2.label_type, -- d2.label AS swap_to_name, -- swap_from_name || ' -> ' || swap_to_name AS swapped_pair -- FROM solana.core.fact_swaps fs -- LEFT JOIN solana.core.dim_labels d1 ON fs.swap_from_mint = d1.address -- LEFT JOIN solana.core.dim_labels d2 ON fs.swap_to_mint = d2.address -- WHERE block_timestamp::date >= '2022-07-1' -- AND succeeded = TRUE -- --AND swap_program ILIKE '%raydium%' -- ) -- SELECT * -- FROM p -- WHERE swap_to_name ILIKE '%raydium%' -- OR swap_from_name ILIKE '%raydium%' -- LIMIT 100; -- SELECT * -- FROM ethereum.core.fact_hourly_token_prices -- WHERE token_address ILIKE '0x5245C0249e5EEB2A0838266800471Fd32Adb1089' -- LIMIT 100; -------------NEAR---------------- -- SELECT block_height, -- txn_hash, -- block_timestamp, -- tx_receipt, -- tx_receipt[0]:"outcome":"status", -- substr(tx_receipt[0]:"outcome":"status",3,7) as success -- FROM flipside_prod_db.mdao_near.transactions -- WHERE block_timestamp::date = '2022-07-18' -- LIMIT 2; -- SELECT block_timestamp::date AS date, -- SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS eth_failed_txns, -- COUNT(DISTINCT(tx_hash)) AS total_eth_txn -- FROM ethereum.core.fact_transactions -- WHERE block_timestamp::date >= '2022-06-01' -- GROUP BY 1 -- ORDER BY 1; -- SELECT block_timestamp::date AS date, -- COUNT(DISTINCT(tx_hash)) AS eth_failed_txn -- FROM ethereum.core.fact_transactions -- WHERE status = 'FAIL' -- AND block_timestamp::date >= '2022-06-01' -- GROUP BY 1 -- ORDER BY 1; --------------IMPERMANENT LOSS--------------------- -- SELECT * -- FROM ethereum.core.dim_dex_liquidity_pools -- WHERE platform ILIKE '%sushi%'; --LIMIT 100; --AND pool_name ILIKE 'usdc/weth'; -- SELECT * -- FROM ethereum.sushi.ez_swaps -- WHERE block_number BETWEEN 14000000 AND 15000000 -- LIMIT 300; -- --AND symbol_out ILIKE 'sushi'; -- SELECT DISTINCT(contract_name) -- FROM ethereum.core.fact_event_logs -- WHERE block_number BETWEEN 14000000 AND 15000000 -- AND tx_status = 'SUCCESS' -- AND event_name ILIKE 'deposit' -- LIMIT 1000; -- SELECT * -- FROM ethereum.core.fact_event_logs -- WHERE event_name ILIKE 'deposit'; --LIMIT 100; --WHERE tx_hash ILIKE '0x8b8c0e960b0678b07c00fc11a07667d9f564916add155babdc069acff7befd8d'; ------------------POLYGON BLOCK-------------------------- /* Block stats */ -- WITH poly_block_stats AS ( -- SELECT block_number, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM polygon.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- sol_block_stats AS ( -- SELECT block_height, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM solana.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- flow_block_stats AS ( -- SELECT block_height, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM flow.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- arbitrum_block_stats AS ( -- SELECT block_number, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM arbitrum.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- optimism_block_stats AS ( -- SELECT block_number, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM optimism.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ) -- SELECT 'polygon' as blockchain, AVG(diff), MAX(diff), MIN(diff) -- FROM poly_block_stats -- UNION -- SELECT 'solana' as blockchain, AVG(diff), MAX(diff), MIN(diff) -- FROM sol_block_stats -- UNION -- SELECT 'flow' as blockchain, AVG(diff), MAX(diff), MIN(diff) -- FROM flow_block_stats -- UNION -- SELECT 'arbitrum' as blockchain, AVG(diff), MAX(diff), MIN(diff) -- FROM arbitrum_block_stats -- UNION -- SELECT 'optimism' as blockchain, AVG(diff), MAX(diff), MIN(diff) -- FROM optimism_block_stats; /* Block stats */ -- WITH poly_block_stats AS ( -- SELECT block_number, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM polygon.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- sol_block_stats AS ( -- SELECT block_height, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM solana.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- flow_block_stats AS ( -- SELECT block_height, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM flow.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- arbitrum_block_stats AS ( -- SELECT block_number, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM arbitrum.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ), -- optimism_block_stats AS ( -- SELECT block_number, -- block_timestamp, -- tx_count, -- LAG(block_timestamp, 1) OVER(ORDER BY block_timestamp) as lag1, -- DATEDIFF(SECOND, lag1, block_timestamp) as diff -- FROM optimism.core.fact_blocks -- WHERE block_timestamp BETWEEN '2022-06-06' AND '2022-07-21' -- AND network = 'mainnet' -- ORDER BY block_timestamp -- ) -- SELECT 'polygon' as blockchain, -- diff -- FROM poly_block_stats -- UNION ALL -- SELECT 'solana' as blockchain, -- diff -- FROM sol_block_stats; -- -- UNION -- -- SELECT 'flow' as blockchain, -- -- diff -- -- FROM flow_block_stats -- -- UNION -- -- SELECT 'arbitrum' as blockchain, -- -- diff -- -- FROM arbitrum_block_stats -- -- UNION -- -- SELECT 'optimism' as blockchain, -- -- diff -- -- FROM optimism_block_stats; -----TORNADO CASH-------- -- SELECT * -- FROM flipside_prod_db.crosschain.address_labels -- WHERE project_name ILIKE '%tornado%' -- AND label_type='dapp' -- AND blockchain = 'ethereum'; ----CEX---- -- SELECT block_timestamp::DATE as date, -- label, -- SUM(amount) as total_eth_out -- FROM ethereum.core.ez_eth_transfers et -- LEFT JOIN ethereum.core.dim_labels dl ON et.eth_from_address = dl.address -- WHERE block_timestamp::date >= CURRENT_DATE - 300 -- AND dl.label_type ILIKE 'cex' -- AND dl.label_subtype ILIKE 'hot_wallet' -- GROUP BY 1, 2 -- HAVING label IN ('binance', 'bitstamp', 'kraken', 'coinbase', 'kucoin') -- ORDER BY 1, 2; -- SELECT label_type, -- count(*) -- FROM ethereum.core.dim_labels -- GROUP BY label_type; -- -- WHERE label_type ILIKE 'cex' -- -- AND label_subtype ILIKE 'hot_wallet'; -- SELECT * -- FROM ethereum.core.fact_hourly_token_prices -- WHERE token_address ILIKE '0xD31a59c85aE9D8edEFeC411D448f90841571b89c' -- sol -- AND hour::date = CURRENT_DATE - 1 -- ORDER BY hour DESC; -- netflow, market cap (over time), daily closing price, number of swappers --ETH PRICE -- SELECT * -- FROM ethereum.core.fact_hourly_token_prices -- WHERE symbol ILIKE 'weth' -- ORDER BY hour DESC -- LIMIT 19; --PRICE -- SELECT * -- FROM ethereum.core.fact_hourly_token_prices -- WHERE symbol ILIKE 'matic' -- ORDER BY hour DESC -- LIMIT 19; --NEAR STAKE -- WITH stake AS ( -- SELECT block_timestamp::DATE as date, -- COUNT(*) as stake_count, -- SUM(stake_amount) as stake_amount -- FROM near.core.dim_staking_actions -- WHERE block_timestamp::DATE >= '2022-08-01' -- AND action ILIKE 'stake' -- GROUP BY 1 -- ), -- unstake AS ( -- SELECT block_timestamp::DATE as date, -- COUNT(*) as unstake_count, -- SUM(stake_amount) as unstake_amount -- FROM near.core.dim_staking_actions -- WHERE block_timestamp::DATE >= '2022-08-01' -- AND action ILIKE 'unstake' -- GROUP BY 1 -- ) -- SELECT s.date, -- s.stake_count, -- s.stake_amount, -- us.unstake_count, -- us.unstake_amount -- FROM stake s -- INNER JOIN unstake us ON s.date = us.date -- ORDER BY s.date; --FLOW STAKE -- SELECT distinct(action) -- FROM flow.core.ez_staking_actions -- WHERE action ILIKE '%committed%' -- AND tx_succeeded = TRUE; --withdrawn --SOL STAKE -- WITH sol_stake AS ( -- SELECT block_timestamp::date as date, -- amount as stake_amount -- FROM solana.core.fact_stake_pool_actions -- WHERE action ILIKE 'deposit' -- AND block_timestamp::DATE >= '2022-08-01' -- AND succeeded = TRUE -- ), -- sol_unstake AS ( -- SELECT block_timestamp::date as date, -- amount as unstake_amount -- FROM solana.core.fact_stake_pool_actions -- WHERE action ILIKE 'withdraw' -- AND block_timestamp::DATE >= '2022-08-01' -- AND succeeded = TRUE -- ) -- SELECT s.date, -- s.stake_amount, -- us.unstake_amount -- FROM sol_stake ss -- INNER JOIN sol_unstake sus ON ss.date = sus.date -- ORDER BY s.date; --NEAR IN and OUT --in = usd swapped in to buy other coin --out = other coin swapped in to buy usd -- WITH usd_in AS ( -- SELECT block_timestamp::date as date, -- SUM(amount_in) as usd_in -- FROM near.core.ez_dex_swaps -- WHERE token_in ILIKE '%usd%' -- AND block_timestamp::DATE >= '2022-08-01' -- GROUP BY 1 -- ), -- usd_out AS ( -- SELECT block_timestamp::date as date, -- -1 * SUM(amount_out) as usd_out -- FROM near.core.ez_dex_swaps -- WHERE token_out ILIKE '%usd%' -- AND block_timestamp::DATE >= '2022-08-01' -- GROUP BY 1 -- ) -- SELECT i.date, -- i.usd_in, -- o.usd_out -- FROM usd_in i -- INNER JOIN usd_out o ON i.date = o.date -- ORDER BY i.date DESC; -- SOL vs. NEAR Price -- WITH -- sol_price_table AS ( -- SELECT hour::date AS date, -- price AS sol_price, -- ROW_NUMBER() OVER(PARTITION BY hour::date ORDER BY hour DESC) AS rn -- FROM ethereum.core.fact_hourly_token_prices -- WHERE token_address ILIKE '0xD31a59c85aE9D8edEFeC411D448f90841571b89c' -- AND hour::date >= '2022-08-01' -- ORDER BY hour -- ), -- near_price_table AS ( -- SELECT timestamp::date AS date, -- price_usd AS near_price, -- ROW_NUMBER() OVER(PARTITION BY timestamp::date ORDER BY timestamp DESC) AS rn -- FROM near.core.fact_prices -- WHERE timestamp::DATE >= '2022-08-01' -- AND symbol ILIKE 'wnear' -- ORDER BY timestamp::date DESC -- ) -- SELECT s.date, -- s.sol_price, -- n.near_price -- FROM sol_price_table s -- INNER JOIN near_price_table n ON s.date = n.date -- WHERE s.rn = 1 and n.rn = 1 -- ORDER BY s.date DESC; -- for medium.com SELECT block_number, block_timestamp, tx_count FROM polygon.core.fact_blocks WHERE block_timestamp::date = '2022-06-06' --BETWEEN '2022-06-06' AND '2022-07-07' AND network = 'mainnet' ORDER BY block_timestamp;

shahwaiz