price_vs_offer

SELECT
    i.ticker                                                AS ticker,
    round(i.offer_px, 2)                                    AS offer_price,
    round(p.last_px, 2)                                     AS last_close,
    round((p.last_px / i.offer_px - 1) * 100, 1)            AS vs_offer_pct,
    concat(monthName(p.last_d), ' ',
           toString(toDayOfMonth(p.last_d)), ', ',
           toString(toYear(p.last_d)))                      AS priced_as_of
FROM
(
    SELECT
        ticker,
        argMax(toFloat64(final_issue_price), _ingest_time)  AS offer_px
    FROM global_markets.stocks_ipos
    WHERE ticker != ''
      AND ticker NOT IN ('SPCX')
      AND toDate(listing_date) BETWEEN today() - 180 AND today() - 90
      AND ipo_status NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
      AND positionCaseInsensitive(issuer_name, 'acquisition') = 0
      AND final_issue_price > 0
    GROUP BY ticker
) AS i
INNER JOIN
(
    SELECT
        ticker,
        toFloat64(argMax(close, date))                      AS last_px,
        max(date)                                           AS last_d
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 10
      AND close > 0
    GROUP BY ticker
) AS p ON p.ticker = i.ticker
ORDER BY vs_offer_pct DESC, ticker