django aggregation query

factories.py

import factory

from broschures import models

class BroschureFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = models.Broschure


class BroschureClickFactory(factory.django.DjangoModelFactory):
    class Meta:
        model = models.BroschureClick

models.py

from django.db import models
import datetime
from django.utils.timezone import make_aware


class BroschureManager(models.Manager):
    def with_clicks(self, number, year, month):
        start = make_aware(datetime.datetime(year, month, 1, 0, 0, 0))
        end = make_aware(
            datetime.datetime(year, month + 1, 1, 0, 0, 0) - datetime.timedelta(days=1)
        )
        return self.annotate(num_clicks=models.Sum(
            models.Case(
                models.When(
                    clicks__created__range=(
                        start, end
                    ),
                    then=1,
                ),
                default=0,
                output_field=models.IntegerField(),
            )
            )
        ).filter(num_clicks__gt=number)


class Broschure(models.Model):
    name = models.CharField(max_length=255)
    objects = BroschureManager()



class BroschureClick(models.Model):
    broschure = models.ForeignKey(
        'broschures.Broschure', related_name='clicks', on_delete=models.CASCADE
    )
    created = models.DateTimeField(auto_now_add=True)

tests.py

from django.conf import settings
from django.urls import reverse
from django.utils.http import urlencode
import pytest
from broschures import models, factories
import pytz
from unittest import mock
import datetime
from django.utils.timezone import make_aware


pytestmark = pytest.mark.django_db


def test__with_a_match(client):
    broschure = factories.BroschureFactory()
    mocked = make_aware(datetime.datetime(2019, 10, 10, 0, 0, 0))
    with mock.patch('django.utils.timezone.now', mock.Mock(return_value=mocked)):
        for x in range(30):
            factories.BroschureClickFactory(broschure=broschure)
        assert models.Broschure.objects.with_clicks(29, 2019, 10).count() == 1

def test__with_no_match(client):
    broschure = factories.BroschureFactory()
    mocked = make_aware(datetime.datetime(2019, 8, 10, 0, 0, 0))
    with mock.patch('django.utils.timezone.now', mock.Mock(return_value=mocked)):
        for x in range(3):
            factories.BroschureClickFactory(broschure=broschure)

        assert models.Broschure.objects.with_clicks(1, 2019, 10).count() == 0

It will generate query below:

SELECT          "broschures_broschure"."id", 
                "broschures_broschure"."name", 
                sum( 
                CASE 
                                WHEN "broschures_broschureclick"."created" BETWEEN 2019-10-01 00:00:00 AND             2019-10-31 00:00:00 THEN 1 
                                ELSE 0 
                END) AS "num_clicks" 
FROM            "broschures_broschure" 
LEFT OUTER JOIN "broschures_broschureclick" 
ON              ( 
                                "broschures_broschure"."id" = "broschures_broschureclick"."broschure_id")
GROUP BY        "broschures_broschure"."id", 
                "broschures_broschure"."name" 
HAVING          sum( 
                CASE 
                                WHEN ( 
                                                                "broschures_broschureclick"."created" BETWEEN 2019-10-01 00:00:00 AND             2019-10-31 00:00:00) THEN 1 
                                ELSE 0 
                END) > 1```