Postgres Calculate Difference Using Window Functions

Question

I apologize in advance if the question is too basic. Window functions are fun and challenging at the same time!

I have two Postgres tables such as below called client and order.

id | name
------------
41 | james
29 | melinda
36 | henry
...

id | date | volume | client_id
------------------------------
328 | 2018-01-03 | 16 | 41
411 | 2018-01-29 | 39 | 29
129 | 2018-01-13 | 73 | 29
542 | 2018-01-22 | 62 | 36
301 | 2018-01-17 | 38 | 41
784 | 2018-01-08 | 84 | 29
299 | 2018-01-10 | 54 | 36
300 | 2018-01-10 | 18 | 36
178 | 2018-01-30 | 37 | 36
...

a) How can I write a query to find the largest difference in order volume for each client? For example, client_id = 36 should show (54 + 18) - 37 = -35. This is because orders placed on the same day by the same client should count as one order.

b) How can I find the difference in volume between the two most recent orders for each client? For example, client_id = 29 should show 39 - 73 = -34

I dont know if T-SQL query will help you?? I can tell a query to get your result — El.Hum
– El.Hum, Commented Nov 2, 2018 at 19:11
Thanks for the comment. I'm not very familiar with T-SQL, but I'd prefer to approach this with postgres syntax and window functions. Do you think you can help me out? — user10442632
– user10442632, Commented Nov 2, 2018 at 19:14
I think sql query may help. ok tell the exact formula you want to write a query for that. I mean instead of (54+..... tell a formula like (MaxV - MinV .... — El.Hum
– El.Hum, Commented Nov 2, 2018 at 19:18
For each unique client_id: Max(total volume for each day) - Min(total volume for each day), does that help? — user10442632
– user10442632, Commented Nov 2, 2018 at 19:20
If my answer helped you so it woud be easy to query the part B. — El.Hum
– El.Hum, Commented Nov 2, 2018 at 19:43

El.Hum · Accepted Answer · 2018-11-02 19:29:25Z

5

Well here is a T-SQL.
For this formula as you said ---> Max(total volume each day) - Min(total volume each day)
May help you.

SELECT (X.Max(SumV)-X.Min(SumV))
From (
     SELECT Client_Id,Date,SUM(Volume) AS SumV
     FROM Orders
     GROUP BY Client_id,Date
     ) X

Group by X.Client_Id

answered Nov 2, 2018 at 19:29

El.Hum

1,4893 gold badges15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Calculate Difference Using Window Functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related