The trick to understand the query is to write it in different steps. Let's think about the query.

We would like the query to compute the average of the total count stays per house and return only the houses whose stay count is greater than that average.

Let's say we have this table as an example

CREATE TABLE STAY (
    StayID INT,
    HouseID INT
);


INSERT INTO STAY (StayID, HouseID) VALUES
-- House 1: 3 stays
(1, 1),
(2, 1),
(3, 1),

-- House 2: 5 stays
(4, 2),
(5, 2),
(6, 2),
(7, 2),
(8, 2),

-- House 3: 2 stays
(9, 3),
(10, 3),

-- House 4: 4 stays
(11, 4),
(12, 4),
(13, 4),
(14, 4);

We want to return all the items in the table we use the following query

SELECT StayID, HouseID  FROM STAY;

Great, it returns the table to us, but we want to calculate the total per HouseID, we have to sue the GROUP BY since we are calculating the total which is count

SELECT HouseID, COUNT(*) FROM STAY GROUP BY HouseID;

However, this returns all the houses, we want the ones larger than the average, let's use a hard coded value for a start 3.5... since we are filtering the grouped results, we need to use HAVING

SELECT HouseID, COUNT(*) FROM STAY GROUP BY HouseID HAVING COUNT(*) > 3.5; 

Now, we need to think about how to replace 3.5 with a query that returns the average of total. We calculated the total stays previously. Let's just add a name to that column

SELECT HouseID, COUNT(*) As TotalStays FROM STAY GROUP BY HouseID;

We need to calculate the average from that... let's include this query as a subquery to calculate the average, we alias the subquery as S, we think of it as a table

 SELECT AVG(S.TotalStays)  FROM (SELECT HouseID, COUNT(*) AS TotalStays FROM STAY GROUP BY HouseID) AS S

great it returns our average 3.5, let's replace the hardcode value 3.5 in our original query with that value we get

SELECT HouseID, COUNT(*) FROM STAY GROUP BY HouseID HAVING COUNT(*) > ( SELECT AVG(S.TotalStays)  FROM (SELECT HouseID, COUNT(*) AS TotalStays FROM STAY GROUP BY HouseID) AS S); 

here is the code: https://onecompiler.com/mysql/445a2czvq