AWS Athena: syntax for grouping data for week ending on a Saturday

Question

I'm having some issues with the Presto syntax in grouping data by separate weeks, ending on Saturdays. I have this field called "timestamp," and I've been using the the following to group by weeks. However, I noticed that date_truncs weeks ending on a Monday and using a date_add doesn't necessary group all rows correctly.

select date_add('day', 5, date_trunc('week', date(substr(timestamp, 1, 10)))) as week_ending

Any suggestions on how I can group weeks ending on Saturdays? Thanks!

Answer 1

select substring(
          cast(date_add(
                  'day',
                  5+(cast(dow(
                             date_parse('2020-04-12','%Y-%m-%d')
                             )=7
                          as integer)
                    )*7,
                  date_trunc(
                     'week',
                     date_parse('2020-04-12','%Y-%m-%d')
                  )
               ) as varchar
          ),
          1,
          10
       ) as week_ending;