Finding the difference between two rows in SQL and assigning a numerical value to it (SQLite)

I have a table

name	login_time
John	2021-01-02 08:10:52
John	2021-01-02 11:55:01
John	2021-01-04 17:36:22
Amy	2021-01-02 10:12:48
Amy	2021-01-06 11:22:41
Amy	2021-01-06 19:05:07

I want to add a usage_days column. If the same user login within the same day of his last login time, it is considered as the same day. The resulting column should look something like this:

name	login_time	usage_days
John	2021-01-02 08:10:52	1
John	2021-01-02 11:55:01	1
John	2021-01-04 17:36:22	2
Amy	2021-01-02 10:12:48	1
Amy	2021-01-06 11:22:41	2
Amy	2021-01-06 19:05:07	2

Answer

We might need to know your exact database to render an exact answer, but the following would probably work:

SELECT name, login_time,
       DENSE_RANK() OVER (PARTITION BY name ORDER BY CAST(login_time AS date)) usage_days
FROM yourTable
ORDER BY name, login_time;