how to realized YOY/MOM on druid?

[xqwbx163]

how to realize YOY or MOM on druid sql (or json file) ? just like this : can you help me ?

Month X – Month Y numbers
_______________________ x 100 = Percentage Growth
Month X

Year X – Year Y numbers
_______________________ x 100 = Percentage Growth
Year X number

[vogievetsky]

This is not Druid specific (more like general SQL) but you would do:

SELECT
  COUNT(*) AS "BothMonths",
  COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00') AS "MonthX",
  COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') AS "MonthY",
  (
    COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') -
    COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
  ) / 
  CAST(
    COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
  AS DOUBLE)
  * 100 AS "MonthOverMonth"
FROM "wikiticker"
WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00'

Or (with a grouping):

SELECT
  "channel",
  COUNT(*) AS "BothMonths",
  COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00') AS "MonthX",
  COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') AS "MonthY",
  (
    COUNT(*) FILTER(WHERE TIMESTAMP '2019-05-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00') -
    COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
  ) / 
  CAST(
    COUNT(*) FILTER(WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-05-01 00:00:00')
  AS DOUBLE)
  * 100 AS "MonthOverMonth"
FROM "wikiticker"
WHERE TIMESTAMP '2019-04-01 00:00:00' <= "__time" AND "__time" < TIMESTAMP '2019-06-01 00:00:00'
GROUP BY "channel"
ORDER BY "MonthOverMonth" DESC

Obviously you do not need all those aggregates, I am just demonstrating.

The idea is that you filter the overall query to both time intervals and then filter each aggregate to a specific interval

[xqwbx163]

thanks for you help,i try to accord to your method, it really succeeded.