0

Input Sql

WITH first_activity AS (
  SELECT USER_ID, MIN(TO_TIMESTAMP(EVENT_DATE)) AS first_activity_date
  FROM DF.USER_EVENTS
  GROUP BY USER_ID
)
SELECT COUNT(*) AS active_users_count
FROM first_activity
WHERE DATEDIFF(day, first_activity_date, CURRENT_TIMESTAMP()) > 90

I am using sqlglot transpile function in the following manner sqlglot.transpile(query, write="snowflake", pretty=True)[0]

Output Query

WITH first_activity AS (
  SELECT
    USER_ID,
    MIN(TO_TIMESTAMP(EVENT_DATE)) AS first_activity_date
  FROM DF.USER_EVENTS
  GROUP BY
    USER_ID
)
SELECT
  COUNT(*) AS active_users_count
FROM first_activity
WHERE
  DATEDIFF(, first_activity_date, day) > 90

The output query is failing as there is a leading comma in the DATEDIFF function. Moreover I'm not sure why it changed the function signature and parameters altogether.

I was expecting the query to be the same with some prettiness.

qwertyuiop
  • 39
  • 4
  • 1
    DATEDIFF also accepts string for the time token, so you could try `DATEDIFF('day', first_activity_date, CURRENT_TIMESTAMP())` and then the constant string token might be handled more correctly. – Simeon Pilgrim Aug 16 '23 at 07:57
  • I would explicitly set the read parameter, also possibly set the log level as high as possible – NickW Aug 16 '23 at 19:41

0 Answers0