create table visits(start_dt text, start_ts integer, end_ts numeric, uid text, source_id text);
create table orders(buy_ts numeric, uid text, revenue integer);

insert into visits values('some_datetime1', 123123123, 123123321, 'abca', 'first');
insert into visits values('some_datetime2', 123123456, 123123654, 'abca', 'second');
insert into visits values('some_datetime2', 123123456, 123123654, 'abcb', 'first');
insert into visits values('some_datetime3', 123123124, 123123421, 'abcc', 'first');
insert into visits values('some_datetime3', 123123124, 123123421, 'abca', 'third');
insert into visits values('some_datetime4', 123123789, 123123987, 'abcd', 'first');
insert into visits values('some_datetime5', 123123998, 123123999, 'abcd', 'second');

insert into orders values(123123130, 'abca', 300);
insert into orders values(123123470, 'abca', 1000);
insert into orders values(123123130, 'abca', 400);
insert into orders values(123123998, 'abcd', 10);

select orders.uid, sum(orders.revenue)
from orders
group by orders.uid
UNION
select visits.uid, 0
from visits
where visits.uid not in (select orders.uid from orders);