By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
with table_name(name, address, count, current_date_time) as (
select 'john' ,'LA' , 102, '2019-07-12 12:24:38' union all
select 'peter' ,'MAC' , 105, '2019-07-12 12:24:40' union all
select 'john' ,'NY' , 210, '2019-07-12 12:24:02' union all
select 'john' ,'WD' , 18 , '2019-07-12 12:24:12'
), t2 as
(
SELECT count, lead(count,1,0) over (order by current_date_time desc ) as ld,
current_date_time
FROM table_name
WHERE name='john'
ORDER BY current_date_time DESC LIMIT 2
)
SELECT count - ld as "Difference"
FROM t2
ORDER BY current_date_time DESC LIMIT 1
| Difference |
|---|
| 84 |
SELECT 1