SQL Aggregate functions Project - 'hacker news'

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(c1) and 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?

1 Like