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

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.

Super-Host Qualification

Table listings

column	type	constraints
listing_id	INTEGER	PRIMARY KEY
host_id	INTEGER	NOT NULL

Sample data:

host_id	listing_id
101	1
101	2
101	3
102	4
102	5
103	6
103	7
103	8
104	9
104	10
104	11
104	12
105	13
105	14
105	15
105	16
105	17
105	18
105	19
105	20
105	21
105	22
106	23
106	24
106	25
106	26
106	27
106	28
106	29
106	30
106	31
106	32
107	33
107	34
107	35
108	36
108	37
108	38
109	39
109	40
109	41
110	42
110	43
110	44

Table reviews

column	type	constraints
review_id	INTEGER	PRIMARY KEY
listing_id	INTEGER	NOT NULL
rating	NUMERIC(3,2)	NOT NULL
review_date	DATE	NOT NULL

Sample data:

listing_id	rating	review_date	review_id
1	4.8	2026-01-20	1
1	4.6	2026-01-25	2
2	5	2026-01-18	3
3	4.9	2026-01-22	4
4	5	2026-01-12	5
4	5	2026-01-26	6
5	5	2026-01-14	7
5	5	2026-01-27	8
6	4.5	2026-01-10	9
7	4.5	2026-01-10	10
8	4.5	2026-01-10	11
9	5	2026-01-15	12
10	5	2026-01-16	13
11	5	2026-01-17	14
13	4.9	2026-01-01	15
14	4.9	2026-01-05	16
15	4.9	2026-01-08	17
16	4.9	2026-01-11	18
17	4.9	2026-01-14	19
18	4.9	2026-01-17	20
19	4.9	2026-01-20	21
20	4.9	2026-01-24	22
21	4.9	2026-01-28	23
23	5	2026-01-06	24
24	5	2026-01-09	25
25	5	2026-01-12	26
26	5	2026-01-15	27
27	5	2026-01-18	28
28	5	2026-01-21	29
29	5	2026-01-24	30
30	5	2026-01-27	31
31	5	2025-12-31	32
32	5	2026-02-01	33
33	5	2026-01-11	34
34	5	2026-01-12	35
35	5	2026-01-13	36
33	1	2025-06-01	37
36	4.6	2026-01-19	38
37	4.6	2026-01-20	39
38	4.6	2026-01-21	40
36	1	2026-02-10	41
39	5	2026-02-03	42
40	5	2026-02-04	43
41	5	2026-02-05	44
42	4	2026-01-08	45
42	4	2026-01-15	46
42	4	2026-01-24	47
43	5	2026-01-19	48
44	5	2026-01-29	49

Table qualification_policy

column	type	constraints
as_of_date	DATE	NOT NULL
minimum_average_rating	NUMERIC(3,2)	NOT NULL

Sample data:

as_of_date	minimum_average_rating
2026-01-30	4.5

Expected output

host_id
101
105
108

Hints

1. 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.