Count customers per period

Question

In my django project, it is possible to show every customer in the application with CustomerProfile.objects.all() and find the creation date of a specific customer with

In [12]: cust = CustomerProfile.objects.get(pk=100)

In [13]: cust.user.date_joined
Out[13]: datetime.datetime(2017, 7, 28, 14, 43, 51, 925548)

In [14]: cust
Out[14]: <CustomerProfile: FistName LastName's customer profile>

According to the creation date, I would like to make a listing of how many customers has been created per day, week, month or year. An example of the result could be

...
week 28 : [ 09/07/2017 - 15/07/2017 ] - Count : 201 customers
...

I probably need a range start_date and end_date where we will list that kind of information. start_date will be the date of the first customer created and the start_week created would be the week of this first_date. Obviously, the end_date is the date of the last customer created and last_week is the week of this end_date.

For instance, if I select Per week in the drop-down menu and press Apply in the form, I want to send information to my model in such I could code what I explained.

So far I know how to count the number of client in a range of two date:

CustomerProfile.objects.filter(user__date_joined__range=["2017-09-03", "2017-09-09"])

How could I update dates here in such a way we could get a list between the start_week and the end_week? (A full answer would be appreciated) A full answer would be appreciate, if possible. I think I could find the first and last date with the .first() and .last() object :

first_date = CustomerProfile.objects.first().user.date_joined
last_date  = CustomerProfile.objects.last().user.date_joined

I think this question was already answered here: https://stackoverflow.com/a/8746532/2729584 . Correct me if I am wrong. — dethos, Sep 24 '17 at 13:57
Yes, you are wrong. Please read the question again. @J.Doe Although the question is good, there are not many people who will be able to answer this question appropriately. — , Sep 24 '17 at 14:07

score 0 · Answer 1 · answered Sep 24 '17 at 16:50

Do you have to use the ORM with this? Trivially solvable in SQL:

SELECT
    date_trunc('week', created) as "week_created",
    count(customerprofile)
FROM customerprofile
GROUP BY 1
ORDER BY 1 ASC;

The form could take in the truncate parameter and then start/stop ranges for filtering the results.

Count customers per period

1 Answers1