[FORMATTING] Treat QUALIFY similar to GROUP BY

[jgontarz]

Input data

Which SQL and options did you provide as input?
Language = Snowflake
All other options default

select
      "ID",
      "Pay" / 100 AS "Pay",
      "Status",
      "OpTtl" / 100 AS "OpTtl",
      "TestAmt" / 100 AS "TestAmt",
      CONVERT_TIMEZONE('UTC', $Timezone, "WrittenAt"::TIMESTAMP_NTZ) AS "LocalWrittenDt",
      CONVERT_TIMEZONE('UTC', $Timezone, "CompleteAt"::TIMESTAMP_NTZ) AS "LocalCompleteDt",
      CONVERT_TIMEZONE('UTC', $Timezone, "UpdatedAt"::TIMESTAMP_NTZ) AS "LocalUpdatedDt",
      CONVERT_TIMEZONE('UTC', $Timezone, "MessageTime"::TIMESTAMP_NTZ) as "ESTMessageTime",
      "ExternalAccountId" as "CustomerAccountId",
      "AffiliateId",
      "SourceSystemId",
      "DomainId",
      "RetailTicketId",
      "Test"
    from
      "StagingMARA"."Contract"
    where
      "ESTMessageTime" < $Todate Qualify dense_rank() over (
        partition by
          "ID"
        order by
          "ESTMessageTime" desc
      ) = 1

Expected Output

select
      "ID",
      "Pay" / 100 AS "Pay",
      "Status",
      "OpTtl" / 100 AS "OpTtl",
      "TestAmt" / 100 AS "TestAmt",
      CONVERT_TIMEZONE('UTC', $Timezone, "WrittenAt"::TIMESTAMP_NTZ) AS "LocalWrittenDt",
      CONVERT_TIMEZONE('UTC', $Timezone, "CompleteAt"::TIMESTAMP_NTZ) AS "LocalCompleteDt",
      CONVERT_TIMEZONE('UTC', $Timezone, "UpdatedAt"::TIMESTAMP_NTZ) AS "LocalUpdatedDt",
      CONVERT_TIMEZONE('UTC', $Timezone, "MessageTime"::TIMESTAMP_NTZ) as "ESTMessageTime",
      "ExternalAccountId" as "CustomerAccountId",
      "AffiliateId",
      "SourceSystemId",
      "DomainId",
      "RetailTicketId",
      "Test"
    from
      "StagingMARA"."Contract"
    where
      "ESTMessageTime" < $Todate 
    qualify dense_rank() over (
        partition by
          "ID"
        order by
          "ESTMessageTime" desc
                              ) = 1

Actual Output

select
  "ID",
  "Pay" / 100 AS "Pay",
  "Status",
  "OpTtl" / 100 AS "OpTtl",
  "TestAmt" / 100 AS "TestAmt",
  CONVERT_TIMEZONE('UTC', $Timezone, "WrittenAt"::TIMESTAMP_NTZ) AS "LocalWrittenDt",
  CONVERT_TIMEZONE('UTC', $Timezone, "CompleteAt"::TIMESTAMP_NTZ) AS "LocalCompleteDt",
  CONVERT_TIMEZONE('UTC', $Timezone, "UpdatedAt"::TIMESTAMP_NTZ) AS "LocalUpdatedDt",
  CONVERT_TIMEZONE('UTC', $Timezone, "MessageTime"::TIMESTAMP_NTZ) as "ESTMessageTime",
  "ExternalAccountId" as "CustomerAccountId",
  "AffiliateId",
  "SourceSystemId",
  "DomainId",
  "RetailTicketId",
  "Test"
from
  "StagingMARA"."Contract"
where
  "ESTMessageTime" < $Todate Qualify dense_rank() over (
    partition by
      "ID"
    order by
      "ESTMessageTime" desc
  ) = 1

Usage

• How are you calling / using the library?
  I am using this through Chrome - https://sql-formatter-org.github.io/sql-formatter/.

• What SQL language(s) does this apply to?
  Snowflake is all I have tried.

• Which SQL Formatter version are you using?
  v12.0.3

Including a link to the Snowflake documentation for Query Syntax and QUALIFY documentation.
https://docs.snowflake.com/en/sql-reference/constructs.html

https://docs.snowflake.com/en/sql-reference/constructs/qualify.html

[nene]

Thanks for reporting.

This should be a simple fix to make.

[nene]

This is now fixed in 12.0.4 release.

[jgontarz]

Thanks. Works great!