CODEWITHSUNDEEP
sqlpostgresql
Hsiu Dai
Hsiu Dai

Reputation: 1323

How to count tags by user in a day

I have a table that tracks tags users create. The important columns for this question, I believe, would be user_id (of who made the tag) and created_at.

With some searching I found how to use date_trunc to turn created_at into just a day so that we can group by that.

However, I can only get this to give me the count of total tags for a day. What I want to do is group by both user and day, such that I can see how many tags each user created each day, and with some ordering figure out what the most number of tags a user has created in a day ever is.

I'm using postgres 9.1.6.

Any help? Thanks in advance.

EDIT: What I have started with, mainly a shot in the dark, is:

select user_id,
       date_trunc('day', created_at) as "Day",
       count(*) as "numtags"
  from tags
  group by user_id, created_at
  order by numtags desc;

And the result is:

 user_id |         Day         | numtags 
---------+---------------------+---------
      1  | 2012-01-08 00:00:00 |    5719
      1  | 2012-01-31 00:00:00 |    3239
      1  | 2011-12-01 00:00:00 |    1868
      1  | 2011-12-01 00:00:00 |    1772
      1  | 2011-12-01 00:00:00 |    1756

So clearly the grouping isn't working as desired, the user IDs are all the same (which I have confirmed manually they should not be).

I would like to see something like (numbers don't match above, just an example):

 user_id |         Day         | numtags 
---------+---------------------+---------
      1  | 2012-01-08 00:00:00 |    100
      4  | 2012-01-31 00:00:00 |    90
      1  | 2011-12-12 00:00:00 |    80
      2  | 2011-12-12 00:00:00 |    77
      2  | 2011-12-13 00:00:00 |    76

i.e. The same user will appear many times, the same date will appear many times - in fact every user_id/Day combination will show up (once each) as long as the user made at least one tag on a day, and numtags will show how many tags were made by that user on that day.

Upvotes: 1

Views: 89

Answers (1)

sufleR
sufleR

Reputation: 2973

As I suggested:

select user_id,
   created_at::date as "Day",
   count(*) as "numtags"
from tags
group by user_id, created_at::date
order by numtags desc;

created_at::date is casting timestamp to date type.

Your mistake was to group by createdate which is timestamp with different time part but in result you had truncated timestamp.

Upvotes: 2

Related Questions