I have a "puzzle of the day" that users can answer. I track the puzzle of the day using the Puzzle model. I track the answers using the PuzzleAnswer model. I would like to calculate the number of consecutive puzzles a particular user (i.e., the author) gets right in a row. The current SQL I use that can calculate the start date of the streak, end date of the streak and the number of days in the streak. As you can see, it does a dens_rank over the puzzles (to count them in order), then does a join with the PuzzleAnswer, then does a second dense rank over the merged tables. I figured out how to use the DenseRank function in the Django ORM on the Puzzle manager, but I cannot figure out how to do the left join next. Any advice?
SELECT min(s.id) AS id, count(s.date) AS streak, min(s.date) AS start_streak, max(s.date) AS end_streak, s.author_id FROM ( SELECT dense_rank() OVER (ORDER BY ROW(pa.author_id, pr.rank)) AS id, pa.created AS date, (pr.rank - dense_rank() OVER (ORDER BY ROW(pa.author_id, pr.rank))) AS g, pa.author_id FROM (( SELECT "POTD_puzzle".id, dense_rank() OVER (ORDER BY "POTD_puzzle".published) AS rank FROM public."POTD_puzzle") pr JOIN public."POTD_puzzleanswer" pa ON ((pr.id = pa.puzzle_id))) WHERE pa.correct) s GROUP BY s.author_id, s.g ORDER BY count(s.date) DESC; The models are: class PuzzleAnswer(models.Model): puzzle = models.ForeignKey(Puzzle, editable=True, on_delete=models.CASCADE) answer = models.CharField(max_length=64) correct = models.BooleanField(editable=False) created = models.DateTimeField(auto_now_add=True) author = models.ForeignKey(settings.AUTH_USER_MODEL, blank=True, null=True, on_delete=models.SET_NULL) class Puzzle(models.Model): category = models.ForeignKey(PuzzleCategory, on_delete=models.CASCADE, help_text=category_help) notation = models.CharField(max_length=64) correct_answer = models.CharField(max_length=64) published = models.DateField(blank=True, null=True, db_index=True, unique =True) -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/3d8106a7-df44-4697-91fe-06c861132ae6n%40googlegroups.com.