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

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Alexander Farber
Sent: Thursday, July 07, 2011 9:54 AM
To: pgsql-general
Subject: [GENERAL] 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.