listings has one row per listing: listing_id, host_id, price and number_of_reviews. Rate each HOST by the total reviews across all of their listings: New (0 reviews), Rising (1 to 5), Trending (6 to 15), Popular (16 to 40) and Hot (more than 40). Then, for each rating, find the minimum, average and maximum nightly price of the listings whose host has that rating. Return result with the columns popularity, min_price, avg_price and max_price, with the average rounded to 2 decimals. Leave out a rating no host has.
The data
listings16 rows
| listing_idint64 | host_idint64 | priceint64 | number_of_reviewsint64 |
|---|
| 501 | 101 | 85 | 0 |
| 502 | 102 | 120 | 2 |
| 503 | 102 | 95 | 3 |
| 504 | 103 | 150 | 6 |
| 505 | 104 | 200 | 10 |
| 506 | 104 | 180 | 5 |
and 10 more rows
Target output
| popularity | min_price | avg_price | max_price |
|---|
| New | 85 | 90 | 95 |
| Rising | 60 | 91.67 | 120 |
| Trending | 150 | 173.75 | 200 |
| Popular | 99 | 133 | 160 |
| Hot | 90 | 231.25 | 310 |
What this question tests
Tests computing a value at one level (the host) and applying it at another (the listing), then binning with clean boundaries and a named aggregation. The usual slip is rating each listing by its own reviews, which is a different question with a confident wrong answer.
More Python interview questions