3,256 answered market questions

WITH symbol_issuers AS
(
    SELECT
        ticker,
        issuer_name,
        min(listing_date) AS first_listing
    FROM global_markets.stocks_ipos
    WHERE listing_date >= '2010-01-01'
      AND listing_date <  '2026-10-01'
      AND ticker != ''
      AND ticker NOT IN ('SPCX')
    GROUP BY ticker, issuer_name
),
reused AS
(
    SELECT
        ticker,
        count()            AS issuer_count,
        max(first_listing) AS latest_listing
    FROM symbol_issuers
    GROUP BY ticker
    HAVING issuer_count > 1
)
SELECT
    toString(toYear(latest_listing)) AS listing_year,
    count()                          AS symbols_reassigned,
    round(avg(issuer_count), 2)      AS issuers_per_symbol
FROM reused
GROUP BY listing_year
ORDER BY listing_year