Write a SQL query to find house with Avg(score) > 70.

Aggregate by house, then filter on the aggregate with HAVING.

SELECT house_id,
       AVG(score) AS avg_score
FROM   scores
GROUP  BY house_id
HAVING AVG(score) > 70;

WHERE versus HAVING

This question exists to check that you know the difference. WHERE filters individual rows before grouping; HAVING filters groups after aggregation. You cannot write WHERE AVG(score) > 70, because at the time WHERE runs no groups exist yet.

The two are complementary, and combining them is usually what production code wants:

SELECT house_id, AVG(score) AS avg_score
FROM   scores
WHERE  score IS NOT NULL
  AND  recorded_at >= DATE '2026-01-01'
GROUP  BY house_id
HAVING COUNT(*) >= 5
   AND AVG(score) > 70;

Filtering in WHERE first is also faster, because fewer rows reach the aggregation step.

Two traps

AVG ignores NULLs rather than treating them as zero. A house with scores of 80, 90 and NULL averages 85, not 56.7. If a missing score genuinely means zero, you must say so explicitly with AVG(COALESCE(score, 0)).

Small samples produce noise. A house with one lucky score of 71 clears the bar just as a house with fifty scores averaging 71 does. The HAVING COUNT(*) >= 5 guard above is what separates a textbook answer from a production one.

Returning the house name

When the name lives on another table, join and group by both columns:

SELECT h.house_id, h.name, AVG(s.score) AS avg_score
FROM   scores s
JOIN   houses h ON h.house_id = s.house_id
GROUP  BY h.house_id, h.name
HAVING AVG(s.score) > 70;

Any non-aggregated column in the SELECT must appear in GROUP BY. PostgreSQL relaxes this when you group by the primary key, but writing both is portable.

In the interview, also mention indexing house_id so the grouping can use a hash or sorted aggregate instead of scanning and sorting the whole table.