SQL Select rows with max difference on a column

Question

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
...

Then I wrote a query with the following logic:

i) Find the difference in volume between every order and its previous order, grouped by each client and order by date. This column will be null for first orders.

ii) Show clients and the maximum difference in volume for each of them.

with cte AS
  (SELECT t.name,
          t.date,
          t.volume,
          t.volume - lag(t.volume) over (
                                         ORDER BY t.name, t.date) AS change
   FROM
     (SELECT c.name,
             o.date,
             sum(o.volume) volume
      FROM orders o
      JOIN client c using (client_id)
      GROUP BY c.name,
               o.date
      ORDER BY c.name,
               o.date) t)
SELECT cte.name,
       max(abs(change))
FROM cte
GROUP BY name

Below is the resulting table.

name | max
------------
james   | 22
melinda | 34
henry   | 25

I am looking to get advice on three things.

a) Is it possible to show the difference with sign? For client ids 29 and 36, the values should be -34 and -25, respectively.

b) Is it possible to show the date as well? I tried to select the date column on CTE, but did not succeed.

c) Would anyone have any general advice on how I can improve the query to make it more performant or readable?

Gordon Linoff · Answer 1 · 2018-11-03T12:12:44.387

Postgres supports DISTINCT ON, which really facilitates your query. In addition, you can simplify the query because window functions and aggregation functions can be combined on the same level:

SELECT DISTINCT ON (co.name) co.*
FROM (SELECT c.name, o.date, SUM(o.volume) as volume,
             LAG(SUM(o.volume)) OVER (PARTITION BY c.name ORDER BY o.date) as prev_volume
      FROM orders o JOIN
           client c 
           USING (client_id)
      GROUP BY c.name, o.date
     ) co
ORDER BY c.name, ABS(volume - prev_volume) DESC

Paul Maxwell · Answer 2 · 2018-11-03T07:56:02.673

0

Kudos to Gordon Linoff for the majority of this query, however it needed some tweaks, in particular the where clause seen below.

SELECT DISTINCT ON (co.name) 
       co.client_id
     , co.name
     , co.DATE
     , (co.volume - co.prev_volume) change
FROM (
     SELECT 
            c.client_id
          , c.name
          , o.DATE
          , SUM(o.volume) AS volume
          , LAG(SUM(o.volume)) OVER (PARTITION BY c.name 
                                     ORDER BY o.DATE) AS prev_volume
     FROM orders o
     INNER JOIN client c USING (client_id)
     GROUP BY
           c.client_id
          , c.name
          , o.DATE
     ) co
WHERE prev_volume IS NOT NULL
ORDER BY
       co.name
     , ABS(co.volume - co.prev_volume)  DESC

Result:

client_id | name    | date       | change
--------: | :------ | :--------- | -----:
       36 | henry   | 2018-01-30 |    -25
       41 | james   | 2018-01-17 |     22
       29 | melinda | 2018-01-29 |    -34

db<>fiddle here

edited Nov 03 '18 at 07:56

answered Nov 03 '18 at 03:19

Paul Maxwell

33,002
3
32
51

Kudos for the awesome query, but I would like to elaborate on my question. Instead of displaying the difference in volume between the two most recent orders, I want to show the largest change in volume from one order to the next. It does not have to be the most recent two. – Nov 03 '18 at 04:58
As an example, if I add a row the order table, `431 | 2018-01-30 | 20 | 29`, the change for client_id 29 in the final table changes to -19 when it should stay at -34. – Nov 03 '18 at 05:00
And the date should change accordingly, instead of showing the date of the most recent order. – Nov 03 '18 at 05:01
Please note the query above really is just a small revision of the one by Gordon Linoff, I had thought you were after something different, but after your comments my only substantive addition is the where clause. – Paul Maxwell Nov 03 '18 at 08:00

SQL Select rows with max difference on a column

2 Answers2