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?
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.
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.