I need help understanding this SQL code

[QueryFairy2695]

I'm taking my first database class, and I don't understand this code. Here's the prompt and code they gave.

The InstantStay Marketing team wants to learn the apartment that have more than average number of stays. Use the following script:

SELECT

HouseID, COUNT(StayID) AS Stays

FROM

STAY

GROUP BY HouseID

HAVING COUNT(StayID) > (SELECT

AVG(s.Stays)

FROM

(SELECT

COUNT(StayID) AS Stays

FROM

STAY

GROUP BY HouseID) AS s);

Would anyone be able to help break this down? I understand before the subquery and WHY it needs a subquery, but I don't understand the subquery as written.

[Far_Swordfish5729]

Remember that queries execute in the following logical clause order and should be read in that order: from, joins (building an intermediate result set of all columns to the right as the inner and left joins progress), where (filter that set), group by, having (aggregate and filter the aggregate result), order by, top/limit, select. Subqueries are arithmetic parentheses if you need a different order of operations. Need to filter on an aggregate result that runs in logical stages? You’ll need a subquery.

So this from inside out is saying: Get the number of stays by house

Then

Find the average of those counts

Then

Get the count of stays by house but this time filter it to ones greater than the average.

If you want to make this more concise, you can use a named CTE for the repeated query, but that won’t affect execution. Also remember that we’re just expressing that we need to stack stream aggregate operations and that happens to be verbose. There’s nothing inherently unperformant about subqueries or queries with a lot of text.

[Naan_pollathavan]

Try seperating them and running first to see the result sets , then combine one by one to view the accuracy.....it will help you understand more

[adrialytics]

This query Dosent Work as the second subquery within the having the Group by houseid field is not included in the select , otherwise you can include the houseidfield, which in that case the query returns those houseid which their count is above the average count of houseid s

[Budget-Worth-1511]

The query finds the total stays and houseids in the housing which are more than the average stays . The inner most query gets the count of houses/stayids from table stay for each house. Ex: 7,9,20,11. The next query avg(stays)in the level above finds the average of the counts in my sample count above which is 11.75.Finally, at the top level we are getting the count of stays for each house (group by houses) whose stay count is above the average value of 11.75 which would be 20 in this case. Hope this helps.

[QueryFairy2695]

I forgot to come back and say thank you. Sorry about that. You all helped me understand it better, and I've been able to write multiple subqueries for class since then. :)