SNOW-890471: Support QUALIFY

[ehellmann-nydig]

Currently, I don't see any way that the QUALIFY clause is supported.

1. What version of Python are you using?

Python 3.8.12 (default, Nov 5 2021, 11:45:44)
[Clang 13.0.0 (clang-1300.0.29.3)]

2. What operating system and processor architecture are you using?

macOS-13.5-x86_64-i386-64bit

3. What are the component versions in the environment (pip freeze)?

acachecontrol==0.3.5
aiohttp==3.8.4
aiosignal==1.3.1
anyio==3.6.2
arabic-reshaper==3.0.0
asn1crypto==1.5.1
async-timeout==4.0.2
attrs==20.1.0
aws-sam-translator==1.26.0
backports.zoneinfo==0.2.1
boto==2.49.0
boto3==1.18.2
botocore==1.21.2
cachetools==5.3.0
certifi==2020.6.20
cffi==1.14.2
cfgv==3.3.1
cfn-lint==0.35.0
chardet==3.0.4
charset-normalizer==2.0.12
click==8.1.3
cryptography==3.3.2
cssselect2==0.7.0
datadog==0.41.0
datadog-lambda==3.60.0
ddtrace==0.61.5
decorator==4.4.2
distlib==0.3.6
docker==4.3.0
ecdsa==0.14.1
exceptiongroup==1.1.2
fastapi==0.79.1
filelock==3.10.0
flake8==3.8.3
freezegun==1.2.2
frozenlist==1.3.3
fsspec==2023.6.0
future==0.18.2
greenlet==2.0.2
gunicorn==20.1.0
h11==0.14.0
html5lib==1.1
httplib2==0.19.0
identify==2.5.21
idna==2.9
importlib-metadata==1.7.0
iniconfig==1.0.1
Jinja2==3.1.2
jmespath==0.10.0
jsondiff==1.1.2
jsonpatch==1.26
jsonpickle==1.4.1
jsonpointer==2.0
jsonschema==3.2.0
junit-xml==1.9
keyring==22.3.0
lxml==4.9.2
MarkupSafe==2.1.2
mccabe==0.6.1
mirakuru==2.3.0
mock==4.0.2
more-itertools==8.4.0
moto==3.1.18
multidict==6.0.4
mypy==0.961
mypy-extensions==0.4.4
networkx==2.4
nodeenv==1.7.0
numpy==1.24.3
oauthlib==3.1.0
okta-jwt-verifier==0.2.4
oscrypto==1.3.0
packaging==20.4
pandas==1.5.3
pg8000==1.16.5
PGPy==0.5.4
Pillow==9.4.0
platformdirs==3.1.1
pluggy==0.13.1
port-for==0.4
pre-commit==2.21.0
protobuf==3.20.3
psutil==5.7.2
psycopg2-binary==2.8.4
py==1.10.0
pyarrow==10.0.1
pyasn1==0.4.8
pyasn1-modules==0.2.8
pycodestyle==2.6.0
pycparser==2.20
pycryptodomex==3.18.0
pydantic==1.8.2
pyfakefs==5.2.2
pyflakes==2.2.0
pyHanko==0.17.2
pyhanko-certvalidator==0.20.1
PyJWT==2.8.0
PyMuPDF==1.21.1
pyOpenSSL==21.0.0
pyparsing==2.4.7
pypdf==3.6.0
pypng==0.20220715.0
pyrsistent==0.16.0
pytest==7.4.0
pytest-env==0.6.2
pytest-pgsql==1.1.2
pytest-postgresql==2.4.1
python-bidi==0.4.2
python-dateutil==2.8.2
python-dotenv==0.20.0
python-jose==3.2.0
pytz==2022.7.1
pytz-deprecation-shim==0.1.0.post0
PyYAML==6.0
qrcode==7.4.2
reportlab==3.6.12
requests==2.26.0
requests-mock==1.5.2
requests-oauthlib==1.3.0
responses==0.21.0
retry2==0.9.5
rsa==4.7
s3fs==0.4.2
s3transfer==0.5.0
scramp==1.2.0
six==1.15.0
sniffio==1.3.0
snowflake-connector-python==3.1.0
snowflake-sqlalchemy==1.4.7
sortedcontainers==2.4.0
SQLAlchemy==1.4.47
starlette==0.19.1
structlog==21.5.0
svglib==1.5.1
tenacity==8.2.2
testing.common.database==2.0.3
testing.postgresql==1.3.0
tinycss2==1.2.1
toml==0.10.1
tomli==2.0.1
tomlkit==0.12.1
typed-ast==1.4.1
types-cachetools==5.3.0.4
types-mock==4.0.2
types-python-dateutil==2.8.19.10
types-requests==2.26.0
typing_extensions==4.5.0
tzdata==2022.7
tzlocal==4.3
uritemplate==3.0.1
uritools==4.0.1
urllib3==1.26.5
uvicorn==0.18.3
virtualenv==20.21.0
webencodings==0.5.1
websocket-client==1.5.1
Werkzeug==1.0.1
wrapt==1.15.0
xhtml2pdf==0.2.9
xlrd==1.2.0
xmltodict==0.13.0
yarl==1.8.2
zipp==3.1.0

4. What did you do?

from datetime import datetime

import snowflake.sqlalchemy.snowdialect
from sqlalchemy import select, Column, DateTime, INT, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.functions import rank

Base = declarative_base()

class TestTbl(Base):
    __tablename__ = "test"
    id = Column(INT, primary_key=True)
    processing_timestamp = Column(DateTime(timezone=True), nullable=False)
    booking_id = Column(INT, nullable=False)
    description = Column(String)


if __name__ == '__main__':
    query = select(TestTbl.booking_id, TestTbl.description).filter(TestTbl.processing_timestamp > datetime(2023,3,5))
    a = rank().over(partition_by=TestTbl.booking_id,
                    order_by=[TestTbl.booking_id, TestTbl.processing_timestamp.desc(), TestTbl.id.desc()]
                    ) == 1
    qry = query.having(a)
    actual = qry.compile(dialect=snowflake.sqlalchemy.snowdialect.dialect(), compile_kwargs={'literal_binds': True})
    print(str(actual))
# prints
# SELECT test.booking_id, test.description 
# FROM test 
# WHERE test.processing_timestamp > '2023-03-05 00:00:00.000000' 
# HAVING rank() OVER (PARTITION BY test.booking_id ORDER BY test.booking_id, test.processing_timestamp DESC, test.id DESC) = 1

5. What did you expect to see?

should print

SELECT test.booking_id, test.description 
FROM test 
WHERE test.processing_timestamp > '2023-03-05 00:00:00.000000' 
QUALIFY rank() OVER (PARTITION BY test.booking_id ORDER BY test.booking_id, test.processing_timestamp DESC, test.id DESC) = 1

6. Can you set logging to DEBUG and collect the logs?

   N/A. This example doesn't log anything

[sfc-gh-sfan]

Hey this is useful feature and we will add to our backlog, but we won't be able to prioritize this unfortunately. If you are open to create a PR, we will be happy to review!