5

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!

mylescc
  • 5,720
  • 3
  • 17
  • 23
  • 1
    There is also IFF, a shorter way of writing case statement. See this other question with an example https://stackoverflow.com/a/55716546/1335793 – Davos Jun 15 '20 at 07:33

1 Answers1

7

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 2
    OK, great thanks! I thought there might be a more concise way but this will work. Thanks for the help – mylescc Nov 19 '19 at 17:00