Find weekly peaks of of daily data SQL

Question

I have data stored in a Vertica sql database structured like so:

location date count
Seattle     2015-09-21  48991
Portland    2015-09-21  38396
Seattle     2015-09-22  49639
Portland    2015-09-22  28817
Portland    2015-09-23  29183
Seattle     2015-09-23  50210
Portland    2015-09-24  29627
Seattle     2015-09-24  50844
Seattle     2015-09-25  56485
Portland    2015-09-25  30076
Portland    2015-09-26  30352
Seattle     2015-09-26  52011
Portland    2015-09-27  30491
Seattle     2015-09-27  52291

With count being a daily peak. I would like to pull the max count for each week, so I essentially end up with a weekly peak.

location week_ending_date count
Portland    2015-09-27  38396
Seattle     2015-09-27  56485

Any suggestions?

Thanks!

And how do you define a week? On what day does it begin? You can use [`DATEPART(WEEK, )` or `DATEPART(ISOWEEK, )`](https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Date-Time/DATE_PART.htm), but you'll have issues at year end rollover either way. If neither of those functions suits your needs, the easiest solution may be to create your own calendar table. — Bacon Bits, Mar 08 '16 at 20:06
Please update your question with the SQL you're having issues with — Kermit, Mar 08 '16 at 20:19
@BaconBits if he/she cannot use neither `week` (starts on Sunday) or `week_iso` (starts on Monday) then `time_slice()` aggregate function will do the trick... — mauro, Mar 09 '16 at 21:48

score 1 · Accepted Answer · answered Mar 09 '16 at 20:53

Assuming your week starts on Monday...

SQL> select * from wtest order by dt ;
 location |     dt     | count 
----------+------------+-------
 Seattle  | 2015-09-21 | 48991
 Portland | 2015-09-21 | 38396
 Portland | 2015-09-22 | 28817
 Seattle  | 2015-09-22 | 49639
 Portland | 2015-09-23 | 29183
 Seattle  | 2015-09-23 | 50210
 Portland | 2015-09-24 | 29627
 Seattle  | 2015-09-24 | 50844
 Portland | 2015-09-25 | 30076
 Seattle  | 2015-09-25 | 56485
 Portland | 2015-09-26 | 30352
 Seattle  | 2015-09-26 | 52011
 Portland | 2015-09-27 | 30491
 Seattle  | 2015-09-27 | 52291

SQL> select 
        location, 
        min(dt) + 6 as week_ending_date, 
        max(count) as count 
     from 
        wtest 
     group by location, year_iso(dt), week_iso(dt) ;

 location | week_ending_date | count 
----------+------------------+-------
 Portland | 2015-09-27       | 38396
 Seattle  | 2015-09-27       | 56485

Find weekly peaks of of daily data SQL

1 Answers1