This query is for specific sports - what changes do I have to make for getting results of all sports and the top 3 alone in each of the sport

See more:

SQL

SQL-Server

Individual sports ---- 30 day ----- Online users

SELECT RoomId, Name, ISNULL((SELECT COUNT(ChatUserLogId) From ChatUserLog WHERE RoomId = Room.RoomId AND LoggedInTime >= DATEADD(DAY,-30,GETDATE())), 0) TotalUsers, ISNULL((SELECT COUNT(LoggedInUserId) From LoggedInUser WHERE RoomID = Room.RoomID), 0) UserOnline From Room Where Sports = @Sports ORDER BY UserOnline DESC";

Individual sports ---- 30 day ----- Total users

SELECT RoomId, Name, ISNULL((SELECT COUNT(ChatUserLogId) From ChatUserLog WHERE RoomId = Room.RoomId AND LoggedInTime >= DATEADD(DAY,-30,GETDATE())), 0) TotalUsers, ISNULL((SELECT COUNT(LoggedInUserId) From LoggedInUser WHERE RoomID = Room.RoomID), 0) UserOnline From Room Where Sports = @Sports ORDER BY TotalUsers DESC";

What changes do I have to make to the query for getting results of all sports and the top 3 alone in each of the sport?

The Table details are

CREATE TABLE [dbo].[Room] (
        [RoomID]        INT           IDENTITY (6666, 1) NOT NULL,
        [Name]          VARCHAR (100) NOT NULL,
        [Sports]        VARCHAR (50)  NOT NULL,
        [CreatedDate]   DATETIME      DEFAULT (getdate()) NOT NULL,
        [CreatedBy]     VARCHAR (50)  NOT NULL,
        [CreatedUserID] INT           NOT NULL,
        PRIMARY KEY CLUSTERED ([RoomID] ASC)
    );
    
    CREATE TABLE [dbo].[ChatUserLog] (
        [ChatUserLogId] BIGINT   IDENTITY (1, 1) NOT NULL,
        [MId]           INT      NOT NULL,
        [RoomId]        INT      NOT NULL,
        [LoggedInTime]  DATETIME DEFAULT (getdate()) NOT NULL,
        [LogOutTime]    DATETIME DEFAULT (getdate()) NULL,
        PRIMARY KEY CLUSTERED ([ChatUserLogId] ASC)
    );
     
    CREATE TABLE [dbo].[LoggedInUser] (
        [LoggedInUserID] INT           IDENTITY (1, 1) NOT NULL,
        [MId]            INT           NOT NULL,
        [RoomID]         INT           NOT NULL,
        [NickName]       VARCHAR (50)  NOT NULL,
        [Icon]           VARCHAR (MAX) NOT NULL,
        CONSTRAINT [PK_LoggedInUser] PRIMARY KEY CLUSTERED ([LoggedInUserID] ASC)
    );

What I have tried:

I tried 2 left joins but it just multiplies the 3 tables and gives the same results coming up n number of times. So, it doesn't work. Help me to just modify this