flipsidecryptoFlowverse Listing Sales
    Updated 2023-09-26
    with campaign_dates AS (
    select distinct
    start_at,
    end_at
    from bi_analytics.silver.bounties
    where slug ilike 'flowscored%flowverse%'
    ),

    listings AS (
    SELECT
    block_timestamp,
    GET (EVENT_DATA, 'storefrontAddress')::STRING AS lister_address,
    CASE WHEN start_at IS NOT NULL THEN 'Campaigns in-progress'
    ELSE 'No campaigns'
    END AS campaign_category,
    GET (EVENT_DATA, 'nftID')::STRING AS nft_id
    FROM (
    SELECT *
    FROM flow.core.fact_events
    WHERE EVENT_CONTRACT = 'A.4eb8a10cb9f87357.NFTStorefrontV2'
    AND BLOCK_TIMESTAMP >= '2023-05-20'
    ) AS events
    LEFT JOIN campaign_dates d
    ON DATE_TRUNC('WEEK', TO_TIMESTAMP(BLOCK_TIMESTAMP))::DATE BETWEEN DATE_TRUNC('WEEK', TO_TIMESTAMP(start_at))::DATE
    AND DATE_TRUNC('WEEK', TO_TIMESTAMP(end_at))::DATE
    WHERE GET(EVENT_DATA, 'customID') = 'flowverse-nft-marketplace'
    AND EVENT_TYPE = 'ListingAvailable'
    ),
    sales AS (
    SELECT DISTINCT
    ez.block_timestamp,
    ez.seller,
    ez.nft_id,
    CASE
    WHEN ez.CURRENCY = 'FLOW' THEN ez.PRICE * fhp.close
    ELSE ez.PRICE
    Run a query to Download Data