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?

4 Likes

Brilliant. I had the exact same desire to return a result in one command.
This is using some SQL code that I haven’t learned yet, but it totally makes sense. I just don’t follow the score_table(c1, c2) and join clause. It seems that c1 = top_users_score and c2 = total_score, but I don’t get how that was defined. I don’t know enough about JOIN clauses yet.

This took me a while to work out but I eventually got a single query that returns the same percentage.

SELECT CAST(SUM(user_score) AS REAL) / total_score
FROM 
  (SELECT SUM(score) as 'user_score'
     FROM hacker_news
     GROUP BY user
     HAVING SUM(score) > 200), 
  (SELECT SUM(score) AS 'total_score'
     FROM hacker_news);

What you can do is query results from multiple tables at once by listing them after FROM.
The results of a query are also stored in a table. You can therefore run the queries for parts 2 and 3 and then reference both of these tables in one further query to get the final result.

The CAST function is used to change the integer returned by the SUM function to a real so the divison can work.

Hope this helps.

WITH highest_pointers AS (

SELECT sum(score) as ‘userscore’

FROM hacker_news

group by user

having sum(score) > 200

ORDER BY 1 desc

),

Total_Score as

(

SELECT sum(score) as ‘TotalScore’

FROM hacker_news

)

SELECT round(cast(sum(userscore) as real)/(select * from Total_Score),2) as ‘%score’ from

highest_pointers;

Not sure how you came up with that… I feel I didn’t learn any of that from the material