Rank query

Databases
1

I am looking for a kind help how to do a rank query. I need to find out the rank of a sales agent who is doing multiple leads and generating revenues. Rank would be on based on the revenue from leads table. Query will sum the revenue amounts of all of his leads and then give me the the rank of that user.

2

use windowing functions, specifically RANK()

if your dbms, which you neglected to mention, does not support window functions, it’s gonna be a lot tougher

3

I am using mysql and it doesn’t support RANK() function. Can i have any help for it please.

4

sure

let’s start with the query that produces a single agent’s revenue

could you show that, please

5

Single agent revenue is generated:

SELECT SUM(marketing_commission+purchaser_commission) AS Revenue FROM operations_history WHERE user_id=22 AND date_range LIKE ‘date(“Y-m”)’

where user_id is the id of agent and date_range is for month.

6 
SELECT t.Revenue
     , ( SELECT COUNT(*)+1
           FROM ( SELECT user_id
                       , SUM(marketing_commission + 
                             purchaser_commission) AS Revenue
                    FROM content_type_results
                   WHERE date_range LIKE 'date("Y-m")'
                  GROUP
                      BY user_id  ) AS m
          WHERE m.Revenue > t.Revenue ) AS rank
  FROM ( SELECT SUM(marketing_commission + 
                    purchaser_commission) AS Revenue 
           FROM operations_history 
          WHERE user_id = 22 
            AND date_range LIKE 'date("Y-m")' ) AS t
7

Thanks much appreciated. Could you please tell me what is this table: content_type_results it was not in my query.

8

sorry, that was a copy/pasta error