New listings landing on a symbol that already had history

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.

SELECT
    toString(toYear(i.listing_day))                          AS listing_year,
    count()                                                  AS listings,
    countIf(f.first_print < subtractDays(i.listing_day, 90))  AS symbols_with_earlier_history
FROM
(
    SELECT
        ticker,
        min(toDate(listing_date)) AS listing_day
    FROM global_markets.stocks_ipos
    WHERE listing_date >= '2016-01-01'
      AND listing_date < today()
    GROUP BY ticker
) AS i
INNER JOIN
(
    SELECT
        ticker,
        min(date) AS first_print
    FROM global_markets.stocks_daily_aggs
    GROUP BY ticker
) AS f ON f.ticker = i.ticker
GROUP BY listing_year
ORDER BY listing_year