5.5 Subquery Case Study: The Top N Performers

Certain queries that are easily described in English have traditionally been difficult to formulate in SQL. One common example is the "Find the top five salespeople" query. The complexity stems from the fact that data from a table must first be aggregated, and then the aggregated values must be sorted and compared to one another to identify the top or bottom performers. In this section, you will see how subqueries may be used to answer such questions. At the end of the section, we introduce ranking functions, a feature of Oracle SQL that was specifically designed for these types of queries.

5.5.1 A Look at the Data

Consider the problem of finding the top five salespeople. Let's assume that we are basing our evaluation on the amount of revenue each salesperson brought in during the previous year. The first task, then, would be to sum the dollar amount of all orders booked by each salesperson during the year in question. To do so, we will dip into our data warehouse, in which orders have been aggregated by salesperson, year, month, customer, and region. The following query generates total sales per salesperson for the year 2001:

SELECT s.name employee, SUM(o.tot_sales) total_sales

FROM orders o INNER JOIN salesperson s

ON o.salesperson_id = s.salesperson_id

WHERE o.year = 2001

GROUP BY s.name

ORDER BY 2 DESC;



EMPLOYEE                       TOTAL_SALES

------------------------------ -----------

Jeff Blake                         1927580

Sam Houseman                       1814327

Mark Russell                       1784596

John Boorman                       1768813

Carl Isaacs                        1761814

Tim McGowan                        1761814

Chris Anderson                     1757883

Bill Evans                         1737093

Jim Fletcher                       1735575

Mary Dunn                          1723305

Dave Jacobs                        1710831

Chuck Thomas                       1695124

Greg Powers                        1688252

Don Walters                        1672522

Alex Fox                           1645204

Barbara King                       1625456

Lynn Nichols                       1542152

Karen Young                        1516776

Bob Grossman                       1501039

Eric Iverson                       1468316

Tom Freeman                        1461898

Andy Levitz                        1458053

Laura Peters                       1443837

Susan Jones                        1392648

It appears that Isaacs and McGowan have tied for fifth place, which, as you will see, adds an interesting wrinkle to the problem.

5.5.2 Your Assignment

It seems that the boss was so tickled with this year's sales that she has asked you, the IT manager, to see that each of the top five salespeople receive a bonus equal to 1% of their yearly sales. No problem, you say. You quickly throw together the following report using your favorite feature, the inline view, and send it off to the boss:

SELECT s.name employee, top5_emp_orders.tot_sales total_sales,

  ROUND(top5_emp_orders.tot_sales * 0.01) bonus

FROM 

 (SELECT all_emp_orders.salesperson_id emp_id, 

    all_emp_orders.tot_sales tot_sales

  FROM

   (SELECT salesperson_id, SUM(tot_sales) tot_sales

    FROM orders

    WHERE year = 2001

    GROUP BY salesperson_id

    ORDER BY 2 DESC

   ) all_emp_orders

  WHERE ROWNUM <= 5

 ) top5_emp_orders INNER JOIN salesperson s

ON top5_emp_orders.emp_id = s.salesperson_id

ORDER BY 2 DESC;



EMPLOYEE                       TOTAL_SALES      BONUS

------------------------------ ----------- ----------

Jeff Blake                         1927580      19276

Sam Houseman                       1814327      18143

Mark Russell                       1784596      17846

John Boorman                       1768813      17688

Tim McGowan                        1761814      17618

The howl emitted by Isaacs can be heard for five square blocks. The boss, looking a bit harried, asks you to take another stab at it. Upon reviewing your query, the problem becomes immediately evident; the inline view aggregates the sales data and sorts the results, and the containing query grabs the first five sorted rows and discards the rest. Although it could easily have been McGowan, since there is no second sort column, Isaacs was arbitrarily omitted from the result set.

5.5.3 Second Attempt

You console yourself with the fact that you gave the boss exactly what she asked for: the top five salespeople. However, you realize that part of your job as IT manager is to give people what they need, not necessarily what they ask for, so you rephrase the boss's request as follows: give a bonus to all salespeople whose total sales ranked in the top five last year. This will require two steps: find the fifth highest sales total last year, and then find all salespeople whose total sales meet or exceed that figure. You write a new query as follows:

SELECT s.name employee, top5_emp_orders.tot_sales total_sales,

  ROUND(top5_emp_orders.tot_sales * 0.01) bonus

FROM salesperson s INNER JOIN

 (SELECT salesperson_id, SUM(tot_sales) tot_sales

  FROM orders

  WHERE year = 2001

  GROUP BY salesperson_id

  HAVING SUM(tot_sales) IN

   (SELECT all_emp_orders.tot_sales

    FROM 

     (SELECT SUM(tot_sales) tot_sales

      FROM orders

      WHERE year = 2001

      GROUP BY salesperson_id

      ORDER BY 1 DESC

     ) all_emp_orders

    WHERE ROWNUM <= 5)

 ) top5_emp_orders

