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?
Advertisement
Answer
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