USE sec_fsnds;
GO

/*
============================================================================
 dbo.usp_sec_list_peers
============================================================================
 List other companies in the same SIC code as a target CIK.

 The target's SIC is resolved from its most recent filing (max filed).
 Peers are listed with one row per distinct cik (deduped), showing the
 latest name and filing date observed.

 Parameters
 ----------
   @cik  target company (required)
   @top  max peers to return (default 100)

 Notes
 -----
 * sec_fsnds has no index on sub.sic -- this is a scan. Sub is 820K
   rows and fits in memory, so the scan is sub-second.
============================================================================
*/

IF OBJECT_ID('dbo.usp_sec_list_peers', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_sec_list_peers;
GO

CREATE PROCEDURE dbo.usp_sec_list_peers
    @cik INT,
    @top INT = 100
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sic SMALLINT;
    SELECT TOP (1) @sic = sic
    FROM dbo.sub
    WHERE cik = @cik AND sic IS NOT NULL
    ORDER BY filed DESC;

    IF @sic IS NULL
    BEGIN
        RAISERROR('Could not resolve a SIC for cik=%d (no filings or sic is null)', 16, 1, @cik);
        RETURN;
    END;

    SELECT TOP (@top)
         @sic                                AS peer_sic
        ,p.cik
        ,p.name
        ,p.most_recent_filed
        ,p.filing_count
    FROM (
        SELECT
             s.cik
            ,MAX(s.name)  AS name
            ,MAX(s.filed) AS most_recent_filed
            ,COUNT(*)     AS filing_count
        FROM dbo.sub s
        WHERE s.sic = @sic
          AND s.cik <> @cik
        GROUP BY s.cik
    ) p
    ORDER BY p.most_recent_filed DESC, p.filing_count DESC;
END;
GO

/*
============================================================================
 Example call
============================================================================

-- Microsoft's peers
EXEC dbo.usp_sec_list_peers @cik = 789019;
*/
