answer

WITH week_sum AS (SELECT 0 as fake_id,week, sum(share_price) as summary from stockquotes group by week order by week), week_growth AS (SELECT week, (LAST_VALUE(summary) OVER (PARTITION BY fake_id ORDER BY week ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - FIRST_VALUE(summary) OVER (PARTITION BY fake_id ORDER BY week ROWS BETWEEN 1 PRECEDING AND CURRENT ROW))::FLOAT/(select count(company) from stockquotes where week=0) AS growth from week_sum), company_growth AS (select company, week, share_price, (LAST_VALUE(share_price) OVER (PARTITION BY company ORDER BY week ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) - FIRST_VALUE(share_price) OVER (PARTITION BY company ORDER BY week ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) ) AS diff from stockquotes order by company, week), growth_table AS (  select company_growth.company, company_growth.week, company_growth.diff, week_growth.growth from company_growth JOIN week_growth ON (company_growth.week=week_growth.week) ), series_count AS ( select *, ( count(case when diff > growth then 1 else null end) OVER (PARTITION BY company ORDER BY week ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ) AS series from growth_table) select company, count(*) as counter from series_count where series=3 group by company order by counter DESC, company;