Ranking users with weekly date period and listing all first ranked users

Question

I have a table called coupons with schema below:

CREATE TABLE "public"."coupons" (
   "id" int4 NOT NULL,
    "suprise" bool NOT NULL DEFAULT false,
    "user_id" int4 NOT NULL,
    "start" timestamp NOT NULL,
    "win_price" numeric(8,2) NOT NULL DEFAULT 0::numeric,
    "fold" int4 NOT NULL DEFAULT 3,
    "pay" numeric(8,2) NOT NULL DEFAULT 0::numeric,
    "rate" numeric(8,2) NOT NULL DEFAULT 0::numeric,
    "win" varchar(255) NOT NULL DEFAULT 'H'::character varying COLLATE "default",
    "end" timestamp NOT NULL,
    "win_count" int4 NOT NULL DEFAULT 0,
    "match_count" int4 NOT NULL DEFAULT 0,
    "played" bool NOT NULL DEFAULT false,
    "created_at" timestamp NOT NULL,
    "updated_at" timestamp NOT NULL
)
WITH (OIDS=FALSE);

To rank users over win_price weekly I wrote the query below to get top 5 between 27-07-2015 and 03-08-2015:

SELECT ROW_NUMBER() OVER(ORDER BY sum(win_price) DESC) AS rnk,
       sum(win_price) AS win_price, user_id,
       min(created_at) min_create
FROM coupons
WHERE played = true AND win = 'Y'
AND created_at BETWEEN '27-07-2015' AND '03-08-2015'
GROUP BY user_id
ORDER BY rnk ASC
LIMIT 5;

I'm looking to a new query that lists first ranked users basis on weekly but in given date period.
I.e : for the period between 01-09-2015 and 30-09-2015:

rnk - win_price - user_id - min_create  
 1  - 1.52      - 1       - ...........  (first week)
 1  - 10.92     - 2       - ...........  (send week)
 1  - 11.23     - 1       - ...........  (third week and so on)

Erwin Brandstetter · Accepted Answer · 2023-01-09T06:07:54.720

SELECT *
FROM  (
   SELECT date_trunc('week', created_at) AS week
        , rank() OVER (PARTITION BY date_trunc('week', created_at)
                       ORDER BY sum(win_price) DESC NULLS LAST) AS rnk
        , sum(win_price) AS win_price
        , user_id
        , min(created_at) min_create
   FROM   coupons
   WHERE  played = true
   AND    win = 'Y' AND created_at BETWEEN '27-07-2015' AND '03-08-2015'
   GROUP  BY 1, 4  -- reference to 1st & 4th column
   ) sub
WHERE  rnk = 1
ORDER  BY week;

This returns the winning users per week - the ones with the greatest sum(win_price).

I use rank() instead of row_number(), since you did not define a tiebreaker for multiple winners per week.

The added clause NULLS LAST prevents NULL values from sorting on top in descending order (DESC) - if you should have NULL. See:

Sort by column ASC, but NULL values first?

The week is represented by the starting timestamp, you can format that any way you like with to_char().

The key feature of this query: you can use window functions over aggregate functions. See:

Postgres window function and group by exception

Consider the sequence of events in a SELECT query:

Best way to get result count before LIMIT was applied

Ranking users with weekly date period and listing all first ranked users

1 Answers1