Calculating totals for pre period excluding counts prior to lookback and after reference year

Question

Assuming a student eats only 1 candy per day. I need to find out how many candies a student consumed before the start of school(as shown in the column "Total_candies_consumed_one_year_before_start_of_school" in red font). My data is as shown in the table with black font. I calculated the highlighted column. I have provided rationale on how to calculate the "Total_candies_consumed_one_year_before_start_of_school".

I am stuck at how to exclude counts that happened before "1_year_lookback_period" and after "school start date".

Any help is greatly appreciated.

Answer 1

I think that you are looking for some kind of linear regression.

The idea is to assess how many candies were consumed during the year that precedes school_start_date by computing the number of days of overlap between this period and the date range defined by the purchase_date and consumed_date of the candies. Based on the overlap ratio, you can project the consumption during the lookback period.

This should do what you want:

select
    t.*,
    number_of_candies *
        greatest(
            least(school_start_date, consumed_date) 
            - greatest(purchase_date, add_months(school_start_date, -12)), 
            0
        ) / (consumed_date - purchase_date) candies_consumed_during_loopback
from mytable t

Demo on DB Fiddle:

ID | PURCHASE_DATE | NUMBER_OF_CANDIES | CONSUMED_DATE | SCHOOL_START_DATE | RES
-: | :------------ | ----------------: | :------------ | :---------------- | --:
 1 | 30-DEC-09     |                15 | 14-JAN-10     | 01-JAN-11         |  13
 1 | 30-MAY-10     |                30 | 29-JUN-10     | 01-JAN-11         |  30
 2 | 01-NOV-10     |                90 | 30-JAN-11     | 01-JAN-11         |  61
 2 | 09-AUG-12     |                15 | 24-AUG-12     | 01-JAN-11         |   0
 3 | 01-DEC-14     |               900 | 19-MAY-17     | 01-JAN-16         | 365