SQL Query/ Assigning Rank

I am interested in SQL query not the PLSQL code.

We need to assign the rank based on date and id value

Input table should look like below

+------------+----+
|    date    | id |
+------------+----+
| 01-01-2018 | A  |
| 02-01-2018 | A  |
| 03-01-2018 | C  |
| 04-01-2018 | B  |
| 05-01-2018 | A  |
| 06-01-2018 | C  |
| 07-01-2018 | C  |
| 08-01-2018 | B  |
| 09-01-2018 | B  |
| 10-01-2018 | B  |
+------------+----+

output table should look like below

+------------+----+------+
|    date    | id | rank |
+------------+----+------+
| 01-01-2018 | A  |    1 |
| 02-01-2018 | A  |    2 |
| 03-01-2018 | C  |    1 |
| 04-01-2018 | B  |    1 |
| 05-01-2018 | A  |    1 |
| 06-01-2018 | C  |    1 |
| 07-01-2018 | C  |    2 |
| 08-01-2018 | B  |    1 |
| 09-01-2018 | B  |    2 |
| 10-01-2018 | B  |    3 |
+------------+----+------+

Answer

This is a type of gaps-and-islands problem. In this case, the simplest solution is probably the difference of row numbers:

select t.*,
       row_number() over (partition by id, (seqnum - seqnum_i)
                          order by date
                         ) as ranking
from (select t.*,
             row_number() over (order by date) as seqnum,
             row_number() over (partition by id order by date) as seqnum_i
      from t
     ) t;

Why this works is a little tricky to explain. The difference of the two row numbers assigns a constant value to advance values of the same id. If you stare at the results of the subquery, you will see how this works.

Then the outer query just uses row_number() to assign the sequential number you want.