How to get distinct day number with window function (Postgres 9.6)
dense_rank() seems to be a perfect fit.

SELECT date,
       id,
       dense_rank() OVER (ORDER BY date) day_num
       FROM elbat;


db&lt;>fiddle