CODEWITHSUNDEEP
sqldatabasepostgresql
Ted
Ted

Reputation: 29

PostgreSQL query current week trend

In PostgreSQL how to query current week's trend compared to last week data (positive or negative indicator)

Id | Value | Date
1  | 5.1   | 2021-05-24 05:41:27
2  | 5.2   | 2021-05-25 05:41:27
3  | 6.3   | 2021-05-26 05:41:27
4  | 6.4   | 2021-05-27 05:41:27
5  | 7.5   | 2021-05-28 05:41:27 
6  | 7.5   | 2021-05-29 05:41:27 
7  | 7.6   | 2021-05-30 05:41:27 
8  | 4.5   | 2021-05-31 05:41:27 
9  | 4.6   | 2021-06-01 05:41:27 
10  | 4.7   | 2021-06-02 05:41:27 
11  | 4.2   | 2021-06-03 05:41:27 
12  | 4.1   | 2021-06-04 05:41:27 
13  | 4.0   | 2021-06-06 05:41:27 
14  | 6.1   | 2021-06-07 05:41:27 
15  | 6.2   | 2021-06-08 05:41:27 

Current week sum = 6.2 + 6.1 + 4.0 + 4.1 + 4.2 + 4.7 + 4.6 = 33.9
Before last week sum = 4.5 + 7.6 + 7.5 + 7.5 + 6.4 + 6.3 + 5.2 = 45
Expected output: Current weekly trend is "negative (-ve)"

Upvotes: 0

Views: 290

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can aggregate by week and compare:

select date_trunc('week', date) as week, sum(value),
       (case when sum(value) > lag(sum(value)) over (order by min(date))
             then '+'
             when  sum(value) < lag(sum(value)) over (order by min(date))
             then '-'
        end) as week_over_week
from t
group by week;

Upvotes: 1

Related Questions