Attempting to calculate Month over Month change Using SQL

Queries and Resources
1

I am trying calculate Month over Month changes to values storied in a SQL database using SQL lag function.

It would seem that windows functions are not able to be using in the system? If that is the case, would it be possible to suggest a work around?

Any assistance would be greatly appreciated.

Current code:
select date_trunc('month', date_created) as date,
count() as count,
lag(count(), 1) over date
from rto_cosignattempt
where date_created >= 'Jan 1, 2023'
group by 1
order by 1

Error message:
datasource run failed.

    • message:"window "date" does not exist"
2

Hey there, you're correct that Retool currently doesn't support SQL window functions like LAG directly within data sources. However, there are a couple of workarounds you can consider.

If your database supports subqueries or self-joins, you might be able to restructure your query to achieve the same results without relying on LAG. Here's an example of a self-join approach:

SELECT

current_month.date_trunc_month AS date,

current_month.count AS count,

previous_month.count AS previous_month_count

FROM (
   SELECT date_trunc('month', date_created) AS date_trunc_month, COUNT(*) as count
   FROM rto_cosignattempt
   WHERE date_created >= 'Jan 1, 2023'
   GROUP BY 1
) current_month
LEFT JOIN (
   SELECT date_trunc('month', date_created) AS date_trunc_month, COUNT(*) as count
   FROM rto_cosignattempt`
   WHERE date_created >= 'Dec 1, 2022' AND date_created < 'Jan 1, 2023'
   GROUP BY 1
) previous_month ON current_month.date_trunc_month = previous_month.date_trunc_month + INTERVAL '1 month'
ORDER BY 1;

Otherwise, if restructuring the query isn't feasible, you can leverage Retool's custom JavaScript functionality to manipulate the data after fetching it. After you've fetched the data without the LAG function, you can use JavaScript's map or forEach methods to iterate through the data and create the desired month-over-month calculation. Then, you can store that data in a new variable or display it in a table.

Here is a guide for using custom JS in Retool and here is a stack overflow discussion you may find helpful.

3

Hey Matt, I think the issue here is just that you're referencing a column alias that the window function can't access. Since the window function behaves a bit like a subquery, it's scanning the rto_cosignattempt table looking for date before the outer query where you alias it runs. Just use the same date_trunc inside of the order statement and you'll be golden.

select 
date_trunc('month', date_created) as month,
count(*) as count,
lag(count(*), 1) over (order by date_trunc('month', date_created)) as prev_month_count
from rto_cosignattempt
where date_created >= '2023-01-01'
group by 1
order by 1
4

Thank you very much. I will try both suggestions.

5

You're welcome, Matt!