in presto, how to use max_by twice with two conditions on the same filed?

Question

I want to use max_by(event_id, date_created) twice: once when date_created<= first_upgrade_date one once when date_created<= prediction_point is there a way to do that in one query instead of two (in each one using different condition in where)

Answer 1

Use filtered aggregations. Example:

SELECT
  max_by(event_id, date_created) FILTER (WHERE date_created <= first_upgrade_date),
  max_by(event_id, date_created) FILTER (WHERE date_created <= prediction_point)
FROM ...