My question is related to an exercise in the multiple tables lesson in SQL but it’s a generic question. How can we run a function / query on the aggregated output of another query? For example, in this question
we are writing this code:
SELECT month, COUNT(*) FROM newspaper CROSS JOIN months WHERE start_month <= month AND end_month >= month GROUP BY month;
To perform a cross join, and the output is a table showing the count of subscriptions per month. What if we want to obtain the month with the highest [ MAX () ] count for example, how would we approach that? Is there a way to temporarily “store” the output of the first query somewhere and run the second MAX query based on it? or do we embed it somehow in the initial one?
At the risk of being repetitive, another example is the Hacker News Project we are writing this code block at Step 3:
SELECT user, SUM(score) FROM hacker_news GROUP BY user HAVING SUM(score) > 200 ORDER BY 2 DESC;
To create a table of the users with the highest score, in the following step we are expected to see their total score as a percentage of the aggregate score of all users in the database, in the example this is done by “manually” reading the numbers and running this query:
SELECT (1.0 + 2.0 + 3.0) / 6.0;
But is there a more efficient way?