How to join two tables based on a calculated field?

September 20, 2022

I have two SQL queries that output the same kind of output and have the same grouping and order :

select date_trunc('month', inserted_at)::date as date, count(id) from payment_logs where payment_logs.event_name = 'subscription_created' group by date order by date desc;

select date_trunc('month', inserted_at)::date as date, count(id) from users group by date order by date desc;

I would like to join those two results based on the calculated date field (which is the month), and have a result with 3 columns : date, count_users and count_payment_logs.

How can I achieve that? Thanks.

>Solution :

Something like this

select plog.date as odata, usr.cntusr, plog.cntlog 
from (
    select date_trunc('month', inserted_at)::date as date, count(id) cntlog
    from payment_logs 
    where payment_logs.event_name = 'subscription_created' 
    group by date order by date desc
) plog
join (
    select date_trunc('month', inserted_at)::date as date, count(id) cntusr
    from users 
    group by date 
) usr on plog.data = usr.data
order by odata desc