python - Django ORM: window function with subsequent filtering

Question

Answering this question, I found out that window functions are not allowed to combine with filter (technically, they are, but filter clause affects the window). There is a hint to wrap window function in an inner query, so that final SQL looks like this (as I understand):

SELECT * FROM (
    SELECT *, *window_function* FROM TABLE)
WHERE *filtering_conditions*

The question is: how can I write this query with Django ORM?

Answer 1

Another solution is Common Table Expressions (CTE), and with the help of django-cte, you could achieve what you want:

cte = With(
    YouModel.objects.annotate(
        your_window_function=Window(...),
    )
)

qs = cte.queryset().with_cte(cte).filter(your_window_function='something')

Which translates roughly to:

WITH cte as (
    SELECT *, WINDOW(...) as your_window_function
    FROM yourmodel
) 
SELECT * 
FROM cte
WHERE cte.your_window_function = 'something'