Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (name, day, count, load_id) AS
SELECT 'John', 10, 9, 14 FROM DUAL UNION ALL
SELECT 'John', 9, 5, 12 FROM DUAL UNION ALL
SELECT 'John', 9, 7, 13 FROM DUAL UNION ALL
SELECT 'John', 8, 5, 12 FROM DUAL UNION ALL
SELECT 'John', 8, 2, 11 FROM DUAL UNION ALL
SELECT 'Paul', 9, 0, 12 FROM DUAL UNION ALL
SELECT 'Paul', 9, 1, 13 FROM DUAL UNION ALL
SELECT 'Paul', 7, 1, 11 FROM DUAL UNION ALL
SELECT 'Paul', 7, 9, 10 FROM DUAL UNION ALL
SELECT 'Mark', 7, 10, 11 FROM DUAL UNION ALL
SELECT 'Mark', 7, 11, 10 FROM DUAL UNION ALL
SELECT 'Ned', 7, 0, 10 FROM DUAL;
12 rows affected

SELECT *
FROM   (
  SELECT name,
         MAX(count) KEEP (DENSE_RANK LAST ORDER BY load_id) AS count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY MAX(load_id) DESC) AS rn
  FROM   test
  GROUP BY name, day
)
PIVOT(
  MAX(count) FOR rn IN (
    1 AS last_day_count,
    2 AS prev_day_count
  )
)


NAME
LAST_DAY_COUNT
PREV_DAY_COUNT


John
9
7

Mark
10
null

Ned
0
null

Paul
1
1


SELECT *
FROM   (
  SELECT name,
         count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY load_id DESC)
           AS rn_by_person
  FROM   (
    SELECT name,
           load_id,
           count,
           ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC)
             AS rn_by_day
    FROM   test
  )
  WHERE  rn_by_day = 1
)
PIVOT(
  MAX(count) FOR rn_by_person IN (
    1 AS last_day_count,
    2 AS prev_day_count
  )
)


NAME
LAST_DAY_COUNT
PREV_DAY_COUNT


John
9
7

Mark
10
null

Ned
0
null

Paul
1
1


SELECT *
FROM   (
  SELECT name,
         MAX(count) KEEP (DENSE_RANK LAST ORDER BY load_id) AS count,
         MAX(load_id) AS load_id,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY MAX(load_id) DESC) AS rn
  FROM   test
  GROUP BY name, day
)
PIVOT(
  MAX(count) AS count,
  MAX(load_id) AS load_id
  FOR rn IN (
    1 AS last_day,
    2 AS prev_day
  )
)


NAME
LAST_DAY_COUNT
LAST_DAY_LOAD_ID
PREV_DAY_COUNT
PREV_DAY_LOAD_ID


John
9
14
7
13

Mark
10
11
null
null

Ned
0
10
null
null

Paul
1
13
1
11


SELECT *
FROM   (
  SELECT name,
         load_id,
         count,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY load_id DESC)
           AS rn_by_person
  FROM   (
    SELECT name,
           load_id,
           count,
           ROW_NUMBER() OVER (PARTITION BY name, day ORDER BY load_id DESC)
             AS rn_by_day
    FROM   test
  )
  WHERE  rn_by_day = 1
)
PIVOT(
  MAX(count) AS count,
  MAX(load_id) AS load_id
  FOR rn_by_person IN (
    1 AS last_day,
    2 AS prev_day
  )
)


NAME
LAST_DAY_COUNT
LAST_DAY_LOAD_ID
PREV_DAY_COUNT
PREV_DAY_LOAD_ID


John
9
14
7
13

Mark
10
11
null
null

Ned
0
10
null
null

Paul
1
13
1
11