How to calculate MAX(SUM()) in postgresql in particular to query below

I am trying to get MAX(SUM()) in PostgreSQL, which is not possible directly in PostgreSQL. I am trying to get the result by any method, but unable to get the desired result. My table is like this –

CREATE TABLE user_transactions (
    id integer NOT NULL,
    user_id bigint,
    credit_rupee numeric,
    created_at timestamp without time zone,
);

This is sample data

id      credit_rupee user_id    date 
1       30           72873      2015-03-25
2       10           76296      2015-03-25 
3       3            73130      2015-03-26 
4       2            73142      2015-03-26 
5       8.44         73145      2015-03-26
6       2            72911      2015-03-26 
7       5            73158      2015-03-26
8       10           73067      2015-03-26
9       2            76296      2015-03-26
10      1            76296      2015-03-27
11      5.5          73042      2015-03-27
12      2            73042      2015-03-27

In this i want to get the user with highest sum of credit_rupee for a particular day.

The result i want is

user_id  max date
72873    30  2015-03-25
73067    10  2015-03-26
73042    5.5 2015-03-27

The problem is i cannot select all three user_id,max(),date without GROUP BY date and user_id which makes it difficult to arrive at the result mentioned above.

i have tried many combinations like

SELECT MAX(sum),user_id,date FROM(
    SELECT SUM(credit_rupee),user_id,DATE(created_at) FROM user_transactions 
    GROUP BY user_id,DATE(created_at) 
    ORDER BY DATE(created_at)) as m 
GROUP BY date ORDER BY date;

SELECT DISTINCT(user_id),max,date from(
    SELECT MAX(sum),user_id,date FROM(
        SELECT SUM(credit_rupee),user_id,DATE(created_at) FROM user_transactions
        GROUP BY user_id,DATE(created_at) ORDER BY DATE(created_at)) as m 
    GROUP BY DATE,user_id ORDER BY date) as x 
GROUP BY date,user_id

Etc., But no matter however tried unable to get the result which i wanted. Can anybody help me how to arrive at the result? Thanks

Answer

This query will give you exactly what you want:

SELECT user_id,
       credit_rupee,
       created_at from
  (SELECT
     rank() OVER (PARTITION BY created_at ORDER BY credit_rupee DESC) AS rank,
     user_id, credit_rupee, created_at
     FROM user_transactions ) sq
WHERE rank=1;

Advertisement

Answer