CODEWITHSUNDEEP
sqlpostgresqlcountsumwindow-functions
Shanmukh Meesala
Shanmukh Meesala

Reputation: 57

PostgreSQL: Simplifying a SQL query into a shorter query

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.

Upvotes: 3

Views: 60

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30565

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

Upvotes: 1

GMB
GMB

Reputation: 222472

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

Upvotes: 1

Related Questions