Find Hosts Meeting Listing, Rating, and Review-Coverage Rules
Company: Airbnb
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
Find hosts who meet a super-host qualification rule involving listing count, average rating, and recent review coverage.
### Input Tables
`listings`
| Column | Type | Meaning |
| --- | --- | --- |
| `listing_id` | INTEGER | Primary key |
| `host_id` | INTEGER | Non-null owner of the listing |
`reviews`
| Column | Type | Meaning |
| --- | --- | --- |
| `review_id` | INTEGER | Primary key |
| `listing_id` | INTEGER | Non-null reference to `listings.listing_id` |
| `rating` | NUMERIC(3,2) | Non-null rating from 1.00 through 5.00 |
| `review_date` | DATE | Non-null review date |
`qualification_policy` contains exactly one row:
| Column | Type | Meaning |
| --- | --- | --- |
| `as_of_date` | DATE | Non-null date on which qualification is evaluated |
| `minimum_average_rating` | NUMERIC(3,2) | Non-null cutoff from 1.00 through 5.00 |
### Qualification Rules
The numeric average-rating cutoff is supplied by the policy table. Use the following explicit practice interpretation of the other conditions:
1. A host has at least three listings in `listings`.
2. The arithmetic mean of all individual reviews for those listings dated on or before `as_of_date` is strictly greater than `minimum_average_rating`.
3. At least 90 percent of the host's listings each have at least one review in the inclusive 30-calendar-day window from `as_of_date - 29 days` through `as_of_date`.
A host with no reviews on or before the evaluation date does not qualify. Each listing has equal weight in the recent-review coverage fraction; each individual review has equal weight in the rating mean. These are exercise conventions, not a statement of any company's actual host policy.
### Output Contract
Write one read-only PostgreSQL query returning a single column, `host_id`, containing qualifying hosts exactly once in ascending order.
### Example
Let `as_of_date` be January 30 and `minimum_average_rating` be `4.50`.
- Host `10` has three listings. Each has one review dated January 10, and all three ratings are `5.00`.
- Host `20` has three listings. Two have one `5.00` review dated January 10; the third has no reviews.
- Host `30` has three listings. Each has one review dated January 10, and all three ratings are `4.50`.
Expected result:
| host_id |
| --- |
| 10 |
Host `20` fails recent-review coverage. Host `30` fails the strict average-rating comparison.
### Constraints and Clarifications
- `listings` is the current ownership snapshot; there are no deleted listings or ownership changes to reconstruct.
- Multiple recent reviews for one listing count only once toward coverage, but each contributes to the rating mean.
- Older reviews still affect the rating mean, while reviews after `as_of_date` affect neither condition.
- Compare the 90-percent requirement exactly; do not round a coverage percentage before applying it.
- Preserve listings with zero reviews when counting the host's total listings.
```hint Keep the denominators separate
The coverage condition counts listings, while the rating condition averages reviews. A direct join can multiply listing rows and silently change one of those denominators.
```
Overview: Find qualifying hosts with SQL using listing counts, a supplied rating cutoff, and exact recent-review coverage across their listings.
Read the full Airbnb Data Engineer interview experience this question came from
Find hosts who meet a super-host qualification rule involving listing count, average rating, and recent review coverage.
Tables:
- listings(listing_id INTEGER primary key, host_id INTEGER non-null owner of the listing). This is the current ownership snapshot; there are no deleted listings or ownership changes to reconstruct.
- reviews(review_id INTEGER primary key, listing_id INTEGER non-null reference to listings.listing_id, rating NUMERIC(3,2) non-null rating from 1.00 through 5.00, review_date DATE non-null review date).
- qualification_policy(as_of_date DATE non-null date on which qualification is evaluated, minimum_average_rating NUMERIC(3,2) non-null cutoff from 1.00 through 5.00). This table contains exactly one row.
A host qualifies when all of the following hold:
1. The host has at least three listings in listings.
2. The arithmetic mean of all individual reviews for the host's listings dated on or before as_of_date is strictly greater than minimum_average_rating.
3. At least 90 percent of the host's listings each have at least one review in the inclusive 30-calendar-day window from as_of_date - 29 days through as_of_date.
A host with no reviews on or before the evaluation date does not qualify. Each listing has equal weight in the recent-review coverage fraction; each individual review has equal weight in the rating mean. Multiple recent reviews for one listing count only once toward coverage, but each contributes to the rating mean. Older reviews still affect the rating mean, while reviews after as_of_date affect neither condition. Compare the 90-percent requirement exactly; do not round a coverage percentage before applying it. Preserve listings with zero reviews when counting the host's total listings. These are exercise conventions, not a statement of any company's actual host policy.
Write one read-only PostgreSQL query returning a single column, host_id, containing qualifying hosts exactly once in ascending order.
Tables
listings(listing_id INTEGER, host_id INTEGER)
reviews(review_id INTEGER, listing_id INTEGER, rating NUMERIC(3,2), review_date DATE)
qualification_policy(as_of_date DATE, minimum_average_rating NUMERIC(3,2))
Hints
- Keep the denominators separate: the coverage condition counts listings, while the rating condition averages reviews. A direct join can multiply listing rows and silently change one of those denominators.