Q:

sql query to iterate over rows and make calculations

I am using SQLITE. I have been trying to find an answer for the past couple of hours without success. The data I'm currently working for looks something like this (I'm simplifying):
Sales_period  Sales_Qty  Sales_Profit
0001                15       300
0002                20       500
0003                10       200

From this table I'm looking to deduct the following:
A new column called Sales_Index which is: Sales_Profit/Sales_Qty
Another new column called Sales_Goal which is the average of previous Sales_Index s for a given sales period. So for example the Sales_Goal for Sales_Period 0003 would be 22.5. Finally I'd like to return a boolean value which determines if the Sales_Goal was met for that given period. So my desired output would look like this:
Sales_period  Sales_Qty  Sales_Profit  Sales_Index  Sales_Goal Goal_Met
0001                15       300           20            -         - 
0002                20       500           25            20        Y
0003                10       200           20            22.5      N

Is there a way to do this? Any help is appreciated. 
Edit: I can create a new table including the Sales_Index column. I'm having trouble with the steps after that. 

A:

You can use a correlated subquery for the calculation:
with t as (
      select t.*, (sales_profit / sales_qty) as sales_index 
      from mytable t
     )
select t.*,
       (select avg(t2.sales_index)
        from t t2
        where t2.sales_period < t.sales_period
       ) as sales_goal
from t;

In the most recent versions of SQLite, you can use window functions:
select t.*, (sales_profit / sales_qty) as sales_index,
       avg(sales_profit / sales_qty) over (order by sales_period rows between unbounded preceding and 1 preceding) as sales_goal
from mytable t

