CODEWITHSUNDEEP
sqlhighest
Amir Ahmadi
Amir Ahmadi

Reputation: 1

SQL - finding hour with highest values - busy hour

I have a table with 15min resolution in every 24 hour and looking to extract an hour with highest value which is sum of the highest four 15 minutes

I can find greatest value, but need to have four consecutive 15 minutes which give the highest hour in 24 hours

here is a sample data enter image description here

Four results from 19:30 to 20:15 will result in a maximum value and the one hour consist of these four 15 minutes provide max hour value

Updated 4/4/2023 added Result for each consecutive rows and at the end get the max

select
item
,max(sum)
from(
    select
    ,result+lead(result),1,0) over (order by item,time)
    +lead(result),2,0) over (order by item,time)
    +lead(result),3,0) over (order by item,time) as sum
    from db
)
where 1=1
and time >= current_date -2 and time < current_date -1
and item like ('item1')
group by 1

Above code gives me the max value looking for

Need help with

Upvotes: -1

Views: 98

Answers (1)

Amir Ahmadi
Amir Ahmadi

Reputation: 1

Thanks Hogan I was not able to make the sample query work; the query might work on range of days and for different items. here I filtered one item and only one day to check the logic. My idea was to add - like what I did in excel (earlier edit) - and find the max original Date is like this enter image description here

Upvotes: 0

Related Questions