PostgreSQL: Simplifying a SQL query into a shorter query

Question

I have a table called 'daily_prices' where I have 'sale_date', 'last_sale_price', 'symbol' as columns.

I need to calculate how many times 'last_sale_price' has gone up compared to previous day's 'last_sale_price' in 10 weeks.

Currently I have my query like this for 2 weeks:

select count(*) as "timesUp", sum(last_sale_price-prev_price) as "dollarsUp", 'wk1' as "week"
from 
(
    select last_sale_price, LAG(last_sale_price, 1) OVER (ORDER BY sale_date) as prev_price 
 from daily_prices 
where sale_date <= CAST('2020-09-18' AS DATE) AND sale_date >= CAST('2020-09-14' AS DATE)
and symbol='AAPL'
) nest
where last_sale_price > prev_price

UNION

select count(*) as "timesUp", sum(last_sale_price-prev_price) as "dollarsUp", 'wk2' as "week"
from 
(
    select last_sale_price, LAG(last_sale_price, 1) OVER (ORDER BY sale_date) as prev_price 
 from daily_prices 
where sale_date <= CAST('2020-09-11' AS DATE) AND sale_date >= CAST('2020-09-07' AS DATE)
and symbol='AAPL'
) nest
where last_sale_price > prev_price

I'm using 'UNION' to combine the weekly data. But as the number of weeks increase the query is going to be huge. Is there a simpler way to write this query? Any help is much appreciated. Thanks in advance.

Answer 1

you can extract week from sale_date. then apply group by on the upper query

select EXTRACT(year from sale_date) YEAR, EXTRACT('week' FROM sale_date) week, count(*) as "timesUp", sum(last_sale_price-prev_price) as "dollarsUp"
from (
     select  
        sale_date, 
        last_sale_price, 
        LAG(last_sale_price, 1) OVER (ORDER BY sale_date) as prev_price 
     from daily_prices 
     where symbol='AAPL'
  )
where last_sale_price > prev_price
group by EXTRACT(year from sale_date), EXTRACT('week' FROM sale_date) 

to extract only weekdays you can add this filter

EXTRACT(dow FROM sale_date) in (1,2,3,4,5)

PS: make sure that monday is first day of the week. In some countries sunday is the first day of the week