select coalesce(t1.col1, t2.col2) as "группировка"
, coalesce(t1.c, 0) as "кол-во_1"
, coalesce(t2.c, 0) as "кол-во_2"
from (select col1, count(*) c from test group by col1) t1
full join
(select col2, count(*) c from test group by col2) t2 on t1.col1 = t2.col2
order by 1
;select *
from users
where not exists (
select 1
from tasks
where executor_id = users.id
and status in ('wip','new'))WITH table1 ( user_id, amount, created_at)
AS (VALUES
(1, 50, '2018-09-02 16:59:58.10' :: TIMESTAMP)
, (1, 120, '2018-09-09 12:59:58.10' :: TIMESTAMP)
, (3, 25, '2018-07-05 16:59:58.10' :: TIMESTAMP)
, (3, 90, '2018-07-07 16:59:58.10' :: TIMESTAMP)
)
SELECT t1.user_id,sum(t1.amount)
FROM table1 t1
JOIN LATERAL (SELECT min(t2.created_at) AS mi
FROM table1 t2
WHERE t2.user_id = t1.user_id
) t3 ON TRUE
WHERE t1.created_at - t3.mi <= '5 day'
GROUP BY t1.user_id
;