How to divide count from one table by the count from the other one or other solution needed

I have just one table with columns:

id date
1  2020-03-01 16:34:00
1  2020-03-01 17:33:32
2  2020-03-02 15:40:54

id is the client who logged in the website on specific date. They can log in more than once per day. I need to calculate retention rate day by day. That is, number of users who logged in more than once divided by all users within that day! So the output should look like this:

date         retention
2020-03-01   33%
2020-03-02   35%
2020-03-03   29%

I used:

select tbl.date, count(tbl.device_id)/count(distinct(retention_cohort.device_id)) as cnt from
    (select date_trunc('day', session_time) as date, device_id from retention_cohort
    group by date, device_id
    having count(device_id)>1
    order by date) tbl
group by tbl.date

But this division with counts doesn't work. Please help!

Solution

number of users who logged in more than once divided by all users within that day

You can do this with two levels of aggregation: first count the logins per user and day, then compute the ratio of counts greater than 1.

select session_date, avg((cnt > 1)::int) retention_rate
from (
    select date_trunc('day', session_time) session_date, count(*) cnt
    from retention_cohort
    group by session_date, device_id
) t
group by session_date