Untitled

with recursive
    u(id, name) AS (
        select id, fullname from users where last_seen_at = now()::date
    ),
    s(user_id, cnt) AS (
        select user_id, count(*) from orders
         where user_id in (select id from u)
         group by user_id
    )
select * from u left join s on u.id = s.user_id;



select u.id, fullname, count(*)
from users u
right join orders o on u.id = o.user_id
where
    u.last_seen_at = now()::date
group by u.id, nickname