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

Question

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?

score 0 · Accepted Answer · answered Aug 11 '21 at 21:13

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.

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

1 Answers1