min and max with and without windowing function

Question

create table dt
(
  id varchar(20),
  user_id int,
  name varchar(20),
  td DATE,
  amount float
);

INSERT INTO dt VALUES('blah',1, 'Rodeo', '2018-01-20', 10.12);
INSERT INTO dt VALUES('blahblah',1, 'Rodeo', '2019-01-01', 40.44);
INSERT INTO dt VALUES('sas',2, 'Janice', '2018-02-05', 18.18);
INSERT INTO dt VALUES('dsdcd',3, 'Sam', '2019-01-26', 16.13);
INSERT INTO dt VALUES('sdc',2, 'Janice', '2018-02-01', 12.19);
INSERT INTO dt VALUES('scsc',2, 'Janice', '2017-12-06', 5.10);

+----------+---------+--------+------------+--------+
| id       | user_id | name   | td         | amount |
+----------+---------+--------+------------+--------+
| blah     |       1 | Rodeo  | 2018-01-20 |  10.12 |
| blahblah |       1 | Rodeo  | 2019-01-01 |  40.44 |
| sas      |       2 | Janice | 2018-02-05 |  18.18 |
| dsdcd    |       3 | Sam    | 2019-01-26 |  16.13 |
| sdc      |       2 | Janice | 2018-02-01 |  12.19 |
| scsc     |       2 | Janice | 2017-12-06 |    5.1 |
+----------+---------+--------+------------+--------+

For the above table how i can get this output. I can achieve this by windowing function but not sure how to do this by correlated subquery. Appreciate any help!

Output Basically difference of users first transaction amount from their latest transaction amount. If the user has only one transaction then the difference is 0

User_id name amount
1 Rodeo 30.32 [40.44(latest trans) - 10.12 (min trans)]
3 Sam 0
2 Janice 13.08 [18.18 (latest trans) - 5.1 (min trans)]

Are we guaranteed that there could be no more than a single transaction per day for each user? — PM 77-1, Oct 11 '19 at 18:59
Also why do you have to use a correlated subquery? Is this homework? Those requirement are very contrived and not the best way to tackle this problem. — Sean Lange, Oct 11 '19 at 19:18
Are you trying to subtract the lowest amount from the earliest OR the most recent - earliest? You state both requirements in your question. The explanation says earliest but in the desired output you state lowest amount. Which one is it? — Sean Lange, Oct 11 '19 at 19:25
Please don't make more work for others by vandalizing your posts. By posting on the Stack Exchange (SE) network, you've granted a non-revocable right, under a [CC BY-SA license](//creativecommons.org/licenses/by-sa/4.0), for SE to distribute the content (i.e. regardless of your future choices). By SE policy, the non-vandalized version is distributed. Thus, any vandalism will be reverted. Please see: [How does deleting work? …](//meta.stackexchange.com/q/5221). If permitted to delete, there's a "delete" button below the post, on the left, but it's only in browsers, not the mobile app. — Makyen, Oct 11 '19 at 21:29

Barmar · Answer 1 · 2019-10-11T19:37:43.817

This is similar to SQL select only rows with max value on a column, but you need to do it twice: once for the earliest row, again for the latest row.

SELECT t1.user_id, t1.name, t1.amount - t2.amount ASA amount
FROM (
    SELECT dt1.user_id, dt1.name, dt1.amount
    FROM dt AS dt1
    JOIN (
        SELECT user_id, name, MAX(td) AS maxdate
        FROM dt
        GROUP BY user_id, name) AS dt2
    ON dt1.user_id = dt2.user_id AND dt1.name = dt2.name AND dt1.td = dt2.maxdate
) AS t1
JOIN (
    SELECT dt1.user_id, dt1.name, dt1.amount
    FROM dt AS dt1
    JOIN (
        SELECT user_id, name, MIN(td) AS mindate
        FROM dt
        GROUP BY user_id, name) AS dt2
    ON dt1.user_id = dt2.user_id AND dt1.name = dt2.name AND dt1.td = dt2.mindate
) AS t2
ON t1.user_id = t2.user_id AND t1.name = t2.name

@forpas - It depends on which set of requirements you read. If you read the text explanation this does not have the most recent amount minus the earliest amount. But if you read the desired output this does match. Seems the OP is not very clear in their requirements and they are inconsistent within their own post. — Sean Lange, Oct 11 '19 at 19:30

forpas · Accepted Answer · 2019-10-11T20:16:00.703

With 2 subqueries to get the latest and earliest amounts:

select distinct t.user_id, t.name, 
  (select amount from dt
   where user_id = t.user_id 
   order by td desc limit 1
  )
  -
  (select amount from dt
   where user_id = t.user_id 
   order by td limit 1
  ) amount
from dt t

See the demo.
Or:

select t.user_id, t.name,
  max(t.latest * t.amount) - max(t.earliest * t.amount) amount
from (
  select d.user_id, d.name, d.amount, 
    d.td = g.earliestdate earliest, d.td = g.latestdate latest
  from dt d inner join (
    select user_id, min(td) earliestdate, max(td) latestdate   
    from dt
    group by user_id
  ) g on d.user_id = g.user_id and d.td in (earliestdate, latestdate)   
) t
group by t.user_id, t.name

See the demo.
Results:

| user_id | name   | amount |
| ------- | ------ | ------ |
| 1       | Rodeo  | 30.32  |
| 2       | Janice | 13.08  |
| 3       | Sam    | 0      |

score 1 · Answer 3 · answered Oct 11 '19 at 19:42

Approach using Correlated Subquery:

Query

SELECT user_id,
       name,
       Round(Coalesce ((SELECT t1.amount
                        FROM   dt t1
                        WHERE  t1.user_id = dt.user_id
                        ORDER  BY t1.td DESC
                        LIMIT  1) - (SELECT t2.amount
                                     FROM   dt t2
                                     WHERE  t2.user_id = dt.user_id
                                     ORDER  BY t2.td ASC
                                     LIMIT  1), 0), 2) AS amount
FROM   dt
GROUP  BY user_id,
          name;

| user_id | name   | amount |
| ------- | ------ | ------ |
| 1       | Rodeo  | 30.32  |
| 2       | Janice | 13.08  |
| 3       | Sam    | 0      |

View on DB Fiddle

score 0 · Answer 4 · answered Oct 12 '19 at 05:51

You can try this as well

Select t3.user_id, t3.name, max(t3.new_amount) FROM (
  Select t1.user_id, t2.name, (t1.amount - t2.amount) as new_amount
  FROM dt t1 
  INNER JOIN dt t2 
  ON t1.user_id=t2.user_id
  Order by t1.user_id ASC, t1.td DESC, t2.user_id ASC, t2.td ASC
) as t3 
group by t3.user_id,t3.name;

Demo

min and max with and without windowing function

4 Answers4