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!

Woo

There is also IFF, a shorter way of writing case statement. See this other question with an example https://mcmap.net/q/1773572/-how-do-i-set-a-number-of-rows-and-count-the-conditional-results – Jealousy

Answer 1

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;

Rosetta

OK, great thanks! I thought there might be a more concise way but this will work. Thanks for the help – Woo