SELECT "Contract Address", "Past 30 Days", "30-60 Days Ago", CASE WHEN "30-60 Days Ago" > 0 THEN (("Past 30 Days" / "30-60 Days Ago" -1) * 100)::DECIMAL(18, 2) ELSE 0 END AS "M/M", "New MAAs", CASE WHEN "M2 New MAAs" > 0 THEN ("Retained MAAs" / "M2 New MAAs" * 100)::DECIMAL(18, 2) ELSE 0 END AS "M2 Retention", "Daily Average", CASE WHEN "Past 30 Days" > 0 THEN ("Daily Average" / "Past 30 Days" * 100)::DECIMAL(18, 2) ELSE 0 END AS "DAU / MAU" FROM ( SELECT CASE WHEN tx_receiver in ( SELECT ADDRESS FROM near.core.dim_address_labels WHERE LABEL_TYPE = 'dao' AND BLOCKCHAIN = 'near' ) THEN 'Some Value' -- Replace this with the appropriate value WHEN right(tx_receiver, 5) <> '.near' THEN 'OTHER' ELSE tx_receiver END AS "Contract Address", COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN tx_signer END ) as "Past 30 Days", COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '60 Days' AND tr.block_timestamp < Current_date - INTERVAL '30 Days' THEN tx_signer END ) as "30-60 Days Ago", COUNT( DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '30 DAYS' AND first_tx < current_date THEN tx_signer END ) AS "New MAAs", COUNT( DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' AND tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN tx_signer END ) AS "Retained MAAs", COUNT( DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' THEN tx_signer END ) AS "M2 New MAAs", ( ( COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '24 Hours' AND tr.block_timestamp < Current_date THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '48 Hours' AND tr.block_timestamp < Current_date - INTERVAL '24 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '72 Hours' AND tr.block_timestamp < Current_date - INTERVAL '48 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '96 Hours' AND tr.block_timestamp < Current_date - INTERVAL '72 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '120 Hours' AND tr.block_timestamp < Current_date - INTERVAL '96 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '144 Hours' AND tr.block_timestamp < Current_date - INTERVAL '120 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '168 Hours' AND tr.block_timestamp < Current_date - INTERVAL '144 Hours' THEN tx_signer END ) ) / 7 ) AS "Daily Average" FROM ( SELECT block_timestamp, tx_receiver, tx_signer FROM near.core.fact_transactions ) tr JOIN ( SELECT tx_signer AS wallet, MIN(block_timestamp) AS first_tx FROM near.core.fact_transactions tr GROUP BY 1 ) b ON tr.tx_signer = b.wallet WHERE tr.block_timestamp >= CURRENT_DATE - INTERVAL '90 Days' AND tr.block_timestamp < Current_date GROUP BY 1 UNION SELECT 'Overall' AS "Contract Address", COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN tx_signer END ) as "Past 30 Days", COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '60 Days' AND tr.block_timestamp < Current_date - INTERVAL '30 Days' THEN tx_signer END ) as "30-60 Days Ago", COUNT( DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '30 DAYS' AND first_tx < current_date THEN tx_signer END ) AS "New MAAs", COUNT( DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' AND tr.block_timestamp >= CURRENT_DATE - INTERVAL '30 Days' AND tr.block_timestamp < Current_date THEN tx_signer END ) AS "Retained MAAs", COUNT( DISTINCT CASE WHEN first_tx >= current_date - INTERVAL '60 DAYS' AND first_tx < current_date - INTERVAL '30 DAYS' THEN tx_signer END ) AS "M2 New MAAs", ( ( COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '24 Hours' AND tr.block_timestamp < Current_date THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '48 Hours' AND tr.block_timestamp < Current_date - INTERVAL '24 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '72 Hours' AND tr.block_timestamp < Current_date - INTERVAL '48 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '96 Hours' AND tr.block_timestamp < Current_date - INTERVAL '72 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '120 Hours' AND tr.block_timestamp < Current_date - INTERVAL '96 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '144 Hours' AND tr.block_timestamp < Current_date - INTERVAL '120 Hours' THEN tx_signer END ) + COUNT( DISTINCT CASE WHEN tr.block_timestamp >= CURRENT_DATE - INTERVAL '168 Hours' AND tr.block_timestamp < Current_date - INTERVAL '144 Hours' THEN tx_signer END ) ) / 7 ) AS "Daily Average" FROM ( SELECT block_timestamp, tx_receiver, tx_signer FROM near.core.fact_transactions ) tr JOIN ( SELECT tx_signer AS wallet, MIN(block_timestamp) AS first_tx FROM near.core.fact_transactions tr GROUP BY 1 ) b ON tr.tx_signer = b.wallet WHERE tr.block_timestamp >= CURRENT_DATE - INTERVAL '90 Days' AND tr.block_timestamp < Current_date GROUP BY 1 ) a ORDER BY 2 DESC LIMIT 100

Query	Creator	Created
Osmosis Transactions Total Count Daily copy osmosis ibc	y3k	Oct 10, 2023
Financials copy near prices	y3k	Apr 14, 2023
Atlas - Comp Txns near ethereum polygon evm	y3k	Sep 18, 2023
DAO-Top-Table near	y3k	Aug 21, 2023
2023-05-05 09:00 AM	y3k	May 5, 2023
Age Users copy near	y3k	Apr 27, 2023
Overtime Social Activty near	y3k	Sep 11, 2023
Total Deposits per Day near	y3k	Aug 21, 2023
Axelar Transactions Total Count Daily copy axelar ibc	y3k	Oct 10, 2023

y3k