CODEWITHSUNDEEP
sqlpostgresql
user12285060
user12285060

Reputation:

Aggregation within a certain time

I need to create a list of the 2 rating hotels in the UK that have increased their rating by at least 3 points from the beginning.

Month       | Hotel | Rating | Region |
---------------------------------------
01-Jan-19   | A     | 1      |  US    |
01-Feb-19   | B     | 2      |  UK    |
01-Mar-19   | C     | 3      |  EU    |
01-Apr-19   | A     | 1      |  US    |
01-May-19   | B     | 4      |  UK    |
01-Jun-19   | C     | 3      |  EU    |
01-Jul-19   | A     | 1      |  US    |
01-Aug-19   | B     | 5      |  UK    |
01-Sep-19   | C     | 4      |  EU    |

Like this, the query must produce Hotel B only.

Upvotes: 1

Views: 51

Answers (2)

user12285060
user12285060

Reputation:

This also works

I have tried it

Select "Hotel"

From T

Where "Region" = 'UK'

Group by "Hotel"
                Having 
                        Min ("Rating") = 2
                And 
                        Max ("Rating") >= 5

The Link to test:

https://www.db-fiddle.com/f/6TVgrC5WRjqdyPwdGSvWGN/8

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

It sounds like you want the first and last entries. One method uses conditional aggregation. I am going to assume that month is really a date or number and not a string:

select t.hotel
from (select t.*,
             row_number() over (partition by hotel order by month asc) as seqnum_asc,
             row_number() over (partition by hotel order by month desc) as seqnum_desc
     from t
    ) t
group by t.hotel
having max(rating) filter (where seqnum_asc = 1) >= max(rating) filter (where seqnum_desc = 1) + 3;

Upvotes: 1

Related Questions