SELECT livequery.live.udf_api(

'https://raw.githubusercontent.com/mehdimarjan/Opensea/refs/heads/main/listing.json'

) AS response

),

offer_api as (

SELECT livequery.live.udf_api(

'https://raw.githubusercontent.com/mehdimarjan/Opensea/refs/heads/main/offer.json'

) AS response

),

offer_data AS(

to_timestamp(value:event_timestamp) AS offer_time,

value:order_hash AS order_hash,

to_timestamp(value:start_date) AS start_date,

to_timestamp(value:expiration_date) AS expiration_date,

value:maker AS maker,

value:order_type as type,

value:payment:quantity/pow(10,18) AS offer_amount,

FROM offer_api, LATERAL FLATTEN (input => response:data)

GROUP BY ALL

ORDER BY 1 DESC

SELECT

offer_time::date AS time,

count(order_hash) AS transactions,

count(DISTINCT maker) AS offer_makers,

SUM(offer_amount) AS offer_amount

FROM offer_data

group BY 1