How do I query to compare the top customer in a 2 quarter period?

Question

1.00/5 (1 vote)

See more:

I have a data set (AdventureWorks db) with fields like 'totalDue, CustomerID, OrderDate, OrderNumber'. I have to show the top customer and total amount they spent over any 2 quarter period from the 3 years (07/01/2005 to 07/31/2008) available in the data. We were told that it can be a range of any period of 9 consecutive months, but for like every year. And perhaps the best way to do it is with "Over BY Partition function".

I have tried numerous ways, but I am not getting the right answer.

What I have tried so far isn't giving me the right answer. The answer is supposed to be CustomerID: 29641. And we are also supposed to show the total amount spent by that Customer, but we weren't given that part of the answer.

Can anyone give me an insight to this problem please?

What I have tried:

select top 1 CustomerID,
Sum(TotalDue) Over (Partition By dateadd(qq, 3, OrderDate)) as TotalSpent
From Sales.SalesOrderHeader

Posted 21-Jan-17 14:34pm

Truecolors

Updated 31-Jan-17 7:03am

v2

Add a Solution

Comments

Afzaal Ahmad Zeeshan 22-Jan-17 4:02am

How do we know that the customer ID must be that one, we have no idea how your tables are structured.

Besides, to also get the total amount spent, just add that as column query (such as SUM(amount_spent). But remember, for aggregate functions you also need to use grouping in SQL.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-01-31T07:03:00

It might have been best if you had just typed in what your homework assignment said because you didn't explain it very well.

However, first problem - you are using Partition By for the quarter, but you also need to partition by the CustomerID

SQL

Sum(TotalDue) Over (Partition By CustomerID, dateadd(qq, 3, OrderDate)) as TotalSpent

Also, you have used top 1 but you have not ordered the results so the single row returned is quite random. You need to include an order by clause e.g.

SQL

order by Sum(TotalDue) Over (Partition By CustomerID, dateadd(qq, 3, OrderDate)) desc

OR

SQL

order by 2 desc

will also work - read this as "order by the 2nd item in the SELECT list, highest value first"

I'm not sure if the overall total for that customer is supposed to be in the same query or not - if it is then include another Sum using Partition By but this time you don't need to partition by the quarter.

That should be enough to get you moving on this again