sql - MySQL: Calculate 4 Week Average with 4 Week Offset -

this question related post made earlier: mysql: calculating data table 1 month offset

but need build procedure queries table of contact data stored week. here's simplified example of table working with:

+-----------------+------------+ | week_start_date |  contacts  | +-----------------+------------+ |    2015-03-08   |     12     | |    2015-03-01   |     20     | |    2015-02-22   |      5     | |    2015-02-15   |     17     | |    2015-02-08   |      8     | |    2015-02-01   |      2     | |    2015-01-25   |     16     | |    2015-01-18   |     10     | |    2015-01-11   |      4     | |       ...       |    ...     | +-----------------+------------+ 

what need figure out how calculate 4 week moving average has 4 week offset on top of that. instance, if wanted average contacts week of march 8, 2015, average of january 18 through february 8. in example above, average be: (10 + 16 + 2 + 8 ) / 4 = 9. , if wanted find average week of march 1, 2015, average of january 11 through february 1 comes out 8 using sample table above.

from last post, know can handle 4 week offset joining table on week_start_date similar this:

select s1.week_start_date, s2.total_contacts   sample_table s1        left join (select week_start_date, sum(contacts) total_contacts                     sample_table                   group week_start_date) s2           on s1.week_start_date =                 date_add(s2.week_start_date, interval 4 week)  s1.week_start_date = '2015-03-08' group s1.week_start_date; 

but getting compute 4 week average stuck. thought joining on range of dates work, keep getting averages lot larger expected. i'm guessing due how week_start_date's being grouped. (note there can multiple records each week. show 1 record each week on sample table make less cluttered.)

is joining on date range correct approach? or need add join somewhere?

thanks help.

i suggest using correlated subquery:

select st.*,        (select avg(contacts)         sample_table st2         st2.week_start_date >= st.week_start_date - interval 7 * 7 days ,               st2.week_start_date <= st.week_start_date - interval 4 * 7 days        ) avg_4week_delayed sample_table st;