Code to identify PERMNOs with non-overlapping listing periods (there are none)

mult_periods.sql

WITH windows AS (
    SELECT permno, namedt, nameenddt, 
        CASE WHEN lag(nameenddt) OVER w + interval '3 days' > namedt THEN 0
        ELSE 1 END AS new_period
    FROM crsp.stocknames
    WINDOW w AS (PARTITION BY permno ORDER BY namedt, nameenddt)),

window_nums AS (
    SELECT *, sum(new_period) OVER w AS listing_period
    FROM windows
    WINDOW w AS (PARTITION BY permno ORDER BY namedt, nameenddt)),

mult_periods AS (
    SELECT permno, count(DISTINCT listing_period) AS num_periods
    FROM window_nums
    GROUP BY permno)

SELECT permno, listing_period,
    num_periods,
    min(namedt) AS first_date,
    max(nameenddt) AS last_date
FROM window_nums
INNER JOIN mult_periods
USING (permno)
GROUP BY 1, 2, 3
ORDER BY 3 DESC, 1, 2;