2,358 answered market questions

SELECT
    ticker,
    company,
    toString(listed_d)                                          AS listed_on,
    concat(formatDateTime(listed_d, '%b'), ' ',
           toString(toDayOfMonth(listed_d)))                    AS listed_label,
    toString(unlock_d)                                          AS unlock_180d,
    concat(formatDateTime(unlock_d, '%a, %b'), ' ',
           toString(toDayOfMonth(unlock_d)))                    AS unlock_label,
    dateDiff('day', today(), unlock_d)                          AS days_away,
    concat(monthName(today()), ' ',
           toString(toDayOfMonth(today())), ', ',
           toString(toYear(today())))                           AS computed_on
FROM
(
    SELECT
        ticker,
        argMax(issuer_name, _ingest_time)                       AS company,
        argMax(toDate(listing_date), _ingest_time)              AS listed_d,
        addDays(listed_d, 180)                                  AS unlock_d
    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
)
ORDER BY unlock_d, ticker