Sajjadiiifind sold collection 2
    Updated 2024-05-09
    WITH price_tab as (SELECT recorded_hour , close AS flow_price
    FROM flow.price.fact_hourly_prices
    WHERE token = 'Flow'
    AND provider = 'coinmarketcap'
    ),
    basss AS (
    SELECT block_timestamp , tx_id ,event_data:amount AS sale_amount ,initcap(event_data:status) AS status,
    event_data:seller AS seller_address ,
    split(event_data:vaultType , '.')[2] AS seles_by_symbol,
    PARSE_JSON(event_data:nft) AS base
    FROM flow.core.fact_events
    WHERE EVENT_CONTRACT = 'A.097bafa4e0b48eef.FindMarketSale'
    AND EVENT_TYPE = 'Sale'
    -- tx_id = '0c9149e05205135c4fc593e0bb7124a24bc93eee8199737b9e302cd30d000d84'
    ),
    final AS (
    SELECT block_timestamp,tx_id,sale_amount,status,seller_address,seles_by_symbol,
    CASE when seles_by_symbol = 'DapperUtilityCoin' then 1 * sale_amount else flow_price * sale_amount end AS price_usd ,
    nvl(base:fields[0].value:value::string , base[0].value:value::string) AS id,
    nvl(base:fields[1].value:value::string , base[1].value:value::string) AS name,
    nvl(base:fields[3].value:value::string , base[3].value:value::string)AS type,
    nvl(base:fields[5].value:value::variant::string , base[5].value:value::variant::string)AS editionNumber,
    nvl(base:fields[6].value:value::variant::string , base[6].value:value::variant::string)AS totalInEdition,
    nvl(nvl(base:fields[9].value:value:value::string , base[9].value:value:value::string),split(type , '.')[2]) AS collectionName,
    nvl(base:fields[10].value:value:value::variant::string , base[10].value:value:value::variant::string) AS collectionDescription
    FROM basss a
    JOIN price_tab b
    on date_trunc('hour', a.block_timestamp) = b.recorded_hour
    )





    SELECT status,
    (SELECT min(block_timestamp::date) FROM final) AS min_date,
    QueryRunArchived: QueryRun has been archived