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

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

Open in new window

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!
Kenni LarsenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
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
Kenni LarsenAuthor Commented:
Hi fyed

Thanks for asking.

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
Dale FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kenni LarsenAuthor Commented:
Yes, exactly!
0
pcelbaCommented:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Commented:
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
pcelbaCommented:
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
Kenni LarsenAuthor Commented:
It works exactly as expected. Thank you for all your assistance, it's greatly appreciated!
0
pcelbaCommented:
Thanks for the points. My opinion: you should split a part of them among others.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.