SELECT t1.SYMBOL symbol, t1.DATE today, MIN( t3.DATE ) pdate, SUM( t3.CLOSE ) / COUNT( t3.CLOSE ) average
FROM STOCKS t1
LEFT JOIN STOCKS t2 ON t2.DATE < t1.DATE
AND t2.SYMBOL = t1.SYMBOL
LEFT JOIN STOCKS t3 ON t3.DATE
BETWEEN t2.DATE
AND t1.DATE
AND t2.SYMBOL = t1.SYMBOL
GROUP BY t1.SYMBOL, t1.DATE, t2.DATE
HAVING COUNT( t3.SYMBOL ) <=7
ORDER BY t1.SYMBOL, t1.DATE, t2.DATE