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:

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.

CodePudding user response：

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;

CodePudding user response：

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