3,293 answered market questions

WITH ipos AS
(
    SELECT
        ticker,
        any(issuer_name)                      AS issuer,
        max(toDate(listing_date))             AS listed_on,
        max(toFloat64(final_issue_price))     AS issue_price,
        max(toFloat64(total_offer_size))      AS offer_size
    FROM global_markets.stocks_ipos
    WHERE ipo_status IN ('history', 'new')
      AND toDate(listing_date) >= today() - 365
      AND toDate(listing_date) <= today() - 3
      AND ticker NOT IN ('SPCX')
      AND ifNull(issuer_name, '') NOT ILIKE '%acquisition%'
    GROUP BY ticker
    HAVING issue_price > 0 AND offer_size >= 100000000
)
SELECT
    i.ticker                                              AS ticker,
    i.issuer                                              AS issuer,
    toString(i.listed_on)                                 AS listed_on,
    round(i.issue_price, 2)                               AS issue_price,
    round(d.first_open, 2)                                AS first_open,
    round(d.first_close, 2)                               AS first_close,
    round((d.first_open / i.issue_price - 1) * 100, 1)    AS open_vs_issue_pct,
    round(i.offer_size / 1e9, 2)                          AS offer_size_billions
FROM ipos AS i
INNER JOIN
(
    SELECT
        ticker,
        min(toDate(date))                 AS first_session,
        argMin(toFloat64(open), date)     AS first_open,
        argMin(toFloat64(close), date)    AS first_close
    FROM global_markets.stocks_daily_aggs
    WHERE date >= today() - 400
    GROUP BY ticker
) AS d ON d.ticker = i.ticker
WHERE d.first_session BETWEEN i.listed_on - 1 AND i.listed_on + 5
ORDER BY i.offer_size DESC
LIMIT 12