SELECT a_bidder.tx_id, a_bidder.block_timestamp,

a_bidder.attribute_value AS bidder,

REGEXP_SUBSTR(a_bid.attribute_value, $$[^\d*][\S+]*$$) AS bid_currency,

CAST(REGEXP_SUBSTR(a_bid.attribute_value, $$^\d*$$) AS INTEGER) AS bid_amount,

SPLIT(a_bundled_txs.attribute_value, ',') AS bundled_txs

FROM

osmosis.core.fact_msg_attributes AS a_bidder,

osmosis.core.fact_msg_attributes AS a_bid,

osmosis.core.fact_msg_attributes AS a_bundled_txs

WHERE a_bidder.block_timestamp >= TO_DATE('2024-05-17')

AND a_bidder.msg_type = 'auction_bid'

AND a_bidder.attribute_key = 'bidder'

AND a_bid.attribute_key = 'bid' AND a_bidder.tx_id = a_bid.tx_id AND a_bidder.msg_index = a_bid.msg_index AND (a_bidder.msg_group = a_bid.msg_group OR a_bidder.msg_group IS NULL AND a_bid.msg_group IS NULL)

AND a_bundled_txs.attribute_key = 'bundled_txs' AND a_bidder.tx_id = a_bundled_txs.tx_id AND a_bidder.msg_index = a_bundled_txs.msg_index AND (a_bidder.msg_group = a_bundled_txs.msg_group OR a_bidder.msg_group IS NULL AND a_bundled_txs.msg_group IS NULL)

),

bids AS (

SELECT tx_id, block_timestamp, bundled_txs, bidder,

bid_amount / POW(10, decimal) AS bid_amount,

b.bid_amount * price / POW(10, decimal) AS bid_amount_usd,

bid_currency, symbol AS bid_symbol

FROM bids_0 b

JOIN osmosis.price.ez_prices ON currency = bid_currency

AND recorded_hour = TRUNC(block_timestamp, 'hour')

JOIN osmosis.core.dim_tokens ON address = bid_currency

),

metrics AS (

SELECT 'Total Bids' AS metric, count(*) AS value FROM bids UNION

SELECT 'Unique Bidders' AS metric, count(DISTINCT bidder) AS value FROM bids UNION

SELECT 'Total Profit $USD' AS metric, ROUND(sum(bid_amount_usd)) AS value FROM bids UNION

SELECT 'Total Bundled Transactions' AS metric, sum(ARRAY_SIZE(bundled_txs)) AS value FROM bids

SELECT *

FROM metrics

ORDER BY metric