Perform a query on each CTE rows (generate_series())

Question

I would like to know if it is possible to perform a query on each rows of a CTE :

with series as (
    select (d + '1 month'::interval - '1 day'::interval )::timestamp date
    from generate_series(date_trunc('month', now()) - interval '1 year', now(), '1 month'::interval) d
)

Which returns :

2022-05-31 00:00:00.000000
2022-06-30 00:00:00.000000
2022-07-31 00:00:00.000000
2022-08-31 00:00:00.000000
2022-09-30 00:00:00.000000
2022-10-31 00:00:00.000000
2022-11-30 00:00:00.000000
2022-12-31 00:00:00.000000
2023-01-31 00:00:00.000000
2023-02-28 00:00:00.000000
2023-03-31 00:00:00.000000
2023-04-30 00:00:00.000000
2023-05-31 00:00:00.000000

I want to execute the following query for each of my CTE rows :

-- For each of series CTE rows as series_row :
SELECT amount_per_month FROM application_contract_subscription WHERE
                 subscription_date <= series_row.date AND (
                     expiry_date IS NULL
                     OR (expiry_date IS NOT NULL AND (commitment_period = 'monthly' AND subscription_date BETWEEN series_row.date - INTERVAL '30 day' AND series_row.date) OR (commitment_period = 'annually' AND subscription_date BETWEEN series_row.date - INTERVAL '1 year' AND series_row.date))
                 )
         AND tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'
         UNION ALL
         SELECT amount_per_month FROM application_contract_service WHERE
                 beginning_date <= series_row.date AND (
                     amortization_end_date IS NULL
                     OR (amortization_end_date >= series_row.date - INTERVAL '30 day')
                 )
         AND tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'
         UNION ALL
         SELECT amount_per_month FROM application_contract_licence WHERE
                 subscription_date <= series_row.date AND (
                     (amortization_end_date IS NULL AND subscription_date BETWEEN series_row.date - INTERVAL '30 day' AND series_row.date)
                     OR (amortization_end_date >= series_row.date - INTERVAL '30 day')
                 )
         AND tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

I expect this kind of result :

2022-05-31 00:00:00.000000 => 10000
2022-06-30 00:00:00.000000 => 500
2022-07-31 00:00:00.000000 => 0
2022-08-31 00:00:00.000000 => 1200
2022-09-30 00:00:00.000000 => 1400
2022-10-31 00:00:00.000000 => ...
2022-11-30 00:00:00.000000 => ...
2022-12-31 00:00:00.000000 => ...
2023-01-31 00:00:00.000000 => ...
2023-02-28 00:00:00.000000 => ...
2023-03-31 00:00:00.000000 => ...
2023-04-30 00:00:00.000000 => ...
2023-05-31 00:00:00.000000 => ...

Is it possible to do this within just 1 query ?

Answer 1

You seem to want an non-equi join to replace most of your WHERE clauses. The method is:

1. All the criteria that contain now() must be moved to the join condition.
2. now() must be replaced by seriesdate.
   NB: date is a type name so it is better if you do not name a column this way.

with series(seriesdate) as (
    select (d + '1 month'::interval - '1 day'::interval )::timestamp
    from generate_series(date_trunc('month', now()) - interval '1 year', now(), '1 month'::interval) d
)
SELECT seriesdate, seriesdateamount_per_month
FROM   application_contract_subscription
JOIN   series 
    ON subscription_date <= seriesdate
    AND (
       expiry_date IS NULL
       OR (expiry_date IS NOT NULL AND (commitment_period = 'monthly' AND subscription_date BETWEEN seriesdate - INTERVAL '30 day' AND seriesdate)
       OR (commitment_period = 'annually' AND subscription_date BETWEEN seriesdate - INTERVAL '1 year' AND seriesdate))
    )
WHERE  tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

UNION ALL

SELECT seriesdate, amount_per_month
FROM   application_contract_service
JOIN   series 
    ON beginning_date <= seriesdate
    AND (
         amortization_end_date IS NULL
         OR (amortization_end_date >= seriesdate - INTERVAL '30 day')
    )
WHERE  tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

UNION ALL

SELECT seriesdate, amount_per_month
JOIN   series 
    ON subscription_date <= now()
    AND (
         (amortization_end_date IS NULL AND subscription_date BETWEEN seriesdate - INTERVAL '30 day' AND seriesdate)
         OR (amortization_end_date >= seriesdate - INTERVAL '30 day')
    )
WHERE  tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'