How would I write the following SQL query in Django.

SELECT COUNT(*)
FROM accounts_transactions
LEFT JOIN accounts_notes
ON accounts_notes.accounts_core_id = accounts_transactions.accounts_core_id
WHERE email_status='clicked' AND email_template_id IS NOT NULL 
AND accounts_transactions.creation_date >= accounts_notes.creation_date
AND accounts_transactions.creation_date <= accounts_notes.creation_date + INTERVAL 7 DAY;

The way the models are structured are.

Account(model):
    id

AccountTransactions(model):
    creation_date
    account_core_id = Account

AccountNote(model):
    account_core_id = Account
    creation_date
    email_status
    email_template

The query works fine but I can't seem to figure out how to do write this in django. Reading about prefetch related, but I don't see how I do it, since there is no direct one to connection between note and transcation.

Update: SOLUTION

queryset = AccountTransaction.objects.filter(
            account__note__email_template__isnull=False,
            account__note__email_status='clicked',
            creation_time__gte=F('account__note__creation_time'),
            creation_time__lte=F(
                'account__note__creation_time') + timedelta(days=7)
        )