ON top5_emp_orders.salesperson_id = s.salesperson_id

ORDER BY 2 DESC;



EMPLOYEE                       TOTAL_SALES      BONUS

------------------------------ ----------- ----------

Jeff Blake                         1927580      19276

Sam Houseman                       1814327      18143

Mark Russell                       1784596      17846

John Boorman                       1768813      17688

Tim McGowan                        1761814      17618

Carl Isaacs                        1761814      17618

Thus, there are actually six top five salespeople. The main difference between your first attempt and the second is the addition of the HAVING clause in the inline view. The subquery in the HAVING clause returns the five highest sales totals, and the inline view then returns all salespeople (potentially more than five) whose total sales exist in the set returned by the subquery.

Although you are confident in your latest results, there are several aspects of the query that bother you:

  • The aggregation of sales data is performed twice.

  • The query will never contend for Most Elegant Query of the Year.

  • You could've sworn you read about some sort of feature just for handling these types of queries . . .

In fact, there is a feature, an analytic SQL feature, for performing ranking queries that became available with Oracle8i. That feature is the RANK function.

5.5.4 Final Answer

The RANK function is specifically designed to help you write queries to answer questions like the one posed in this case study. Part of a set of analytic functions (all of which will be explored in Chapter 14), the RANK function may be used to assign a ranking to each element of a set. The RANK function understands that there may be ties in the set of values being ranked and leaves gaps in the ranking to compensate. The following query illustrates how rankings would be assigned to the entire set of salespeople; notice how the RANK function leaves a gap between the fifth and seventh rankings to compensate for the fact that two rows share the fifth spot in the ranking:

SELECT salesperson_id, SUM(tot_sales) tot_sales,

  RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank

FROM orders

WHERE year = 2001

GROUP BY salesperson_id;



SALESPERSON_ID  TOT_SALES SALES_RANK

-------------- ---------- ----------

             1    1927580          1

            14    1814327          2

            24    1784596          3

             8    1768813          4

            15    1761814          5

            16    1761814          5

            20    1757883          7

            11    1737093          8

             9    1735575          9

            10    1723305         10

            17    1710831         11

             4    1695124         12

             5    1688252         13

            12    1672522         14

            19    1645204         15

            18    1625456         16

            21    1542152         17

            13    1516776         18

             3    1501039         19

            22    1468316         20

             2    1461898         21

             7    1458053         22

            23    1443837         23

             6    1392648         24

Leaving gaps in the rankings whenever ties are encountered is critical for properly handling these types of queries. (If you do not wish to have gaps in the ranking, you can use the DENSE_RANK function intead.) Table 5-1 shows the number of rows that would be returned for this data set for various top-N queries.

Table 5-1. Rows returned for N = {1,2,3,...,9}

Top-N salespeople

Rows returned

1

1

2

2

3

3

4

4

5

6

6

6

7

7

8

8

9

9


As you can see, the result sets would be identical for both the "top five" and "top six" versions of this query for this particular data set.

By wrapping the previous RANK query in an inline view, you can retrieve the salespeople with a ranking of five or less and join the results to the salesperson table to generate the final result set:

SELECT s.name employee, top5_emp_orders.tot_sales total_sales,

  ROUND(top5_emp_orders.tot_sales * 0.01) bonus

FROM

 (SELECT all_emp_orders.salesperson_id emp_id, 

    all_emp_orders.tot_sales tot_sales

  FROM

   (SELECT salesperson_id, SUM(tot_sales) tot_sales,

      RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank

    FROM orders

    WHERE year = 2001

    GROUP BY salesperson_id

   ) all_emp_orders

  WHERE all_emp_orders.sales_rank <= 5

 ) top5_emp_orders INNER JOIN salesperson s

ON top5_emp_orders.emp_id = s.salesperson_id

ORDER BY 2 DESC;



EMPLOYEE                       TOTAL_SALES      BONUS

------------------------------ ----------- ----------

Jeff Blake                         1927580      19276

Sam Houseman                       1814327      18143

Mark Russell                       1784596      17846

John Boorman                       1768813      17688

Tim McGowan                        1761814      17618

Carl Isaacs                        1761814      17618

If this query looks familiar, that's because it's almost identical to the first attempt, except that the RANK function is used instead of the pseudocolumn ROWNUM to determine where to draw the line between the top five salespeople and the rest of the pack.

Now that you are happy with your query and confident in your results, you show your findings to your boss. "Nice work," she says. "Why don't you give yourself a bonus as well? In fact, you can have Isaacs's bonus, since he quit this morning." Salespeople can be so touchy.