Drill 1 of 2 in Marketplace questions
0

Interview Question Bank · Date window · HAVING · COUNT(column)

1. Recent rating with a minimum review count

MediumMarketplaceQuestion #126

Concept: Date window · HAVING · COUNT(column)show

A window like 'the 30 days ending June 30' has two edges, and both are easy to get wrong by a day. Filter the rows to the window in WHERE, then apply a minimum-count rule to each group with HAVING. COUNT(*) counts every row, but COUNT(rating) counts only rows with a rating, which are the same rows AVG(rating) averages.

Example (different data):

SELECT restaurant_id,
  COUNT(score) AS scored_visits,
  ROUND(AVG(score), 1) AS avg_score
FROM inspections
WHERE inspected_on >= '2025-01-01' AND inspected_on < '2025-04-01'
GROUP BY restaurant_id
HAVING COUNT(score) >= 5;

Your task

Burrowstay, a short-stay rentals marketplace, shows a recent-rating badge only on listings with enough recent feedback, so one 5-star stay can't carry a listing.

Using reviews from the 30 days ending 2025-06-30 (2025-06-01 through 2025-06-30, inclusive), return listing_id, title, rated_reviews (reviews in that window with a rating) and avg_rating (ROUND(AVG(rating), 2)) for listings with at least 3 rated reviews in the window. Reviews with a NULL rating are comments only and don't count. Sort by avg_rating highest first, then listing_id.

Starting Postgres in your browser…

Checking your access…