Hiya fellow codecademers,
I’d like to discuss a solution to task no. 4 of the ‘Aggregate Functions Projects and Applications’
link to project
I wanted to come up with a one-step solution that calculates the requested percentage, and this is the shortest code that worked for me:
with top_users as ( select user, sum(score) as ssc from hacker_news group by user having sum(score)>200 ), top_users_score as ( select sum(ssc) from top_users ), total_score as ( select sum(score) from hacker_news ), score_table(c1, c2) as( select * from top_users_score join total_score ) select 100*sum(c1)/sum(c2) as 'Percentage' from score_table;
Q1 - first off, I suspect there could be a shorter and perhaps more elegant solution, without needing to use the last alias of
score_table or even that of
total_score, but I couldn’t make it work. Any ideas are welcome.
Q2 - without the
100* multiplier, the division appears to return
0, and I realised that has to do with the data type, as
sum(c2) are integers, while
sum(c1)/sum(c2) is a decimal <1, so the decimal part gets truncated. If for some reason I wanted to return
sum(c1)/sum(c2) and see the digits after the decimal point, where and how would I influence the data type?