ddccOpensea Hot or Not USD Volume
    Updated 2021-10-06
    --- sale counts: gains and losses comparing this week vs previous week
    WITH project_names AS
    (SELECT contract_address,
    lower(project_name) AS project_name
    FROM ethereum.nft_events
    WHERE project_name IS NOT NULL
    GROUP BY 1,
    2),
    T3 AS
    (SELECT contract_address,
    sum(price) AS eth_sales_sum_t3,
    min(price) AS eth_sales_min_t3,
    avg(price) AS eth_sales_mean_t3,
    count(tx_id) AS eth_sales_count_t3
    FROM ethereum.nft_events
    WHERE event_platform = 'opensea'
    AND (tx_currency = 'ETH'
    OR tx_currency = 'WETH')
    AND price > 0
    AND event_type = 'sale'
    AND block_timestamp >= getdate() - interval '28 days'
    AND block_timestamp < getdate() - interval '21 days'
    GROUP BY contract_address),


    T2 AS
    (SELECT contract_address,
    sum(price) AS eth_sales_sum_t2,
    min(price) AS eth_sales_min_t2,
    avg(price) AS eth_sales_mean_t2,
    count(tx_id) AS eth_sales_count_t2
    FROM ethereum.nft_events
    WHERE event_platform = 'opensea'
    AND (tx_currency = 'ETH'
    OR tx_currency = 'WETH')
    AND price > 0