Sajjadiiifind sold collection 2
Updated 2024-05-09
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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