SELECT

block_timestamp AS event_date,

type AS event_type,

nft_item_address AS nft_address,

marketplace_address AS market_place,

marketplace_fee_address AS market_place_fee_address,

owner_address AS initial_owner,

collection_address AS collection_address,

prev_owner,

PARSE_JSON(content_onchain) AS content,

content:uri::STRING AS metadata_uri,

TRIM(

REGEXP_REPLACE(

SPLIT_PART(content:uri::STRING, '/', 5),

'\.json$',

''

)

) AS username,

CAST(content:initial_min_bid / 1e9 AS NUMBER) AS initial_min_bid,

CAST(content:min_bid / 1e9 AS NUMBER) AS min_bid,

CAST(content:max_bid / 1e9 AS NUMBER) AS max_bid,

content:min_bid_step::NUMBER AS min_bid_step,

content:duration::NUMBER AS duration_seconds,

content:min_extend_time::NUMBER AS min_extend_time_seconds,

CAST(content:bid / 1e9 AS NUMBER) AS bid_amount,

TO_TIMESTAMP_NTZ(content:bid_ts::NUMBER) AS bid_ts,

TO_TIMESTAMP_NTZ(content:end_time::NUMBER) AS end_time,

content:bidder_address::STRING AS bidder_address,

content:beneficiar::STRING AS beneficiary_address,

fact_nft_events_id,

--DATEDIFF('day', bid_ts, TO_TIMESTAMP_NTZ(content:end_time::NUMBER)) AS bid_days_int

CASE

WHEN DATEDIFF('day', bid_ts, TO_TIMESTAMP_NTZ(content:end_time::NUMBER)) < 1

THEN DATEDIFF('hour', bid_ts, TO_TIMESTAMP_NTZ(content:end_time::NUMBER))

ELSE DATEDIFF('day', bid_ts, TO_TIMESTAMP_NTZ(content:end_time::NUMBER))