usage stats report

-- how many new users signed in past 24 hours?
--select id, email, name, sign_in_count, last_sign_in_at, created_at 
select count(*)
from users 
where NOW() > last_sign_in_at 
  AND NOW() - last_sign_in_at <= interval '24 hours' 
  and sign_in_count = 1;
  
-- how many return users?
--select id, email, name, sign_in_count, last_sign_in_at, created_at 
select count(*)
from users 
where NOW() > last_sign_in_at 
  AND NOW() - last_sign_in_at <= interval '24 hours' 
  and sign_in_count > 1;

-- how many lot searches?
select count(*) 
from lot_search_logs
where NOW() > created_at 
  AND NOW() - created_at <= interval '24 hours'
  
-- any unconfirmed registrations?
--select id, email, name, sign_in_count, last_sign_in_at, created_at, confirmed_at 
select count(*)
from users 
where NOW() > last_sign_in_at 
  AND NOW() - last_sign_in_at <= interval '24 hours' 
  and confirmed_at is null ;