Question 21022 in Mysql Question posted in
The official MySQL documentation can be found here.

Get the top 200 distributors – Mysql

Given a data set of orders, I want to be able to get the distributors ranked in the top 200 based on the total amount of sales generated such that users having the same (total sales) figure are ranked equally:

For example:

Rank User Total Sales
1 25944 22026.75
2 26322 19756
2 11821 19756

I have a number of tables involved here: users, categories, user_category, orders, order_items, and products.

I have been able to write a query to compute the total sales per users using all the tables i mentioned earlier but i’m not ranking users correctly at the moment

SELECT 
        `customer`.`referred_by` AS `user_id`,
        SUM(`customer_orders`.`quantity_purchased`) AS `total_sales`
    FROM
        (((`users` `customer`
        LEFT JOIN `users` `referrer` ON ((`customer`.`referred_by` = `referrer`.`id`)))
        JOIN (SELECT 
            `user_category`.`user_id` AS `user_id`,
                `user_category`.`category_id` AS `category_id`,
                `categories`.`name` AS `category_name`
        FROM
            (`user_category`
        JOIN `categories` ON ((`categories`.`id` = `user_category`.`category_id`)))) `user_cat` ON ((`user_cat`.`user_id` = `customer`.`id`)))
        JOIN (SELECT 
            `orders`.`id` AS `order_id`,
                `orders`.`invoice_number` AS `invoice_number`,
                `orders`.`purchaser_id` AS `purchaser_id`,
                `orders`.`order_date` AS `order_date`,
                `items`.`quantity` AS `order_quantity`,
                `items`.`product_id` AS `product_id`,
                `products`.`name` AS `product_name`,
                `products`.`price` AS `product_price`,
                `products`.`sku` AS `product_sku`,
                (`products`.`price` * `items`.`quantity`) AS `quantity_purchased`
        FROM
            ((`orders`
        JOIN `order_items` `items` ON ((`orders`.`id` = `items`.`order_id`)))
        JOIN `products` ON ((`items`.`product_id` = `products`.`id`)))) `customer_orders` ON ((`customer`.`id` = `customer_orders`.`purchaser_id`)))
    GROUP BY `customer`.`referred_by`
    ORDER BY `total_sales` DESC

This is the data i’m working with.

Tags:

2

Answers


  1. 0

    This is a really nice guide to Rank(). Well worth taking a few minutes to scroll through it and check out the examples. Breaks it out in a way easy to consume and implement.

    SELECT 
        RANK () OVER ( ORDER BY SUM(`customer_orders`.`quantity_purchased`) DESC) AS `Rank`,
        `customer`.`referred_by` AS `user_id`,
        SUM(`customer_orders`.`quantity_purchased`) AS `total_sales`
    FROM
        (((`users` `customer`
        LEFT JOIN `users` `referrer` ON ((`customer`.`referred_by` = `referrer`.`id`)))
        JOIN (SELECT 
            `user_category`.`user_id` AS `user_id`,
                `user_category`.`category_id` AS `category_id`,
                `categories`.`name` AS `category_name`
        FROM
            (`user_category`
        JOIN `categories` ON ((`categories`.`id` = `user_category`.`category_id`)))) `user_cat` ON ((`user_cat`.`user_id` = `customer`.`id`)))
        JOIN (SELECT 
            `orders`.`id` AS `order_id`,
                `orders`.`invoice_number` AS `invoice_number`,
                `orders`.`purchaser_id` AS `purchaser_id`,
                `orders`.`order_date` AS `order_date`,
                `items`.`quantity` AS `order_quantity`,
                `items`.`product_id` AS `product_id`,
                `products`.`name` AS `product_name`,
                `products`.`price` AS `product_price`,
                `products`.`sku` AS `product_sku`,
                (`products`.`price` * `items`.`quantity`) AS `quantity_purchased`
        FROM
            ((`orders`
        JOIN `order_items` `items` ON ((`orders`.`id` = `items`.`order_id`)))
        JOIN `products` ON ((`items`.`product_id` = `products`.`id`)))) `customer_orders` ON ((`customer`.`id` = `customer_orders`.`purchaser_id`)))
    GROUP BY `customer`.`referred_by`
    ORDER BY `total_sales` DESC
    Login or Signup to reply.
  2. 0

    Why all the sub-queries? Your server is having to do a lot more work than it needs to.

    SELECT *
FROM (
    SELECT
        RANK() OVER (ORDER BY SUM(`p`.`price` * `oi`.`quantity`) DESC) `rank`,
        `customer`.`referred_by`,
        SUM(`p`.`price` * `oi`.`quantity`) `total_sales`
    FROM `users` `customer`
    JOIN `orders` `o` ON `customer`.`id` = `o`.`purchaser_id`
    JOIN `order_items` `oi` ON `o`.`id` = `oi`.`order_id`
    JOIN `products` `p` ON `oi`.`product_id` = `p`.`id`
    GROUP BY `customer`.`referred_by`
    ORDER BY `total_sales` DESC
) t
WHERE `rank` <= 200;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search