sql how to get models sorted by foreign key amount field of last month

I had 2 tables

User and Product

for user
    id
for product
    id
    amount - price of this product
    user_id - foreign key
    created - date of creating product

i need to get list of Users sorted by sum of amounts of their products that they created by last month

Thus, i shoud to get the number of users sorted by the largest product amount to the smallest amount sum, but only by the number of products created in the last month and newer.

how to do this in sql?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

select u.user_id, u.name, ..., sum(p.amount) as total
from user as u
left outer join product as p
    on u.id = p.user_id
where p.created >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)
group by u.user_id, u.name...
order by total desc, u.name asc;

You tagged your question peewee, but asked for the answer with SQL. Please update if you want help writing this with Peewee.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x