How to get distinct day number with window function (Postgres 9.6) I am trying to get the distinct day number with a set of data that contains different dates that are not always consecutive. For example, if I have this data:

      date | id
2019-01-01 | 1
2019-01-02 | 2
2019-01-02 | 3
2019-01-04 | 4
2019-01-07 | 5
2019-01-07 | 6


I need to get the day number, where the day_num is the next unique data from the fist, and NOT the count of days SINCE the first .. e.g.

      date | id | day_num
2019-01-01 | 1  |   1
2019-01-02 | 2  |   2
2019-01-02 | 3  |   2
2019-01-04 | 4  |   3
2019-01-07 | 5  |   4
2019-01-07 | 6  |   4


Use case:
1) I need to know how many distinctive dates someone logged time .. so above I could see that the time was logged over 4 days (last day_num value).
2) I need to see which work day each separate date group is .. for example, above I can see that work done on 2019-01-04 was the 3rd time which this person worked.  

for brevity, columns for user_id, hours etc, are not shown

In trying to figure this out, I got as far as

  COUNT(1) OVER (
     ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW
  ) day_num,


.. which of course is not grouping by the date. This is PG 9.6 so I dont have frame_exclusion available .. not sure if that would even have helped.
dense_rank() seems to be a perfect fit.

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


db&lt;>fiddle