Week over week % change

Hi,

Is it possible to get Week over Week percentage change for a an aggregation (or a post aggregation) with a Druid query?

Thanks,

Kalyan

Reposting to see if there are any ideas.
I looked at https://druid.apache.org/docs/latest/development/extensions-contrib/moving-average-query.html which does not support previous day/week aggregation.

This is typically done with with LAG window function, which Druid SQL does not support yet.

So, I had a look at this article (for T-SQL not for Druid SQL!!!) as the author really seems to have thought about solving this problem in SQL from lots of angles!

https://www.red-gate.com/simple-talk/sql/t-sql-programming/calculating-values-within-a-rolling-window-in-transact-sql/

I don’t think any of the functionality you’ll need is in Druid SQL though - like WIndows, JOINs back to the same data source, etc. - I suspect you might need to do something programmatic, like running multiple date trunc queries and computing it manually… https://druid.apache.org/docs/latest/querying/sql.html#time-functions

I mean I could be wrong… I often am…! :smiley:

Thanks Peter for the inputs.

Druid self join with TIME_SHIFT works nicely. Since I’m using BI tool (apache superset), this method can not work.
That’s why I’m looking for Window function.

SELECT
t1.__time,
t1.reqs,
t2.reqs as shifted_reqs,
t2.reqs-t1.reqs as increase,
round((t2.reqs-t1.reqs)*100.0/t1.reqs,2) as increase_percentage

from
(SELECT
__time,
TIME_SHIFT(__time,‘P1D’,-1) as shiftedtime,
sum(sum_requests) as reqs
FROM “supply_funnel”
WHERE “__time” >= CURRENT_TIMESTAMP - INTERVAL ‘7’ DAY
group by 1)t1
left join
(SELECT
__time,
sum(sum_requests) as reqs
FROM “supply_funnel”
WHERE “__time” >= CURRENT_TIMESTAMP - INTERVAL ‘8’ DAY
group by 1
)t2 on t1.shiftedtime = t2.__time

1 Like