Postgresql – Calculate difference of days between dates in different rows, based on consecutive value of a column, for each customer_id

You can use LAG() function in a query with GROUP BY just like any other query. The only difference is that the columns allowed in the window (OVER) and in the LAG are the ones allowed in SELECT after a GROUP BY:

select 
    date,
    session_id, 
    sum(upload_usage) as upload,
    sum(download_usage) as download,
    sum(total_usage_on_a_day) as total_as_cumm,
    sum(total_usage_on_a_day) 
    - coalesce(lag(sum(total_usage_on_a_day)) over (partition by session_id order by date), 0)
        as expected_difference
 from jiodba.s_crc_zda_mon_conn_usage 
 where gpart = '1100043958' 
   and zzaccess_ntwk_id = 'FTTH' 
 group by session_id, date
 order by session_id, date 
 limit 100 ;

• I'm not sure why you used max(usage) while you name the column total_usage. I changed it to use sum() instead. If you need to use max(usage) there, pick a more appropriate name for that column.

Skinning Q#1 independently and slightly differently than @ypercube

with cte as (select row_number() over (partition by product_id,
                                       invoice_id 
                                 order by dt desc) as rn,
                    product_id,
                    invoice_id,
                    amount,dt
               from product ) 
select product_id, invoice_id,amount,dt
  from cte
 where rn=1
 order by product_id,invoice_id;

 product_id | invoice_id | amount |     dt     
------------+------------+--------+------------
 PROD1      | INV01      |      2 | 2018-01-05
 PROD1      | INV03      |      1 | 2018-01-05
 PROD2      | INV02      |      3 | 2018-01-08
 PROD2      | INV04      |      4 | 2018-01-08
(4 rows)

For Q#2, you are on the right track, but the SQL will have a cross join(gasp!)

I think a function with a loop/cursor would be more optimized (i'll try that in my next free block of time)

--the cte will give us the real values
with cte as (select product_id, 
                    sum(amount) as amount, 
                    dt
               from product
              group by product_id,dt)
select p.product_id,  
       (select cte.amount --choose the amount
          from cte
         where cte.product_id = p.product_id
           and cte.dt <= d.gdt -- for same day or earlier
         order by cte.dt desc
         limit 1) as finamt,
       d.gdt
from (select generate_series( (select min(dt)
                                 from product), --where clause if some products 
                                                --don't have an amount
                              (select max(dt)
                                 from product),
                              '1 day' 
                            )::date as gdt)  d
cross join --assuming each listed product has an amount on the min date
     (select distinct product_id
        from product) p
left join --since we need to fill the gaps
     cte on ( d.gdt = cte.dt 
             and p.product_id = cte.product_id)
order by d.gdt, p.product_id
;