SQL Query: Find highest revenue month/year for a customer

Question

I'm looking to query the database to find highest revenue month for all the customers in the system. I have got the query working to pull customers monthly revenue from all the years for which the data is present. But I'm struggling to figure out how to get highest revenue month-year from this data.

The database is SQL Server 2008 R2. The columns are: Customer name, Year, Month, and Revenue. I even tried using Row_Number() and tried partitioning by customer name/year and ordering by revenue. But it didn't work. Maybe I'm making some mistake there.

Here's how I tried to build the base query.

Select Customer, Year(orderdatetime) as Year, Month(orderdatetime) as Month, SUM(Revenue) From Orders Group By Customer, Year(orderdatetime), Month(orderdatetime)

This is how I tried to use Row_Number()

WITH Max_Revenue AS ( Select Customer, Year(orderdatetime) as Year, Month(orderdatetime) as Month, SUM(Revenue), RowNumber = ROW_NUMBER() OVER(PARTITION By Year Order By Revenue DESC) From Orders Group By Customer, Year(orderdatetime), Month(orderdatetime) ) Select Max_Revenue.Customer, Max_Revenue.Year, Max_Revenue.Month, Max_Revenue.Revenue From Max_Revenue Where Max_Revenue.RowNumber = 1 Order By Max_Revenue.Customer asc

The data I get back is like:

Customer Month Year Revenue ABC 2 2012 100 ABC 3 2013 150 ABC 5 2012 200 XYZ 4 2011 500 XYZ 6 2012 650 XYZ 7 2012 800

What I want as the output is

 Customer Month Year Revenue ABC 5 2012 200 XYZ 7 2012 800

So every customer's best month and respective year in terms of revenue.

try using "max" something like select max(revenue) from table — zxc
– zxc, Commented Oct 11, 2013 at 1:02

jcwrequests · Accepted Answer · 2013-10-11 00:53:45Z

 SELECT Customer, Year, Revenue, Month FROM ( SELECT Customer, Year, ROW_NUMBER() OVER(PARTITION By Customer Order By Revenue DESC) as rank, Revenue, Month FROM ( Select Customer, Year(orderdatetime) as Year, Month(orderdatetime) as Month, SUM(Revenue) as Revenue From Orders Group By Customer, Year(orderdatetime), Month(orderdatetime) ) BS GROUP BY Customer, Year, Month) BS2 WHERE BS2.rank = 1

OR change = ROW_NUMBER() OVER(PARTITION By Year Order By Revenue DESC to = ROW_NUMBER() OVER(PARTITION By Customer Order By Revenue DESC

This works perfectly, I missed on adding the last level of nesting query there. Also the only change we need to make here is to add Revenue in the Group By of BS inner SQL otherwise we won't be able to use it in the partition of Row_Number(). That's the only extra thing I added and it's working perfectly now..tx

Collectives™ on Stack Overflow

SQL Query: Find highest revenue month/year for a customer

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related