?
Solved

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

Posted on 2014-07-28
9
Medium Priority
?
340 Views
Last Modified: 2014-07-29
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!
0
Comment
Question by:Kenni Larsen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 48

Expert Comment

by:Dale Fye
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

by:Kenni Larsen
ID: 40225544
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
 
LVL 48

Expert Comment

by:Dale Fye
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
Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

 

Author Comment

by:Kenni Larsen
ID: 40225609
Yes, exactly!
0
 
LVL 42

Accepted Solution

by:
pcelba earned 2000 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

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
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 42

Expert Comment

by:pcelba
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

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

Expert Comment

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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question