Replicating FILTER clause from Postgres in Snowflake

Question

Postgres have a really useful method called FILTER which doesn't seem to be in Snowflake. In Postgres it works like this:

SELECT
  user_id,
  MIN(orders.started_at) FILTER (WHERE orders.sequence_in_subscription = 1) as time_of_t1
FROM platform.orders
GROUP BY 1

How would you do this in Snowflake? Is there such a concise way to do this?

Might be quite a basic question, but I'm fairly new to the Snowflake world and have only really done Postgres before. Thanks for any help in advance!

There is also IFF, a shorter way of writing case statement. See this other question with an example stackoverflow.com/a/55716546/1335793 — Davos

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2019-11-19T16:59:10

Yes, you may use a CASE expression in place of the FILTER clause:

SELECT
    user_id,
    MIN(CASE WHEN orders.sequence_in_subscription = 1 THEN orders.started_at END) AS time_of_t1
FROM platform.orders
GROUP BY
    user_id;

Replicating FILTER clause from Postgres in Snowflake

1 Answers