with a table table1 like below
+------+-----+------+----------+-----------+
| city | day | hour | car_name | car_count |
+------+-----+------+----------+-----------+
| 1 | 12 | 00 | corolla | 8 |
| 1 | 12 | 00 | city | 9 |
| 1 | 13 | 00 | corolla | 17 |
| 1 | 13 | 00 | city | 2 |
| 1 | 14 | 00 | corolla | 3 |
+------+-----+------+----------+-----------+
for each city, day , hour need to find out the difference count_diff in the car_count for car_names corolla and city excluding those when both or either of them is not present. count_diff is essentially [ (count of corollas) - (count of city) ]
expected output
+------+-----+------+-----------+
| city | day | hour | count_diff|
+------+-----+------+-----------+
| 1 | 12 | 00 | -1 |
| 1 | 13 | 00 | 15 |
+------+-----+------+-----------+
with data as
(select city, day, hour ,
sum(case when car_name = 'corolla' then car_count else 0 end) corolla_count,
sum(case when car_name = 'city' then car_count else 0 end) as city_count
group by city, day, hour
)
select city, day, hour, corolla_count - city_count from data
where corrolla_count > 0 and city_count > 0
You could try this.
t=# with a as (
select
*
, count(1) over a
, car_count - lead(car_count) over a count_diff
from table1
window a as (partition by city,day,hour)
)
select city,day,hour,count_diff
from a
where count >1 and count_diff is not null;
city | day | hour | count_diff
------+-----+--------+------------
1 | 12 | 00 | -1
1 | 13 | 00 | 15
(2 rows)
Time: 0.411 ms