[Django]-Django: Count only the latest object after grouping by month

by

0👍

Well I noticed this is little over a day old, with no answer. While I cannot provide a direct solution as I do not speak Django. But perhaps I can point you in the right direction.
What you need is to reduce the count universe by first eliminating multiple versions by user. In direct sql this can be accomplished via a sub-select returning only max version per user with the outer select counting the result.

-- setup
create table django_count(userid integer, version numeric, dttz timestamp with time zone);
insert into django_count(userid, version, dttz ) 
 values (1, 3.1, now()-interval '1 month')
      , (2, 3.1, now()-interval '1 month' + interval '3 days')
      , (1, 3.2, now()-interval '1 month' + interval '5 days') 
      , (3, 3.1, now()-interval '1 month' + interval '7 days') ;
select * from django_count order by version desc;      

-- count query. This is what you need in raw sql.
select version, count(*)
  from (
        select userid, max(version) as Version, date_trunc('month',dttz) as "For Month"
          from django_count
         group by userid, date_trunc('month',dttz) 
       ) m
 group by version
 order by version;

If you can adapt this to Django I’m glad to help, if not I’m sorry I can’t help further. Good Luck.

👤Belayer

Leave a comment