Finding used cars priced below market in Spain, with one query

The screen itself is four lines of SQL. Everything difficult about this task is in the two biases you inherit for free and the three categories of gap that look like bargains and are not.

The screen, in full

SELECT ad_id, url, make, model, year, km, hp,
       environmental_label, province, seller_type,
       price_eur, price_average_indicator_eur AS market_eur,
       ROUND(100.0 * (price_average_indicator_eur - price_eur)
             / price_average_indicator_eur, 1) AS gap_pct
FROM listings
WHERE price_average_indicator_eur IS NOT NULL
  AND price_rank_indicator IN ('below_market', 'good_price')
  AND price_eur < price_average_indicator_eur * 0.88
  AND km < 150000
  AND environmental_label IS NOT NULL
  AND data_age_hours < 48
ORDER BY gap_pct DESC
LIMIT 100;

Six conditions, and each one removes a specific class of false positive. The rest of this article is why each is there.

Why one condition is not enough

An absolute gap on its own produces two kinds of noise. Rounding artefacts at the cheap end — a €3,000 car €400 below a €3,400 average is a 12% gap and a rounding error — and model-mix effects at the expensive end, where a base trim sits below the average of a model whose average is dominated by higher trims.

Adding price_rank_indicator filters both, because the site's own rank is computed with context you do not have. Using the two together means you are asking: is this car cheap by the numbers, and does the marketplace itself think so?

The percentage threshold does the third job. 88% of the estimate is roughly the point where the gap exceeds normal trim and condition variance; below 80% the results skew heavily toward cars with something wrong that the data does not show.

The coverage bias you inherit

The valuation is present on 86.6% of dealer listings and 38.2% of private ones. So the first condition in the screen — price_average_indicator_eur IS NOT NULL — silently makes this mostly a dealer screen.

That may be what you want: dealers are the segment that trades at volume, publishes phone numbers and can be contacted at scale. But if your thesis is that private sellers are cheaper, this screen cannot test it, and running it anyway produces a confident answer to a question it did not ask.

To compare segments, screen them separately with segment-specific reference prices — the coverage numbers are the reason.

The gaps that are not bargains

Three explanations account for most of the largest gaps:

• Mileage. The valuation is a model-level average, so a car at 220,000 km is compared against a reference dominated by cars at half that. The gap is real and correctly priced.
• No DGT badge. A car with no environmental badge cannot enter the low-emission zones in Madrid or Barcelona. That restriction is priced into the ask and is invisible unless you read environmental_label — what the codes mean.
• Stale rows. A cached row from six days ago may describe a car that sold on day two. data_age_hours exists so this is checkable rather than assumed.

Subtract those three and the list gets much shorter and much more interesting. What remains is mostly dealers repricing slow stock and cars in provinces where that model is oversupplied.

Freshness decides whether the car still exists

This is the one condition people leave out and then wonder why half their leads are dead. Rows can be served from a shared cache — which is why runs are cheap — and every row reports which it was through from_cache, fetched_at and data_age_hours.

For a live bargain hunt, set maxCacheAgeDays: 0 so every row is read fresh during your run, and accept the higher cost. For depreciation analysis, week-old rows are perfectly fine and the cache is a gift.

A worked example

Take a single make in a single province, fresh, with dealers included so you can actually make contact:

{
  "maxResults": 3000,
  "makes": ["bmw"],
  "provinces": ["madrid", "barcelona", "valencia"],
  "maxCacheAgeDays": 0,
  "scrapeDealers": true,
  "maxDealers": 150
}

Three thousand listings is $6, plus $0.60 for the dealer profiles. Run the screen above and you will typically be left with a few dozen rows. Join the dealer dataset on the seller to get a phone number on 100% of the dealer ones, and you have a list you can act on the same morning.

Repeat it on a schedule and the interesting signal is not the list — it is the churn. A car that appears in the below-market set and disappears within a day was correctly priced by somebody faster than you.

Scaling it to the whole country

50,000 rows per run is the cap, so a national sweep is several runs partitioned by facet, which is the better operational shape anyway: a failure costs one partition. Partition by make for analysis, by province for lead generation.

And keep the raw rows. The below-market screen is cheap to re-run; re-scraping last week to discover which listings vanished is impossible. Disappearance from the listing set is the closest proxy you have for a sale, and it only exists if you kept the earlier snapshot — turning snapshots into a time series.