Column showing revenue increasing cumulatively, then only repeats the max value after a certain date

Question

I'm creating a dashboard that, among other things, shows how much revenue a rep generated in their first month of joining the company.

This is going to be based off a table I'm creating with SQL. The simplest version of this table will be:

date (which will be every day of the week), rep name, revenue generated from when they joined.

This will be increasing cumulatively (so when you select a date in the dashboard, you see the total amount generated up until that day).

As we're only interested in revenue generated in the first month, I want my revenue table to be increasing cumulatively up until we get to 1 month from start date, then, whatever that max number is, to just be repeated all the way down my column. So after the first month, any date you select in the dashboard, you will still only see the 1 month number. I want to do it this way as I think it'll be easier to do in SQL, and not my dashboard tool, as then I have to bring through more data and run further calculations there too.

To do this, I first created a sub query:

(SELECT date, rep name, sum(revenue) as revenue
FROM rev Group BY 1,2) as a

So this gives me revenue generated on every single day for the rep.

I'm then querying this sub query, whilst joining to a table giving me start date. So my new query starts like this:

SELECT a.date, a.rep name,

SUM(CASE WHEN (a.date- b.join_date < 1 month) then sum(a.revenue)

I'm now stuck on the ELSE part. Do I want to partition here?

My thoughts are something like:

ELSE sum(revenue) partition over(rep name, add_months(join_date, 1) order by date asc)

But I don't think that makes a lot of sense?

Essentially, what I want to see as final output is this. In order to keep the table short, I will assume I want revenue in the first week of them joining (not month like I actually want):

In this example, I am pretending the rep started generating money on the first day of them joining, which is the 2020-02-19. What I have in the first three columns is the current output of my sub query (the individual amounts they generated on each day). I want to aggregate that up to another query, which gives me the 4th column. I hope this makes things a little clearer!

Answer 1

As Fred commented, a conditional agregation in a running total should be what you want:

SELECT date, rep_name, sum(revenue) as revenue
  ,sum(CASE WHEN a.date <= add_months(b.join_date, 1) then sum(revenue) END) 
   over (partition by rep_name
         order by date
         rows unbounded preceding) as monthly_rev
FROM rev as a
join ...
Group BY 1,2