Need help writing a druid subquery

I want to get something similar to a windowing function to get the most recent row for a list of messages.

I’m trying to get the where statement to execute, but the plan doesn’t seem to be working and I’m not sure why:

SELECT TIME_FORMAT(DATE_TRUNC(‘day’, “__time”), ‘y-MM-dd’) as “send_date”,

   message_id,
   SUM(CASE WHEN "status" = 'sent' THEN 1 ELSE 0 END) AS "send_size"

FROM “message_events”
WHERE “__time” in
(select max("__time")
FROM “message_events”
WHERE “message_id” in (‘message-1’, ‘message-2’))
AND “message_id” in (‘message-1’, ‘message-2’)
group by 1, 2
order by 1 DESC

``

Does anyone have any ideas on what we should be doing differently here?

The error that we’re getting here is:

Unknown exception (org.apache.druid.java.util.common.ISE): Cannot build plan for query: SELECT TIME_FORMAT(DATE_TRUNC(‘day’, “__time”), ‘y-MM-dd’) as “send_date”, message_id, SUM(CASE WHEN “status” = ‘sent’ THEN 1 ELSE 0 END) AS “send_size” FROM “message_events” WHERE “__time” in (select max("__time") FROM “message_events” WHERE “message_id” in (‘message-1’, ‘message-2’)) AND “message_id” in (‘message-1’, ‘message-2’) group by 1, 2 order by 1 DESC LIMIT 1000 – { “run_at”: 1559591652, “user_id”: “svenkat”, “username”: “svenkat”, “is_scheduled”: false, “worker”: “superset-production-iad-03667cf2aa5d87925”}

``

Hey Sreesha,

I believe this is a missing feature, and you can’t currently use an aggregation function in a subquery that is used as part of an IN filter. This issue is related: https://github.com/apache/incubator-druid/issues/8123. I expect we’ll be adding support for it in a future release.

As a workaround, how about writing a query like this:

SELECT TIME_FORMAT(DATE_TRUNC(‘day’, “__time”), ‘y-MM-dd’) as “send_date”,
message_id,
SUM(CASE WHEN “status” = ‘sent’ THEN 1 ELSE 0 END) AS “send_size”
FROM “message_events”
WHERE “message_id” in (‘message-1’, ‘message-2’)
GROUP BY 1, 2
ORDER BY __time DESC
LIMIT 1

``

It will get you the most recent message of type “message-1” or “message-2”.

Gian