SQL Server : find 3 top games by user

Question

I am trying to get the 3 top games by user for a website.

I am first trying to sum the most played games and then try to group them for each user limiting them to 3.

Here is what I reached so far

SELECT * 
FROM
    (SELECT DISTINCT ACCOUNT_ID FROM account_tran_all) users 
LEFT JOIN 
    (SELECT ACCOUNT_ID, GAME_ID, -SUM (AMOUNT1+AMOUNT2+AMOUNT3) AS ACTIONS 
     FROM admin_all.account_tran_all 
     WHERE TRAN_TYPE = 'GAME_USE' 
       AND DATETIME >= '2020-08-01' 
     GROUP BY ACCOUNT_ID, GAME_ID) games ON games.ACCOUNT_ID = users.ACCOUNT_ID
ORDER BY 
    users.ACCOUNT_ID

It outputs a result similar to this:

+------------+------------------+---------+
| ACCOUNT_ID | GAME_ID          | ACTIONS |
+------------+------------------+---------+
| 611525     | 771              | 0.4     |
| 611525     | 82244            | 11.5    |
| 611525     | 82246            | 4       |
| 611525     | 81400039         | 15      |
| 1000029    | 78884            | 30      |
| 1000029    | 98889            | 100     |
| 1000040    | orjyqvinjm3xsp6y | 120     |
| 1000040    | 82200105         | 180     |
| 1000040    | 82245            | 2       |
| 1000040    | 82254            | 7       |
| 1000040    | 82244            | 8       |
| 1000040    | 82242            | 27      |
| 1000040    | instantgame      | 1       |
| 1000060    | 82264            | 1       |
| 1000060    | 137000058        | 1       |
| 1000060    | 82200064         | 229     |
| 1000083    | 82254            | 109.4   |
| 1000083    | 82264            | 19.5    |
| 1000083    | 82243            | 6       |
| 1000103    | 80758            | 1.5     |
| 1000103    | 82245            | 51      |
| 1000103    | 82200125         | 300     |
| 1000103    | 81400044         | 0.7     |
+------------+------------------+---------+

As a result, I would like to summarize the top games in terms of action for an ACCOUNT_ID but having an output similar to this, concatenating the games in a single field, or leaving each Game_ID in a single column game1, game2, game3 to be able to have more calculation.

+------------+------------------------+
| ACCOUNT_ID | TOP_GAME               |
+------------+------------------------+
| 611525     | 81400039, 82244, 82246 |
| 1000029    | 98889,78884            |
+------------+------------------------+

I add Sample data to be loaded in a sql fiddle:

CREATE TABLE [ACCOUNT_TRAN_ALL] (
    [ACCOUNT_ID] int  NOT NULL,
  [DATETIME] datetime  NOT NULL,
  [TRAN_TYPE] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS  NOT NULL,
  [AMOUNT] numeric(38,18)  NULL,
    [GAME_ID] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
    [BRAND_ID] int  NULL,
 );
 INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001715, '2020-09-23 11:35:48.553', 'GAME_BET', 3.000000000000000000, '82252', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001715, '2020-09-23 11:36:02.363', 'GAME_BET', 1.000000000000000000, '82252', 1);   
  
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:00:21.800', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:00:26.373', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:00:33.770', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:00:40.130', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:00:48.940', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:00:55.586', 'GAME_BET', 1.000000000000000000, '85555', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:01:00.770', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:01:04.490', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:01:09.183', 'GAME_BET', 1.000000000000000000, '80100161', 1);   
INSERT INTO [ACCOUNT_TRAN_ALL]([ACCOUNT_ID], [DATETIME], [TRAN_TYPE], [AMOUNT], [GAME_ID], [BRAND_ID]) VALUES (1001713, '2020-09-22 19:01:22.703', 'GAME_BET', 1.000000000000000000, '80100161', 1);

Sample data, what your data looks like *before* your attempt, will help us help you. Seem like you can simplify what you already have. Also what version of SQL Server? — Thom A, Sep 22 '20 at 16:55
Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/q/18870326/2029983) — Thom A, Sep 22 '20 at 16:56
my question in a nutshell: how can I group the games and concatenate them (3 TOP) for each account_ID? — R_life_R, Sep 22 '20 at 16:57
I know the CONCAT function, but do not believe it answers my question totally, because I am blocked on the grouping more than anything else — R_life_R, Sep 22 '20 at 16:59
I have deleted my answer, as the OP has now supplied consumable sample data, however, it does not conform with their attempt. Thus it has invalidated my answer. — Thom A, Sep 23 '20 at 09:28

score 0 · Answer 1 · answered Sep 27 '20 at 20:02

Easiest way I can think of right now:

Order the games for each account by actions value and assign them a row number. This step is here executed in a common table expression (cte).
Concatenate the top 3 results. Depending on your SQL version you might have access to the function string_agg() (SQL Server 2017 and later), otherwise you can use a pivot statement.

Sample data

create table PlayTime
(
  AccountId int,
  GameId nvarchar(20),
  Actions numeric(5,1)
);

insert into PlayTime (AccountId, GameId, Actions) values
(611525, '771', 0.4),
(611525, '82244', 11.5),
(611525, '82246', 4),
(611525, '81400039', 15),
(1000029, '78884', 30),
(1000029, '98889', 100),
(1000040, 'orjyqvinjm3xsp6y', 120),
(1000040, '82200105', 180),
(1000040, '82245', 2),
(1000040, '82254', 7),
(1000040, '82244', 8),
(1000040, '82242', 27),
(1000040, 'instantgame', 1),
(1000060, '82264', 1),
(1000060, '137000058', 1),
(1000060, '82200064', 229),
(1000083, '82254', 109.4),
(1000083, '82264', 19.5),
(1000083, '82243', 6),
(1000103, '80758', 1.5),
(1000103, '82245', 51),
(1000103, '82200125', 300),
(1000103, '81400044', 0.7);

Solution

SQL Server 2017 and later solution

with cte as
(
  select pt.AccountId,
         pt.GameId,
         row_number() over(partition by pt.AccountId order by pt.Actions desc) as RowNum
  from PlayTime pt
)
select cte.AccountId,
       string_agg(cte.GameId, ',') as TopGames
from cte
where cte.RowNum <= 3
group by cte.AccountId
order by cte.AccountId;

Pre SQL Server 2017 solution

with cte as
(
  select pt.AccountId,
         pt.GameId,
         row_number() over(partition by pt.AccountId order by pt.Actions desc) as RowNum
  from PlayTime pt
)
select p.AccountId,
       p.[1] + case when p.[2] is not null then ',' + p.[2] else '' end
             + case when p.[3] is not null then ',' + p.[3] else '' end as TopGames
from cte
pivot (max(cte.GameId) for cte.RowNum in ([1], [2], [3])) p
order by p.AccountId;

Result

AccountId TopGames
--------- --------------------------------
611525    81400039,82244,82246
1000029   98889,78884
1000040   82200105,orjyqvinjm3xsp6y,82242
1000060   82200064,82264,137000058
1000083   82254,82264,82243
1000103   82200125,82245,80758

Fiddle

SQL Server : find 3 top games by user

1 Answers1