show results of sql code per month in one view

Question

I have this SQL statement that generates the revenue, the total customer and revenue for new business.

I would like to amend my SQL to allow it to show the same result but also run it for the previous 3 years with the same logic.

One option is to use a union and amend every time the current_date with DATEADD function by -1 , -2 etc. But this would be so inefficient.

Is there a better way to amend the code? with a date dimension maybe?

select date_trunc('month',current_date),
COUNT(DISTINCT CASE WHEN (case when RELEVANT_DATE_OUTBOUND> current_date then TOTAL_REVENUE end)>0 THEN CUSTOMER_NAME end) CUSTOMER_ID,
SUM(CASE WHEN (case when date_trunc('month',reporting_date) = date_trunc('month',current_date) then NB_EUR end)>0 THEN nb_eur end) nb_eur
from REVENUE_DATABASE_AGR_VIEW

Please find attached the dataset and result of the SQL and desired outcome.

dataset:

---

results:

desired outcome:

Answer 1

I would suggest that you create a date_dim table and use that to provide the date values to join with your query as the date parameter.

You could also use a loop, but row based processing is pretty non-performant in Snowflake.

However, if you're only running it once to populate the prior 3 years, and then once per day only to do each new day's data, then you could wrap your existing code in a loop like the following to process all the historical data, and then use your query with just the current_date()-1 and run daily prospectively:

create
or replace table date_dim (loop_date varchar);

declare ctr number(4, 0);
begin ctr: = 0;
while (ctr <= 1095) do -- create date for prior 3 years
   insert into
       date_dim
   select
       current_date() - :ctr;
   ctr: = ctr + 1;
end while;
end;