Hosts Rated Above Their City Average

SQL practice question · Medium · Subqueries & CTEs · Airbnb

Ignore NULL ratings. For each host and city, `host_avg` is the average rating of all reviews of that host's listings in the city; `city_avg` is the average rating of all reviews of listings in that city. Return hosts whose host_avg is strictly greater than city_avg. Return `city`, `host_id`, `host_avg` (ROUND 2), `city_avg` (ROUND 2). Order by `city`, `host_id`.

Table: listings

Schema of listings
Column	Type
listing_id	INTEGER
host_id	INTEGER
city	TEXT

Schema of listings

Column

Type

listing_id

INTEGER

host_id

INTEGER

city

TEXT

Sample rows of listings
listing_id	host_id	city
1	10	Goa
2	10	Goa
3	11	Goa
4	12	Manali
5	13	Manali

Sample rows of listings

listing_id

host_id

city

Goa

Manali

Table: listing_reviews

Schema of listing_reviews
Column	Type
review_id	INTEGER
listing_id	INTEGER
rating	INTEGER

Schema of listing_reviews

Column

Type

review_id

INTEGER

listing_id

INTEGER

rating

INTEGER

Sample rows of listing_reviews
review_id	listing_id	rating
1	1	5
2	1	4
3	2	5
4	3	3
5	3	4
6	4	4
7	4	5
8	5	2

Sample rows of listing_reviews

review_id

listing_id

rating

Expected output on the sample data
city	host_id	host_avg	city_avg
Goa	10	4.67	4.2
Manali	12	4.5	3.67

Expected output on the sample data

city

host_id

host_avg

city_avg

Goa

4.67

4.2

Manali

4.5

3.67

Solve it in the live editor on this page — run on sample data, then submit against hidden tests.

Tech Town

Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Question Asked in Interview Blog

SQL Interview Practice

Company-wise SQL questions

{{ totalCount }} questions in the style asked at Amazon, Microsoft, Google, Meta, Flipkart and more — {{ hardPct }}% Hard. Write SQL in a real database engine in your browser, run it on sample data, then evaluate against hidden tests.

🏆 Weekly SQL Contest

Questions

Companies

Solved

Bookmark Question Companies Difficulty Category Status

{{ r.n }} - {{ r.title }}

No questions match these filters.

Showing {{ rangeLabel }} of {{ filteredCount }}

Company tags show where this type of question is commonly reported in interviews. Questions, data and solutions are written by TechTown.

Daily challenge

{{ daily.title }} · {{ daily.difficulty }} Solve now

New question every day at midnight IST.

Your Progress

Reset all SQL practice progress{{ resetScope }}? This can't be undone.

{{ p.label }} ({{ p.pct }}%){{ p.solved }}/{{ p.total }}

Points: {{ myPoints }} · Easy 10 · Medium 20 · Hard 40

🔥 Longest streak: {{ streak.longest }}Current streak: {{ streak.current }}

Leaderboard (rolling 100 days)

Loading…

{{ u.rank }} {{ u.name }}{{ u.points }} pts

← All questions

{{ cur.n }} - {{ cur.title }}

🏆 Contest {{ contestWeek }}

Embed this questionPaste on a blog, course page or LMS. Free to use — educator guide

{{ embedCode }}

Table: {{ tb.name }}

ColumnType

Expected output on the sample data:

No submissions yet. Press Evaluate to submit your query.

The solution unlocks after you solve this question or after 3 evaluations that didn't pass ({{ failCount }}/3 so far). Trying first is the fastest way to learn.

{{ solutionSql }}

SQL dialect ⏱ {{ timerLabel }}

{{ fnHelpTitle }}
{{ fnHelpText }}

Writing {{ dialectName }} · Ctrl/Cmd + Enter runs the query · Tab indents · your code is saved automatically

Output

{{ out.error }}

Expected (sample data)

Your output (sample data)

Run your query to see the result here.

🔒

To Track Your Progress, Log In / Sign Up

You have solved your 3 free questions — nice work! Log in (free) to unlock all 150 questions, keep your streak and appear on the leaderboard.