Postgresql redshift version: retrieve sales amount at the end of each month

Question

I have the following table called sales_intake. Every day a record is logged in the system until someone cancel their membership. I'm using PostgreSQL redshift version 8.0, which mean a lot of the updated release hasn't taken part in this version.

I'm trying to pull the last record/ revenue logged in at the end of each month

The table looks something like this:

Daily                   revenue
12/1/2020 00:00:00      50000
11/30/2020 00:00:00     47000
11/29/2020 00:00:00     45000
11/28/2020 00:00:00     42000
11/27/2020 00:00:00     39000
10/31/2020 00:00:00     25000
10/30/2020 00:00:00     22000
10/09/2020 00:00:00     19000
09/30/2020 00:00:00     16000

I'd like to have a table that looks like this:

 Daily        revenue
 11/30/2020   50000
 10/31/2020   47000
 09/30/2020   1600

I was told it's better to use the dateadd probably to pull it easier.

Here is what i tried but no success:

 Select revenue
 ,max(daily)
 from sales
 group by day

I also tried:

 select *
 from(select daily, revenue
 ,row_number () over (partition by year(revenue), month(revenue) order by daily )t
  from sales) as b
  where t= 1

I was also suggested this:

    with E as
    (
    select *, row_number() over (partition by year([Date]), month([Date]) order by [Date] desc ) 
     rn
    from @sales
    )
    select [Date], [Revenue]
     from E
     where rn = 1

Nothing seems to work. Any help would be appreciated

Answer 1

You can use window functions like that:

select *
from (
    select s.*, 
        row_number() over(partition by date_trunc('month', daily) order by daily desc) rn
    from sales_intake s
) s
where rn = 1