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 ) )
INSERT INTO sources ( source_id, source_name ) VALUES
 (1,'cc_a')
,(2,'cc_b')
,(3,'cc_c')
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