CODEWITHSUNDEEP
mysqlsqle-commerce
TwinAxe96
TwinAxe96

Reputation: 139

SQL to get top 3 most ordered products for every user by quantity sold

I am working on a school project the idea of which is an eCommerce site for selling beers.

I need to get the top 3 most ordered products for every individual user that has ordered something. My database includes 4 tables: users, products, orders and order_detail. The diagram is:

https://imgur.com/a/PofTxjQ

I think I will have to join all 4 of the tables to get that info, but I can't figure out the correct way to do it. Here is what I have generated: 

SELECT u.username, p.`id` AS productId, p.`name`, od.`quantity` AS quantity
FROM `order_detail` AS od
    INNER JOIN `products` AS p
    INNER JOIN `users` AS u
    ON od.`product_id` = p.`id`
GROUP BY od.`order_id`, p.name
ORDER BY od.`quantity` DESC, p.`name` ASC

The database script: https://pastebin.com/BvQLGqur

Upvotes: 0

Views: 3522

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

In order to limit the rows returned to show just the top 3 products ordered for each user, you'll need to use a sub-query with a limit clause on the order_detail.

Other than that, it just a simple join between the 4 tables.

SELECT
    a.`user_name`,
    d.`id` as `product_id`,
    d.`name` as `product_name`,
    SUM(c1.`quantity`) as `total_quantity`,
    d.`price` as `product_price`,
    d.`price` * SUM(c1.`quantity`) as `total_spent`
FROM `users` a
JOIN `orders` b
    ON b.`user_id` = a.`id`
JOIN (SELECT c.`order_id`, c.`product_id`, SUM(c.`quantity`) as `num_ordered`
        FROM `order_detail` c
        ORDER BY `num_ordered` DESC
        LIMIT 3) as c1
    ON c1.`order_id` = b.`id`
JOIN `products` d
    ON d.`id` = c1.`product_id`
GROUP BY a.`id`,d.`product_id`
ORDER BY a.`user_name`,`total_quantity` DESC, d.`name`;

Upvotes: 1

Related Questions