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.

Derviş Kayımbaşıoğlu · Accepted Answer · 2020-09-25 12:16:47Z

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

edited Sep 25, 2020 at 12:16

answered Sep 25, 2020 at 12:10

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2020-09-25 12:14:29Z

1

You can filter on the last 8 weeks in the where clause, then group by week and do conditional aggregation:

select extract(year from sale_date) yyyy, extract(week from saledate) ww,
    sum(last_sale_price - lag_last_sale_price) filter(where lag_last_sale_price > last_sale_price) sum_dollars_up,
     count(*) filter(where lag_last_sale_price > last_sale_price) cnt_dollars_up
from (
    select dp.*, 
        lag(last_sale_price) over(partition by extract(year from sale_date), extract(week from saledate) order by sale_date) lag_last_sale_price
    from daily_price
    where symbol = 'AAPL'
      and sale_date >= date_trunc('week', current_date) - '8 week'::interval
) dp
group by 1, 2

Notes:

I am asssuming that you don't want to compare the first price of a week to the last price of the previous week; if you do, then just remove the partition by clause from the over() clause of lag()
this dynamically computes the date as of 8 (entire) weeks ago
if there is no price increase during a whole week, the query still gives you a row, with 0 as sum_dollars_up and cnt_dollars_up

answered Sep 25, 2020 at 12:14

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Shanmukh Meesala Over a year ago

Thank you very much @GMB for a quick answer. You put me in the right direction!

Collectives™ on Stack Overflow

PostgreSQL: Simplifying a SQL query into a shorter query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related