I have table “tasks” with following fields:

id:bigint
canceled:date
done:date

And I have SQL query in this form:

SELECT date_trunc('day', date):: date as date,
(SELECT COUNT(*) FROM tasks WHERE date>="canceled") "canceled",
(SELECT COUNT(*) FROM tasks WHERE date>="done" AND (date<"canceled" or canceled isnull)) "done"
FROM generate_series('2020-01-01', '2020-01-10', '1 day'::interval) AS date
ORDER BY date

And I spent a lot of time googling if it is possible to wrap it in ecto query form without any success.

The main problem is I can’t write something like from( date in fragment(...)) and I can’t see how I can reformulate this query using joins to be able to fit into ecto restrictions on fragment() usage, because subqueries are using “less-than” or “more-than” comparisons of dates.

Any suggestions are welcome.