0

I have a table with 3 columns, account_name, customer_name, amount, where the 'amount' is the amount of money a customer has paid to an account.

I want to determine what percentage of the customer's contribution is to each account. That is, for each account's total amount what percentage did the customer contribute to.

Currently using a query like this:

with customers as (
    select account_name, 
           customer_name, 
           sum(amount) as cst_contrib
    from accounts_table
    group by account_name, customer_name
), 

accounts as (
    select account_name, 
           sum(amount) as total_account_amount
    from accounts_table
    group by account_name
)

select a.account_name, 
       c.customer_name, 
       (c.cst_contrib/a.total_account_amount) as percent_contributed
from customers c
join accounts a
on c.account_name = a.account_name;

I suspect there is a much more efficient way to do this with window functions, but I have just begun using them and I'm not sure how to apply them here.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
JYosen
  • 89
  • 7
  • Have you looked at [this StackOverflow question] (https://stackoverflow.com/questions/8515152/how-to-use-a-sql-window-function-to-calculate-a-percentage-of-an-aggregate) – Robert Hamilton Oct 27 '22 at 14:44

0 Answers0