Comparing two columns in filter

Hi,
I’m trying to compare two dimensions to calculate aggregates. Is that possible.

Questions:

Use case-1:

I have a large amount of data which has following fields

id, primary_date, date_1, date_2, amount

For a given date by the user user_date, I have to calculate sum(amount) when primary_date < user_date and date_1 < primary_date and date_2 > date_1

Is it possible to compare two dimensions in a given interval to calculate the aggergate?

Use case-2:

id, primary_date, date_1, date_2, amount

For a given date by the user user_date, I have to calculate sum(amount) when primary_date < user_date and date_1 < user_date and date_2 > user_date

I want to calculate this for past 6 months

user_date = 2019-10-01

I want this average to be calculated for last 6 months i.e 2019-09-01, 2019-08-01, 2019-07-01, 2019-06-01, 2019-05-01

Right now I wrote a script which takes user_date and runs the query and returns sum, I’m running this script 6 times to calculate 6 sums for 6 above dates. Is it possible to calculate these in a single query using granularity.

Thanks,

Pradeep