Days from listing to a company's first lockup 8-K

Run it yourself

This is the exact query behind the result above. Change a ticker, a date or a column and run it against the warehouse — no account, no key. The no-signup tier is smaller than the one this page was computed on; a query that reaches past it comes back saying which plan runs it.

WITH
    listings AS
    (
        SELECT
            ticker,
            min(listing_date) AS listed
        FROM global_markets.stocks_ipos
        WHERE listing_date >= '2021-01-01'
          AND listing_date <= today()
          AND ticker != ''
          AND ticker NOT IN ('SPCX')
        GROUP BY ticker
    ),
    lockup_filings AS
    (
        SELECT
            arrayJoin(tickers) AS ticker,
            min(filing_date)   AS first_filing
        FROM global_markets.stocks_8k_disclosures
        WHERE filing_date >= '2021-01-01'
          AND (supporting_text ILIKE '%lock-up%' OR supporting_text ILIKE '%lockup%')
        GROUP BY ticker
    )
SELECT
    multiIf(days_after <  90, 'day 0 to 89',
            days_after < 135, 'day 90 to 134',
            days_after < 180, 'day 135 to 179',
            days_after < 210, 'day 180 to 209',
                              'day 210 or later') AS days_after_listing,
    count()                                       AS companies
FROM
(
    SELECT dateDiff('day', l.listed, k.first_filing) AS days_after
    FROM listings AS l
    INNER JOIN lockup_filings AS k ON k.ticker = l.ticker
    WHERE k.first_filing >= l.listed
)
GROUP BY days_after_listing
ORDER BY min(days_after)