Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sources (
 source_id int 
,source_name varchar(10)
,PRIMARY KEY(source_id))
CREATE TABLE foo_values(
 device_id int not null --device_id for "foo"
,source_id int 
,value int
,CONSTRAINT fk_source_id
      FOREIGN KEY(source_id ) 
	  REFERENCES sources(source_id ) )
CREATE TABLE high_low_source
(high_source_id int
,low_source_id int
, constraint fk_low
     FOREIGN KEY(low_source_id ) 
	  REFERENCES sources(source_id )
,constraint fk_high
     FOREIGN KEY(high_source_id ) 
	  REFERENCES sources(source_id )
)
INSERT INTO sources ( source_id, source_name ) VALUES
 (1,'cc_a')
,(2,'cc_b')
,(3,'cc_c');


INSERT INTO high_low_source VALUES
(1,2),(1,3),(2,3)
3 rows affected

3 rows affected

INSERT INTO foo_values (device_id,source_id, value ) VALUES
 (3,1,1),(3,2,2),(3,3,3)
,(4,1,15),(4,2,4),(4,3,100)
6 rows affected

--sanity check
SELECT * FROm foo_values f INNER JOIN sources s on f.source_id  = s.source_id


device_id
source_id
value
source_id
source_name


3
1
1
1
cc_a

3
2
2
2
cc_b

3
3
3
3
cc_c

4
1
15
1
cc_a

4
2
4
2
cc_b

4
3
100
3
cc_c


SELECT device_id
     , MAX(value)-MIN(value)  as greatest_diff
FROM foo_values 
group by device_id


device_id
greatest_diff


3
2

4
96


--aux table if you need to know the ranking
SELECT 
     f.device_id
    ,f.value
    ,f.source_id
    ,RANK() OVER (PARTITION BY f.device_id ORDER BY f.value ) as low_first
    ,RANK() OVER (PARTITION BY f.device_id ORDER BY f.value DESC) as high_first
 FROM foo_values as f


device_id
value
source_id
low_first
high_first


3
1
1
1
3

3
2
2
2
2

3
3
3
3
1

4
4
2
1
3

4
15
1
2
2

4
100
3
3
1


WITH ranked as (
SELECT 
     f.device_id
    ,f.value
    ,f.source_id
    ,RANK() OVER (PARTITION BY f.device_id ORDER BY f.value ) as low_first
    ,RANK() OVER (PARTITION BY f.device_id ORDER BY f.value DESC) as high_first
 FROM foo_values as f)
 SELECT h.device_id
      , hs.source_name as source_high
      , ls.source_name as source_low
      , h.value as value_high
      , l.value as value_low
      , h.value - l.value as greatest_diff
 FROM ranked l 
 INNER JOIN ranked h 
     on l.device_id = h.device_id
 INNER JOIN sources ls
     on ls.source_id = l.source_id
 INNER JOIN sources hs
     on hs.source_id = h.source_id
 WHERE l.low_first =1 AND h.high_first = 1
 
 


device_id
source_high
source_low
value_high
value_low
greatest_diff


3
cc_c
cc_a
3
1
2

4
cc_c
cc_b
100
4
96


 SELECT h.device_id
      , hs.source_name as source_high
      , ls.source_name as source_low
      , h.value as value_high
      , l.value as value_low
      , h.value - l.value as my_diff
      , RANK() OVER (PARTITION BY h.device_id ORDER BY (h.value - l.value) DESC) as greatest_first
 FROM foo_values l 
 INNER JOIN foo_values h 
     on l.device_id = h.device_id
 INNER JOIN high_low_source hl
     on hl.low_source_id = l.source_id
     AND hl.high_source_id = h.source_id
 INNER JOIN sources ls
     on ls.source_id = l.source_id
 INNER JOIN sources hs
     on hs.source_id = h.source_id
  ORDER BY device_id, greatest_first


device_id
source_high
source_low
value_high
value_low
my_diff
greatest_first


3
cc_a
cc_b
1
2
-1
1

3
cc_b
cc_c
2
3
-1
1

3
cc_a
cc_c
1
3
-2
3

4
cc_a
cc_b
15
4
11
1

4
cc_a
cc_c
15
100
-85
2

4
cc_b
cc_c
4
100
-96
3
device_id	source_high	source_low	value_high	value_low	my_diff	greatest_first
3	cc_a	cc_b	1	2	-1	1
3	cc_b	cc_c	2	3	-1	1
3	cc_a	cc_c	1	3	-2	3
4	cc_a	cc_b	15	4	11	1
4	cc_a	cc_c	15	100	-85	2
4	cc_b	cc_c	4	100	-96	3