Solved

# Count the number of games that are being played on average per user

Posted on 2014-07-28
324 Views
Hi Experts

I have the following PostgreSQL code which returns the number of created games per user.

``````SELECT "user", COUNT("user") AS games
FROM
(
SELECT "games"."player_id" AS "user" FROM "games"
UNION ALL
SELECT "games"."opponent_id" FROM "games"
) AS t
WHERE "user" IS NOT NULL
GROUP BY "user"
ORDER BY games DESC
``````
Output:
USER      GAMES
56997     3642
44801     3155
3682       3134
58255     2673
64718     2384
...

However, I would like to know the number of games that are being played on average per user. I've tried various solutions without success. Can you help? Thanks!
0
Question by:Kenni Larsen
• 3
• 3
• 2
• +1

LVL 47

Expert Comment

ID: 40225535
I'm not sure I understand the question.  Do you want to know how many games the user has active on any particular day, or the average number of games they have played on a given day?

do you have a user game start and end date field?
0

Author Comment

ID: 40225544
Hi fyed

I just want to know how many games each user plays on average. Regardless of date.
Like the sum of all games divided by the total number of users.

I hope it made â€‹â€‹it more clear. Thanks!
0

LVL 47

Expert Comment

ID: 40225563
So, I think what you are saying, is (for the sample data):

56997     3642
44801     3155
3682       3134
58255     2673
64718     2384

What you want is the number: 2997.6

Which is (3642+3155+3134+2673+2384)/5

Is that correct?
0

Author Comment

ID: 40225609
Yes, exactly!
0

LVL 41

Accepted Solution

pcelba earned 500 total points
ID: 40225670
So in SQL:
``````SELECT AVG(games) FROM
(SELECT "user", COUNT("user") AS games
FROM
(
SELECT "games"."player_id" AS "user" FROM "games"
UNION ALL
SELECT "games"."opponent_id" FROM "games"
) AS t
WHERE "user" IS NOT NULL
GROUP BY "user"
) AS g
``````
0

LVL 31

Expert Comment

ID: 40226573
Just out of curiosity, assuming games has a playerid of 58255 with an opponentid of 56997 and  also has a playerid of 56997 with an opponentid of 58255, wouldn't that show as two games when, in fact, it's only one using union all?
0

LVL 41

Expert Comment

ID: 40226605
That's OK. Each player plays the game thus two players playing together have average 1 game played not 0.5. Each of them can say "I have been playing one game".

The number of matches is the half if we assume two player games, of course.
0

Author Closing Comment

ID: 40226695
It works exactly as expected. Thank you for all your assistance, it's greatly appreciated!
0

LVL 41

Expert Comment

ID: 40226755
Thanks for the points. My opinion: you should split a part of them among others.
0