Talal qadoummi Talal qadoummi - 7 months ago 29
SQL Question

Calculate the variation of values in amazon redshift database

I'm trying to calculate the variation of two values between current month and the previous one.
let's say I have a total calls in different months and and want to have the variation for each month from the previous one.
U have a table contains vendor, month and calls in each month
I have tried the following query nut it gives a wrong results for the same vendor if there is no data in the previous month

select vendor,
nvl(round(sum(calls),0),0.00) as "total_calls",
nvl((((lag(CAST(sum(calls) AS decimal) ,0) over(order by month)) -
(lag(CAST(sum(calls) AS DECIMAL),1) over(order by month))) /
(lag(CAST(sum(calls) AS DECIMAL),1) over(order by month))), 0) as tot_calls_variation
from table_summary
group by full_month,vendor
order by month,vendor


the lag() function returns the row by given index.but this gives wrong results since the variation is calculated by rows and not by each vendor
wondering if there is any other way to do so ? thanks

Answer

It's difficult to say without seeing your data and desired results, but perhaps you might be better of just going with a self join instead of window functions:

SELECT
    month_summary.vendor,
    month_summary.calls,
    month_summary.calls - prev_month_summary.calls / prev_month_summary.calls) as tot_calls_variation
FROM
    (SELECT vendor, full_month, sum(calls) as calls FROM table_summary GROUP BY vendor, full_month) as month_summary
    INNER JOIN (SELECT vendor, full_month, sum(calls) as calls FROM table_summary GROUP BY vendor, full_month) as prev_month_summary ON
        month_summary.vendor = prev_month_summary.vendor AND
        month_summary.full_month - 1 = prev_month_summar.full_month