Vendors with Most Invoices in State

Question

I have the tables Vendors(VendorName, VendorID,...) and Invoices(InvoiceID,...) and I am trying to find vendors with the most invoices in their respective states. Please critique my work:

SELECT Max(NumInvoicesState)
FROM 
 (
   SELECT  VendorState,VendorName,
      Count(InvoiceID) AS NumInvoicesState 
   FROM VENDORS V 
   JOIN Invoices I
     on V.VendorID=I.VendorID
   GROUP by  VendorState, VendorName
 ) AS Alias

My idea is to first do a query to get a table with a list of vendors by state and the number of invoices for each vendor.

I thought of joining the inner-table to Vendors and then group by VendorState, but that does not seem to work.

I guess I could SELECT VendorName, VendorState then join it with Vendors on, say VendorState and then Group by Vendors. But then I get all the Vendors, not just the ones with the max.

Answer 1

I think the inner query is right.

The MAX without a matching GROUP BY in the outer query will give a single value from the whole dataset. In your example the dataset would be the inner query. This is not what you want, so you have to add group by state.

Now you have the problem of vendor name. It can't be in the select list unless it is also in the GROUP BY, but that will give the wrong result. It is necessary to do this in two steps. In the outer query just deal with states:

Select state, max (..) as MaxByState
From (inner query)
Group by state

Then join this back to the inner query on the MAX() value:

Select
  ...
from (
    Select state, max (..) as MaxByState
    From (inner query)
    Group by state
) as mbs
inner join (
  Inner query
) as iq
on mbs.state = iq.state
and mbs.MaxByState = iq.NumInvoicesState;

In other words, here are the most numerous invoices in each state. Which vendors have these many invoices?

If two vendors have the same number of invoices in a state there will be two rows output.

Since the inner query is repeated verbatim it would be better presented as a non-recursive CTE.

SQL Server 2012 has a number of windowing functions built in. The most useful one here is RANK (). It does just what you think it should do.

Select 
  RANK ( ) OVER (PARTITION BY state order by NumInvoicesState )
from (inner query) as a

This eliminates all of the second-order dataset alignment on derived aggregate values required above. It is much easier to read and understand, and hence to maintain.

Answer 2

WITH Counts AS (
    SELECT  VendorState, 
            VendorName,
            Count(InvoiceID) AS NumInvoicesState
    FROM    Vendors v
    JOIN    Invoice i 
    ON      v.VendorID = i.VendorID 
    GROUP BY VendorState, VendorName
    ), Ranks As (
    SELECT  VendorState,
            VendorName,
            NumInvoicesState,
            ROW_NUMBER() OVER (PARTITION BY VendorState ORDER BY NumInvoicesState DESC) As StateRank
    FROM    Counts
    )
SELECT  VendorState,
        VendorName,
        NumInvoicesState
FROM    Ranks
WHERE   StateRank = 1
ORDER BY 3, 1;

Note: If there is more than one vendor with the same number of invoices then it will arbitrary pick one. There may also be a more compact way to do this.

Regarding your request to critique your attempt... yours doesn't work because you're only returning one max number and that's it. You said you wanted to get the vendor with the most invoices split per state. This is going to require a few steps and WITH is a simple way of splitting up a complex query into sub queries that reference each other.

The first part of the WITH is exactly your query (only reformatted to be clearer). It gets the basic data... the states, the vendors, and how many invoices they had.

The second part of the statement references that first table and just adds row numbers. These start at 1 for each state and order downwards depending on that invoice count. This is seperate because we need that first query to get that data for us before we can start working on it.

The final part of the statement is just returning the top row of each state by getting wherever the row number is 1, so wherever it is the top row for each state.

There are likely ways to make this more compact or performant. But for teaching and readability purposes which include your core query this is how it's done.

Answer 3

As suggested by Cody Konior, you need to find rank of each vendor in a state partition by vendor state and then find all records in which ranks were 1. I will try to connect his solution to your attempt:

SELECT t2.VendorState [State], t2.VendorName VendorWithMostSales
FROM (
SELECT VendorState,
VendorName,
NumInvoicesState,
ROW_NUMBER() OVER (PARTITION BY VendorState ORDER BY NumInvoicesState DESC) As StateRank
FROM 
 (
   SELECT  VendorState,VendorName,
      Count(InvoiceID) AS NumInvoicesState 
   FROM VENDORS V 
   JOIN Invoices I
     on V.VendorID=I.VendorID
   GROUP by  VendorState, VendorName
 ) t1
) t2
WHERE t2.StateRank = 1;

If you want two names instead of one in case of a tie for the sales, use DENSE_RANK or RANK. For data with a tie between second and third standing ROW_NUMBER goes like 1,2,3,4,5... RANK goes like 1,2,2,4,5... and DENSE_RANK goes like 1,2,2,3,4...

Answer 4

A late review for an interview:

SELECT TOP 1 Count(InvoiceID) Most , VendorState
FROM Vendors V JOIN Invoices I
 ON V.VendorID=I.VendorID
 GROUP BY VendorState
 ORDER BY Most DESC

It ran with the right answer. Thanks all for your input.

A similar one: The state with the most vendors:

SELECT TOP 1 Count(VendorState) MostV , VendorState
FROM Vendors V JOIN Invoices I
ON V.VendorID=I.VendorID
GROUP BY VendorState
ORDER BY MostV DESC