I have the following query in PostgreSQL:
SELECT SUM(revenue) as revenue, AVG(revenue) as revenue_avg,
TO_CHAR(a.report_date, ‘IYYY-IW’) as weekdate
FROM table a
INNER JOIN (
SELECT site_id, product_id
WHERE site_id = 19
GROUP BY product_id, site_id
HAVING revenue > 200
) b ON a.site_id = b.site_id AND a.product_id = b.product_id
Is it possible to get a similar result in Druid? These are for our ‘trended’ queries, where a user might filter on a calculated column by product_id and we return a table of that data, plus a total of that data aggregated by week that is graph-able.
I don’t think it works in a nested subquery since report_date isn’t part of the base query’s dimensions since the base query would have the equivalent of ‘all’ granularity in Druid, but the top level query has a weekly granularity.