Count distinct dates between two timestamps

Question

I want to count %days when a user was active. A query like this

select 
    a.id, 
    a.created_at, 
    CURRENT_DATE - a.created_at::date as days_since_registration, 
    NOW() as current_d
from public.accounts a where a.id = 3257

returns

id  created_at  days_since_registration current_d   tot_active
3257    2022-04-01 22:59:00.000 1   2022-04-02 12:00:0.000 +0400    2

The person registered less than 24 hours ago (less than a day ago), but there are two distinct dates between the registration and now. Hence, if a user was active one hour before midnight and one hour after midnight, he is two days active in less than a day (active 200% of days)

What is the right way to count distinct dates and get 2 for a user, who registered at 23:00:00 two hours ago?

Answer 1

WITH cte as (
    SELECT 42 as userID,'2022-04-01 23:00:00' as d
    union 
    SELECT 42,'2022-04-02 01:00:00' as d
)
SELECT 
   userID,
   count(d),
   max(d)::date-min(d)::date+1 as NrOfDays,
   count(d)/(max(d)::date-min(d)::date+1) *100 as PercentageOnline
FROM cte
GROUP BY userID;

output:

userid	count	nrofdays	percentageonline
42	2	2	100