How can I put a query to average DOW sales?

How can I put a query to average DOW sales?

How can i join a condition to this query? What i need is average by DOW
with the following condition... Example : Product A at Store 1 has Monday
unit sales that go like this:
Week 1 – 5
Week 2 – 10
Week 3 – 3
Week 4 – 3
Week 1 will have no lost sales, as it is the first week. In week 2, the
baseline average would be 5 – since the week 2 sales is greater than 5,
then there is no lost sales. The average going into week 3 would be 7.5.
Week 3 sales are 3, so the gross lost sales is 4.5. Condn. 1. IF
SALES AVERAGE IS LESS THAN 2.0 PER DAY, THEN THE CALCULATION IS NOT
NECESSARY. LOST SALES SHOULD BE ZERO.
Condn. 2. THE AVERAGE IS THE LAST 13 WEEKS OF SALES FOR THAT
STORE/DAY OF WEEK/PRODUCT. ZEROS DO NOT COUNT IN THE AVERAGE. SO, WEEK
14 SHOULD BE THE AVERAGE OF WEEK 2 TO WEEK 14, ASSUMING THAT ALL OF THESE
DAYS HAVE SALES>0.
(QUERY)
select product_id,
store_id,
trans_date,
promo_sales,
promo_sold_qty,
'Baseline Average(13 week)_1',
from fact_product_hourly
where product_id=650231 and store_id = 886
and dayofweek(trans_date)=2
and trans_date between '2012-11-11' and '2013-07-31'
Please help.