Running Aggregation Functions on Previous Queries

Hello,

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?

Thank you!!

You can create a temporary table by using a CTE, or WITH:

https://www.geeksforgeeks.org/cte-in-sql/

And:
https://www.essentialsql.com/introduction-common-table-expressions-ctes/

If I’m not mistaken, this module does cover temporary tables in pt. 9…

1 Like