CODEWITHSUNDEEP
sqlsql-serverrollup
flojo777
flojo777

Reputation: 1

SQL Find Top Customer with greatest total transaction amount, list transactions, and total all transactions in row at bottom

Sample data from transactions table..

example

transaction_id customer_id transaction_amount transaction_location
600 66 $504.57 California
601 47 $367.14 Virginia
602 36 $756.00 Michigan
603 63 $364.62 Texas

Here is the question:

List the top 1 customer's transactions who have the highest total transaction amount in such a way that the result shows all the transaction done by the customer and the last row shows the total amount of all the transaction done by the customer. For Example: The output should be in the following format:

transaction_id Transaction_Amount
1 50.06
5 298.45
20 400.73
31 75.48
50 1300.15
-------------------------- -------------------
Total_Transactions_Amount 2124.87

I am working through this. I am thinking perhaps a self join is needed?

/*SELECT t1.transaction_amount, t1.transaction_id
FROM Transactions t1
INNER JOIN Transactions t2
ON t1.transaction_id = t2.transaction_id
...? */

I know that I will need to use some sort of rank function or row number function??, or something like that. Really need help in that part.

All in all, I know my code needs to partition over the customer id, and will need some way to rank and output the top #1 customer.

I also know I need a ROLLUP function to tally the total on the row below, but I haven't come to that part of my solution yet. This is what I have so far.

I figured out that customer #50 is the #1 customer, so for now I was able to partition over the customer id and output all transactions and Total_Transactions_Amount.

SELECT customer_id, transaction_id, transaction_amount, 
 SUM(transaction_amount) 
    OVER (Partition BY customer_id) AS Total_Transactions_Amount 
 FROM Transactions
 WHERE customer_id = 50
 GROUP BY customer_id, transaction_id, transaction_amount
 ORDER BY transaction_amount DESC, customer_id, transaction_id

Upvotes: 0

Views: 3780

Answers (1)

Cetin Basoz
Cetin Basoz

Reputation: 23837

Your last query doesn't make much sense really.

First you need to find the customer with the highest total amount:

select customer_Id, sum(transaction_amount)
from Transactions
group by customer_id
order by sum(transaction_amount) desc;

We only need the top(1), thus:

select top(1) customer_Id, sum(transaction_amount)
from Transactions
group by customer_id
order by sum(transaction_amount) desc;

And also we really don't need the sum() here, just the customer_id, thus:

select top(1) customer_Id
from Transactions
group by customer_id
order by sum(transaction_amount) desc;

So far so good, if we knew the customer_id, we would write:

SELECT transaction_id, sum(transaction_amount) as transaction_amount 
 FROM Transactions
 WHERE customer_id = 50
 GROUP BY transaction_id
 ORDER BY transaction_id
 with rollup;

We actually found that customer_id in the former query so connecting them:

SELECT transaction_id,
       SUM(transaction_amount) AS transaction_amount
FROM Transactions
WHERE customer_id IN
      (
          SELECT TOP (1)
                 customer_Id
          FROM Transactions
          GROUP BY customer_id
          ORDER BY SUM(transaction_amount) DESC
      )
GROUP BY transaction_id
ORDER BY transaction_id
with rollup;

BTW, used IN () because there might be multiple customers with the same total transaction_amount and you might want to use with ties in that case.

Upvotes: 0

Related Questions