Fifteen Chinese cities have more sights than beds — and that is a feature, not a gap

#data #database #sql #travel

We run a small travel dataset: hotels in China that state they accept foreign guests, attractions with English names and opening hours, both matched by city. Every city page on the site has a floor of eight verified hotels. Below eight, the city gets the nearest larger city instead of a page.

Eighteen cities sit exactly on that floor. Fifteen of them have at least six attractions.

That number is the whole post. Here is what it means and how we got it.

The query

Two aggregates, one join:

SELECT a.city_slug, a.n AS spots, h.n AS hotels
FROM (SELECT city_slug, COUNT(*) n FROM attractions
      WHERE COALESCE(status,'live')='live'
      GROUP BY city_slug HAVING COUNT(*) >= 6) a
JOIN (SELECT city_slug, COUNT(*) n FROM hotels
      WHERE COALESCE(status,'live')='live'
      GROUP BY city_slug HAVING COUNT(*) BETWEEN 1 AND 8) h
  ON h.city_slug = a.city_slug
ORDER BY a.n DESC;

Fifteen rows. Zhenjiang has nineteen attractions and eight hotels. Yichun, in Heilongjiang, has twenty and eight. Laibin has seventeen and eight.

Why the hotel count is not a row count

A hotel only counts if it states it takes foreign guests, has a verified street address in Chinese, and has a public review history. A city with thirty listings and five verified addresses counts as five. That filter is why the numbers are lower than any booking site will tell you, and it is also why the floor exists at all.

Why we did not lower the floor

This is the design decision worth arguing about. Below eight properties, a city page stops answering a question and starts being a list. Three options means you cannot choose on district, on price, or on whether the desk will accept your passport — you take what exists.

Publishing 359 pages instead of 355 would have made our coverage look better and the product worse. A count that includes everything is easy to produce and useless to act on.

What we do with the gap instead

The honest output is "come for the day, sleep somewhere else". Zhenjiang is 40 minutes from Nanjing by high-speed rail. Yichun is a flight from Harbin, which is where the beds are. Laibin is an hour from Nanning.

We surface that on the city page rather than pretending the city is covered. You can see the city list sorted by verified hotel count, the attraction index, and the rest of the working notes at sinostay.

The general lesson

If you aggregate messy real-world data, the threshold is the product. Write down what "usable" means, publish the rule next to the number, and accept that the resulting figure will sometimes be smaller than the one your competitor is advertising — and defensible in a way theirs is not.

DEV Community