Resolved: Find Total Occurrence of a Variable Based on Another Variable in SQL

-- Create Tables
    -- Create X Table
        CREATE TABLE X (
            XID int identity,
            XNumber varchar(100) not null,
            XDescription varchar(100) not null,
            XCode varchar(100) not null,
            VIT varchar(100) not null,
            XLocation varchar(100) not null,
            CONSTRAINT PK_X PRIMARY KEY (XID),
            CONSTRAINT U1_X UNIQUE (XNumber),
            CONSTRAINT U2_X UNIQUE (XCode)
        )
        
    -- Create Y Table
        CREATE TABLE Y (
            YID int identity,
            YNumber varchar(100) not null,
            YDescription varchar(100) not null,
            YDate datetime not null default GetDate(),
            CONSTRAINT PK_Y PRIMARY KEY(YID),
            CONSTRAINT U1_Y UNIQUE(YNumber)
        )
        
    -- Create XY Table
        CREATE TABLE XY (
            XYID int identity,
            XID int not null,
            YID int not null,
            CONSTRAINT PK_XY PRIMARY KEY(XYID),
            CONSTRAINT FK1_XY FOREIGN KEY(XID) REFERENCES X(XID),
            CONSTRAINT FK2_XY FOREIGN KEY(YID) REFERENCES Y(YID)
        )


-- Insert Values in X
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('ASDLKVNAEQRIUNBVLAXKD', 'THDSDF', 'Violet', 2, 'FROP');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('GBOGJMBNNUTHDFHJG', 'TFGSDFG',    'Pink', 20, 'DOOO');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('GKHL', 'SDFGSDF', 'Yellow', 21, 'DSUIO');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('DELKMDTKJGBT', 'HNYDGH', 'Green', 20, 'KABBA');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('WRTOUBHNPSDFIVKM', 'OYIU', 'Aquamarine', 19, 'SANFRIO');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('FGBKMOSEKFVKF', 'DCGTH', 'Red', 8, 'BOB');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('GBIMGIK', 'FTGHJM', 'Mauv', 10, 'CHURCH');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('JLJLH', 'FCTGHKII', 'Goldenrod', 5, 'ZAN');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('YITORKRKG', 'ITRTGJJ', 'Maroon', 7, 'ZERR');
        insert into X (XNumber, XDescription, XCode, VIT, XLocation) values ('QWYTWEGD', 'PRTHJHJUKKM', 'Khaki', 9, 'ZAB');

    -- Insert Values in Y

        insert into Y (YNumber, YDescription, YDate) values ('8e3ac22d07ee88efa9e91fc0830cdee0f1263d82', 'POSTY', '12/22/2019');
        insert into Y (YNumber, YDescription, YDate) values ('735c653fec21726598b47b8c59f7b4a4a8ee3618', 'PUTH 3500', '1/14/2021');
        insert into Y (YNumber, YDescription, YDate) values ('ef3b991a287db1c3bfd9c568972bc35e5c7c6218', 'BTS', '4/4/2021');
        insert into Y (YNumber, YDescription, YDate) values ('a45cec89ba00b77439802b342ce16be4c8ddbee4', 'JLO', '12/3/2019');
        insert into Y (YNumber, YDescription, YDate) values ('747a92666a65947e9f03fd7cf7ad55fdd803a2ab', 'JT', '5/1/2020');
        insert into Y (YNumber, YDescription, YDate) values ('19a5e1d0c75abea249e50997dd1252285c36404c', 'M5', '1/13/2022');
        insert into Y (YNumber, YDescription, YDate) values ('1fe285c2249937f9c5c7608f46be736840ef6007', 'Laroi', '12/12/2021');
        insert into Y (YNumber, YDescription, YDate) values ('e6d3614571648dceae07a4813824904f70bc4bef', 'Biebs', '5/1/2022');
        insert into Y (YNumber, YDescription, YDate) values ('2a7401fd3f68e09f45564ff29fc4b16c1f7e88ba', 'The Weeknd', '2/21/2022');
        insert into Y (YNumber, YDescription, YDate) values ('20a451d4be1894e8f585af47807df6cbe55edfe4', 'Taylor', '2/1/2022');

    -- Insert Values in XY

        insert into XY (XID, YID) values (2, 6);
        insert into XY (XID, YID) values (6, 3);
        insert into XY (XID, YID) values (3, 3);
        insert into XY (XID, YID) values (9, 8);
        insert into XY (XID, YID) values (5, 7);
        insert into XY (XID, YID) values (4, 1);
        insert into XY (XID, YID) values (3, 7);
        insert into XY (XID, YID) values (3, 2);
        insert into XY (XID, YID) values (5, 9);
        insert into XY (XID, YID) values (6, 5);

        SELECT 
            X.XNumber,
            COUNT(XNumber) AS XFreq,
            Y.YNumber
            --COUNT(YNumber) AS YFreq
        FROM XY
        JOIN X ON X.XID = XY.XID
        JOIN Y ON Y.YID = XY.YID
        GROUP BY XNumber, YNumber

        WITH tmp AS (
        SELECT 
            X.XNumber,
            COUNT(Y.YNumber) AS YFreq,
            RANK() OVER(ORDER BY COUNT(Y.YNumber) DESC) AS rnk
        FROM XY
        JOIN X ON X.XID = XY.XID
        JOIN Y ON Y.YID = XY.YID
        GROUP BY X.XNumber
        )
        
        SELECT tmp.*
        FROM tmp
        WHERE rnk = 1