Django: Correct request to database to calculate DAU

Question

Could someone find a mistake(s) in my request to the database in Django? So, I have the next model:

class GameEvent(models.Model):    
    game = models.ForeignKey(Game, blank=False, on_delete=models.CASCADE)    
    name = models.CharField(max_length=255, blank=False)
    app_id = models.CharField(max_length=255, blank=False)
    datetime = models.DateTimeField(blank=False)        
    def __str__(self):      
        return u"%s [ %s ]" % (self.game.title, self.name)

@receiver(models.signals.pre_save, sender=GameEvent)
def update_datetime(sender, instance, **kwargs):
    instance.datetime = datetime.datetime.now()

I create the record in the database each time when my game sends me some event like:level complete, level failed etc. To recognize which copy of application sends me the events the copy has unique app_id. So the question is how to calculate the DAU. I make the next request to the database but it returns wrong answer:

dau = GameEvent.objects.filter(game = game, datetime__gte = date_start, datetime__lte = date_end)
           .extra({'date' :'date(datetime)'})
           .values('date')
           .annotate(count=Count('app_id', disntict=True))
           .order_by('date')   

If I choose the date range for one day it returns dau[0].count = 1200, but the next request which calculates the count of the unique app_id returns 100:

app_ids = GameEvent.objects.filter(game = game, datetime__gte = date_start, datetime__lte = date_end)
           .values_list('app_id', flat=True).distinct() 

So, where is mistake? Thanks for helping

Answer 1

dau = GameEvent.objects.filter(game = game, datetime__gte = date_start, datetime__lte = date_end)
           .extra({'date' :'date(datetime)'})
           .values('date')
           .annotate(count=Count('app_id', distinct=True))
           .order_by('date')   

Seems distinct spelling is wrong.

Another point is are you doing proper datetime formatting for Timezone etc.