DiamondFlowty Daily (Non-Offers + Offers) with prices 7day,14day,30day copy
    Updated 2023-09-06
    WITH
    daily_prices AS (
    SELECT
    token,
    date_trunc('day', recorded_hour) AS day,
    AVG(close) AS price
    FROM flow.core.fact_hourly_prices
    WHERE recorded_hour >= '2023-01-01'
    GROUP BY token, day
    ),
    CombinedData AS (
    SELECT
    TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', BLOCK_TIMESTAMP)) AS day,
    COUNT(*) AS "Total Transactions",
    COUNT(DISTINCT event_data:buyer::string) AS "Unique Buyers",
    COUNT(DISTINCT event_data:seller::string) AS "Unique Sellers",
    SUM(event_data:salePrice::float) AS total_sale_price,
    SUM(event_data:commissionAmount::float) AS total_commission,
    SUM(COUNT(*)) OVER (ORDER BY TO_DATE(CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', BLOCK_TIMESTAMP)) ASC) AS "Cumulative Transactions"
    FROM
    FLOW.CORE.FACT_EVENTS
    WHERE
    (
    EVENT_CONTRACT IN ('A.3cdbb3d569211ff3.NFTStorefrontV2')
    AND EVENT_TYPE = 'ListingCompleted'
    AND TX_SUCCEEDED = TRUE
    AND EVENT_DATA:purchased::string = 'true'
    )
    OR
    (
    EVENT_CONTRACT IN ('A.b8ea91944fd51c43.OffersV2')
    AND EVENT_TYPE = 'OfferCompleted'
    AND BLOCK_TIMESTAMP >= CONVERT_TIMEZONE('America/Los_Angeles', 'UTC', '2023-01-01')
    AND tx_succeeded = 'true'
    AND EVENT_DATA::string LIKE '%0x6590f8918060ef13%'
    AND EVENT_DATA:purchased LIKE '%true%'
    Run a query to Download Data