Search Postgresql Archives

Re: Trying to find miss and mister of the last month with highest rating

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Alexander Farber
Sent: Thursday, July 07, 2011 9:54 AM
To: pgsql-general
Subject:  Trying to find miss and mister of the last month with
highest rating

# select id, nice, last_rated from pref_rep where nice=true
  order by last_rated desc limit 7;
           id           | nice |         last_rated
------------------------+------+----------------------------
 OK152565298368         | t    | 2011-07-07 14:26:38.325716
 OK452217781481         | t    | 2011-07-07 14:26:10.831353
 OK524802920494         | t    | 2011-07-07 14:25:28.961652
 OK348972427664         | t    | 2011-07-07 14:25:17.214928
 DE11873                | t    | 2011-07-07 14:25:05.303104
 OK335285460379         | t    | 2011-07-07 14:24:39.062652
 OK353639875983         | t    | 2011-07-07 14:23:33.811986

And I know their gender:

# select id, female from pref_users limit 7;
       id       | female
----------------+--------
 OK351636836012 | f
 OK366097485338 | f

I'm trying to construct 2 queries -
one to find the female user with
highest count of ratings for the last month (not just for the last 30 days -
and this condition is already killing me) and the same for non-female users.

Any help please? SQL is so hard sometimes.


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

For the dates you basically need to figure out the correct year, month and
day values to represent the prior month using the current month as a base
(then build a "date string" and cast it to an actual date).  Hint; the last
day of the prior month is one day before the first day of the current month.
Use a WITH or sub-query to select only ratings between the dates while
joining the gender table.  You can also perform your "COUNT(*) at this level
and group by ID, Gender.

In the main query try to use the "RANK()" window function with an ORDER BY
on the "count" field and partitioned by "gender".  You can probably put this
in the HAVING clause and check for "(RANK(*) OVER ...) = 1"

Not totally sure on the syntax and don't have time to load up some test data
and try different permutations but this should at least get you headed in
the right direction if no-one else comes along and provides a more detailed
explanation.

David J.





-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux