So I would like to count my active partners (merchants) for each day from 2017-01-01

I have a postgresql datawarehouse where I have a source table called sources like this:

And I have a goal table called historical_active_merchants table like this:

... are days before and after from 2017-01-01 until now

My logic how it would be nice to work:

UPDATE historical_active_merchants SET num_of_merchants=num_of_merchants 1 
WHERE (sources.contract_date <= historical_active_merchants.date
AND sources.termination_date > historical_active_merchants.date)
OR (sources.contract_date <= historical_active_merchants.date
AND sources.termination_date is null)

Is it possible without loops? Or is there a solution by using loop?

I just can't list here all the attepmts I've tried already...

I appreciate any tips and solutions and thanks in advance!

CodePudding user response：

You can update historical_active_merchants based on a select that counts the merchants.

update historical_active_merchants H
set num_of_merchants = count_merchants
from (
  select H.date, count(S.merchantid) as count_merchants
  from historical_active_merchants H
  left join sources S on H.date >= S.contract_date 
    and (S.termination_date is null or H.date <= S.termination_date)
  group by H.date
) X
where H.date = X.date;

(the subquery is needed because of the group by)