SQL - Customer Satisfaction Impact (Subquery needed?)

Question

I've been having some SQL troubles trying to add a new column to my table which I believe relies on sub-queries to work.

Below I've shared a link to a Google Sheet with some example data to demonstrate what I'm trying to achieve. https://docs.google.com/spreadsheets/d/1qaWZg4BEs8xOxG3jNZwKRcaOGfMiMtCb15IU3FQ9W0o/edit?usp=sharing

Very basic layout I have is:

select
 team_member,
 language,
 channel,
 month,
 sum(total_customers),
 sum(happy_customers),
 safe_divide(sum(happy_customers),sum(total_customers)) AS Satisfaction_Score
FROM
 Table1
GROUP BY 1,2,3,4

I understand the above will get me the satisfaction score for each row of team_member,language,channel,month. I'm trying to add new columns to the end of this which could get me:

• sum(total_customers) rolled up by language
• Satisfaction_Score for the supported language by team_member

Which I could then use to calculate satisfaction impact, which is laid out like:

(team member satisfaction score-language satisfaction score)*(team member total customers/language total customers)

I sadly haven't even got close to the answer, hoping a wizard here might know how it could be done?

Answer 1

You can use window functions:

select team_member, language, channel, month,
       sum(total_customers), sum(happy_customers),
       sum(sum(total_customers)) over (partition by team_member, language) as total_team_language,
       safe_divide(sum(sum(happy_customers)) over (partition by team_member, language),
                   sum(total_customers)over (partition by team_member, language)
                  ) AS Satisfaction_Score_team_language
FROM Table1
GROUP BY 1,2,3,4