Find Hosts Meeting Listing, Rating, and Review-Coverage Rules

Problem Statement

Airbnb operations wants to identify high-performing, engaged "Superhosts" who maintain active inventory and consistently receive positive guest feedback.

Write a SQL query to find all hosts who meet all of the following criteria:

1. The host owns at least 2 active listings (is_active = TRUE).
2. The host's overall average rating across all reviews of their listings is at least 4.50.
3. 100% of their active listings have received at least one review in the past 180 days relative to '2024-01-01' (i.e., review_date between '2023-07-05' and '2024-01-01', inclusive).

Schema

listings

reviews

Output Format

Return the following columns, ordered by avg_rating descending, then host_id ascending:

• host_id (INT)
• active_listing_count (INT): Total number of active listings for the host.
• avg_rating (NUMERIC): Overall average rating across all reviews for the host's listings, rounded to 2 decimal places.

Example

listings

+------------+---------+-----------+
| listing_id | host_id | is_active |
+------------+---------+-----------+
| 101        | 1       | TRUE      |
| 102        | 1       | TRUE      |
| 103        | 2       | TRUE      |
| 104        | 2       | TRUE      |
| 105        | 3       | TRUE      |
+------------+---------+-----------+

reviews

+-----------+------------+--------+-------------+
| review_id | listing_id | rating | review_date |
+-----------+------------+--------+-------------+
| 1         | 101        | 4.8    | 2023-11-10  |
| 2         | 102        | 4.6    | 2023-12-01  |
| 3         | 103        | 4.9    | 2023-08-15  |
| 4         | 104        | 4.7    | 2022-01-10  |
| 5         | 105        | 5.0    | 2023-10-05  |
+-----------+------------+--------+-------------+

Output

+---------+----------------------+------------+
| host_id | active_listing_count | avg_rating |
+---------+----------------------+------------+
| 1       | 2                    | 4.70       |
+---------+----------------------+------------+

Explanation:

• Host 1 has 2 active listings, an average rating of (4.8 + 4.6) / 2 = 4.70 >= 4.50, and both listings have reviews after '2023-07-05' (100% coverage). Host 1 qualifies.
• Host 2 has 2 active listings and an average rating of 4.80 >= 4.50, but listing 104 has no review within the 180-day window (only 50% coverage). Host 2 is excluded.
• Host 3 only has 1 active listing (< 2). Host 3 is excluded.

Edge Cases to Consider

• Active listings with zero reviews ever should not satisfy the recent-review condition.
• A listing may have multiple reviews in the recent window; ensure distinct listing counts are used so duplicates do not inflate the coverage ratio.
• Inactive listings belonging to the host should not count towards active listing totals or coverage requirements.