2,358 answered market questions

SELECT
    k.ticker                                                   AS ticker,
    toString(toDate(k.filing_date))                            AS filed_on,
    concat(formatDateTime(toDate(k.filing_date), '%b'), ' ',
           toString(toDayOfMonth(toDate(k.filing_date))))      AS filed_label,
    any(k.form_type)                                           AS form_type,
    max(countSubstringsCaseInsensitive(k.items_text, 'lock-up')
        + countSubstringsCaseInsensitive(k.items_text, 'lockup')) AS lockup_mentions,
    any(k.filing_url)                                          AS filing_url
FROM global_markets.stocks_8k_text AS k
WHERE k.ticker IN
(
    SELECT ticker
    FROM global_markets.stocks_ipos
    WHERE ticker != ''
      AND ticker NOT IN ('SPCX')
      AND toDate(listing_date) BETWEEN today() - 730 AND today() - 30
      AND ipo_status NOT IN ('pending', 'postponed', 'withdrawn', 'rumor', 'direct_listing_process')
      AND positionCaseInsensitive(issuer_name, 'acquisition') = 0
      AND final_issue_price > 0
)
  AND toDate(k.filing_date) >= today() - 180
  AND (positionCaseInsensitive(k.items_text, 'lock-up') > 0
       OR positionCaseInsensitive(k.items_text, 'lockup') > 0)
GROUP BY k.ticker, k.accession_number, k.filing_date
ORDER BY k.filing_date DESC, k.ticker
LIMIT 25