Dune AI

    SELECT 
        date_trunc(\'day\', block_time) AS time
        , (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gas_price))/1e9  AS median_gas
        , COUNT(*) AS daily_tx
    FROM ethereum.transactions
    GROUP BY time

SELECT 
    date_trunc('day', block_time) AS time
    , MIN(gas_price)/1e9 AS min_gas
    , (PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY gas_price))/1e9 AS lower_quartile_gas
    , (PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gas_price))/1e9  AS median_gas
    , (PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY gas_price))/1e9 AS upper_quartile_gas
    , MAX(gas_price)/1e9 AS max_gas
    , COUNT(*) AS daily_tx
FROM ethereum.transactions
GROUP BY time

SELECT  date_trunc('day', block_time) as day, COUNT(distinct seller) as sellers
FROM nft.trades
WHERE project = 'blur'
GROUP BY 1

SELECT  
    date_trunc('day', t.block_time) as day, 
    COUNT(distinct t.seller) as sellers, 
    COUNT(distinct wt.seller) as wash_traders, 
    (COUNT(distinct wt.seller) * 100.0 / COUNT(distinct t.seller)) as wash_traders_percentage
FROM 
    nft.trades t
LEFT JOIN 
    nft.wash_trades wt ON t.seller = wt.seller
WHERE 
    t.project = 'blur'
GROUP BY 
    1

SELECT
 DATE_TRUNC('month', block_time),
 SUM(CAST(amount_usd AS DOUBLE)) AS usd_volume
FROM
 dex.trades AS t
WHERE
block_time > CAST('2019-01-01' AS TIMESTAMP)
GROUP BY
 1

SELECT
  DATE_TRUNC('month', block_time) AS month,
  SUM(CASE WHEN EXTRACT(YEAR FROM block_time) = 2019 THEN CAST(amount_usd AS DOUBLE) ELSE 0 END) AS usd_volume_2019,
  SUM(CASE WHEN EXTRACT(YEAR FROM block_time) = 2020 THEN CAST(amount_usd AS DOUBLE) ELSE 0 END) AS usd_volume_2020,
  SUM(CASE WHEN EXTRACT(YEAR FROM block_time) = 2021 THEN CAST(amount_usd AS DOUBLE) ELSE 0 END) AS usd_volume_2021,
  SUM(CASE WHEN EXTRACT(YEAR FROM block_time) = 2022 THEN CAST(amount_usd AS DOUBLE) ELSE 0 END) AS usd_volume_2022,
  SUM(CASE WHEN EXTRACT(YEAR FROM block_time) = 2023 THEN CAST(amount_usd AS DOUBLE) ELSE 0 END) AS usd_volume_2023
FROM
  dex.trades AS t
WHERE
 block_time >= CAST('2019-01-01' AS TIMESTAMP)
GROUP BY
  1

select
 date_trunc('day', block_time) as dt
 ,count(hash) as num_transactions
 ,count(distinct "from") as users
 ,sum(gas_used) as gas_used
 ,avg(gas_used) as avg_gas_used
 ,avg(gas_price)/1e9 as avg_gas_price
from ethereum.transactions
where
 success
 and block_time >= cast('2022-01-01' as date)
 and date_trunc('day', block_time) < date_trunc('day', current_timestamp)
group by 1

WITH daily_mints AS (
    SELECT 
        date_trunc('day', block_time) as dt,
        count(*) as num_mints
    FROM nft.mints
    WHERE 
        block_time >= cast('2022-01-01' as date) 
        and date_trunc('day', block_time) < date_trunc('day', current_timestamp)
    GROUP BY 1
)

SELECT 
    et.dt,
    et.num_transactions,
    et.users,
    et.gas_used,
    et.avg_gas_used,
    et.avg_gas_price,
    dm.num_mints,
    avg(dm.num_mints) over () as avg_daily_mints
FROM (
    SELECT 
        date_trunc('day', block_time) as dt,
        count(hash) as num_transactions,
        count(distinct "from") as users,
        sum(gas_used) as gas_used,
        avg(gas_used) as avg_gas_used,
        avg(gas_price)/1e9 as avg_gas_price
    FROM ethereum.transactions
    WHERE 
        success
        and block_time >= cast('2022-01-01' as date) 
        and date_trunc('day', block_time) < date_trunc('day', current_timestamp)
    GROUP BY 1
) et
LEFT JOIN daily_mints dm ON et.dt = dm.dt