How I compute a "typical price" for 22,000 LEGO offers (and why shop discounts are mostly theatre)

klockers.pl — Tue, 03 Nov 2026 16:10:36 +0000

Every price comparison site eventually has to answer one question, and it is not "where is it cheapest". It is: is this actually cheap?

Retailers answer it for you, loudly, with a red badge: -50%. The badge means that the current price is half of the regular price the shop declares. That number is a field in a feed. Nobody audits it.

I run https://klockers.pl/, a LEGO price comparison for the Polish market: 18 shops, 5,600 active sets, roughly 22,000 offers, all built on Symfony 8 and MariaDB. This post is about the part that took the most iterations - turning a firehose of affiliate offers into a price history I can reason about. The code is real, trimmed for readability.

The dataset

At the time of writing: 22,842 offers, 5,595 sets with at least two shops, 18 shops, daily snapshots since October. LEGO is a friendly domain for this because two numbers are public for every set: the retail price and the piece count. Price per piece is the metric collectors actually argue about, and it is the one thing a shop cannot fake without changing the product.

Step 1: do not store offers per day

The obvious schema - one price_history row per offer per day costs about 8M rows a year at today's volume, and grows linearly with every shop you add. Most of those rows are useless: nobody asks what shop #7 charged for set #412 three weeks ago.

What people ask is "what was the cheapest I could get this set for, and when". So I store one row per set per day:

CREATE TABLE price_history (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  set_id       INT NOT NULL,
  recorded_on  DATE NOT NULL,
  price        DECIMAL(10,2) NOT NULL,
  offers_count INT NOT NULL,
  UNIQUE KEY uniq_set_day (set_id, recorded_on)
);

That is ~2M rows a year, and every point in it is a daily minimum - the only number that matters for a deal. The cost is that per-shop history is gone. I decided I would rather have a dense, cheap, meaningful series than a sparse, expensive, archival one.

Step 2: make the snapshot idempotent

The snapshot runs after every feed import and again at 23:45, in case the evening import failed. Running it twice must not create a second row for the day, otherwise every median downstream quietly skews.


final class PriceHistoryRecorder
{
    public function __construct(private readonly Connection $db) {}

    public function capture(?\DateTimeImmutable $day = null): int
    {
        $date = ($day ?? new \DateTimeImmutable('today'))->format('Y-m-d');

        $this->db->executeStatement(
            'INSERT INTO price_history (set_id, recorded_on, price, offers_count)
             SELECT id, ?, min_price, offers_count FROM lego_set
             WHERE offers_count > 0 AND min_price IS NOT NULL
             ON DUPLICATE KEY UPDATE
               price = VALUES(price), offers_count = VALUES(offers_count)',
            [$date]
        );

        return $this->eligibleSets();
    }
}

Two details that matter more than they look:

WHERE offers_count > 0 - a set with no live offer must not pollute the series with a zero or a gap. A gap is meaningful; a zero is a bug.
The upsert is the whole point. Cron overlap, manual re-runs, a deploy in the middle of the window - none of it can corrupt the day.
Step 3: median, not average
Median of the daily minimums over the last 30 days is my "typical price". The average is the wrong statistic here, and the reason is boring: one feed error is enough. A shop that publishes 14.99 zł instead of 149.99 zł for a day moves the mean by several złoty and the median by nothing.

The naive implementation is a SELECT per set. With 5,600 sets that is 5,600 queries every night. Instead: one query, group in PHP, bulk upsert, one join.


$rows = $this->db->fetchAllAssociative(
    'SELECT set_id, price FROM price_history
     WHERE recorded_on >= CURDATE() - INTERVAL '.($days - 1).' DAY
     ORDER BY set_id ASC, recorded_on ASC'
);

$bySet = [];
foreach ($rows as $row) {
    $bySet[(int) $row['set_id']][] = (float) $row['price'];
}

$updates = [];
foreach ($bySet as $setId => $prices) {
    sort($prices);
    $count  = \count($prices);
    $middle = intdiv($count, 2);
    $median = 0 === $count % 2
        ? ($prices[$middle - 1] + $prices[$middle]) / 2
        : $prices[$middle];

    $updates[] = [$setId, number_format($median, 2, '.', '')];
}

foreach (array_chunk($updates, 500) as $chunk) {
    $values = [];
    $params = [];
    foreach ($chunk as [$setId, $median]) {
        $values[] = '(?,?)';
        array_push($params, $setId, $median);
    }

    $this->db->executeStatement(
        'INSERT INTO _price_stats (set_id, typical_price) VALUES '.implode(',', $values).'
         ON DUPLICATE KEY UPDATE typical_price = VALUES(typical_price)',
        $params
    );
}

Then a single UPDATE ... JOIN pushes the values onto the catalog table, and the temp table is dropped. The whole refresh for 5,600 sets runs in a couple of seconds.

Note what is not here: no window function, no PERCENTILE_CONT (MariaDB does not have one), no ORM. The statistics are the one place where I want SQL and arrays, not entities.

Step 4: the guards that actually save the data

Matching is where a price comparison dies. The feed gives you a title, an optional manufacturer number, sometimes an EAN, and a price. Everything else is inference.

Three guards earn their keep:

Set number extraction. Prefer the explicit field, then the pattern. Empik puts the number at the end of the title, some shops put polybag numbers (3 digits) in the middle:


$explicit = trim($fields['attr_201105'] ?? $fields['Numer produktu'] ?? '');
if ('' !== $explicit && ctype_digit($explicit)) {
    return ltrim($explicit, '0');
}

if (preg_match('/\b(\d{4,6})\b/', $name, $match)) {
    return ltrim($match[1], '0');
}

return preg_match('/(?<!\d)(\d{3})(?!\d)/', $name, $match) ? ltrim($match[1], '0') : null;

Careful: the base number is not the identity. 985 base numbers in the LEGO catalog have variants - 30066-1 through 30066-5 are five different sets. Keying on the base number produced 2,513 duplicate records in my first import.

Reject non-sets. A title is a no-go if it smells like a knockoff, an incomplete set, a used one, or a box-less one:

return (bool) preg_match(
    '/kompatybiln|lepin|podr[oó]b|niekomplet|without box|no box|bez pude[lł]ka|u[zż]ywan|uszkodz/u',
    mb_strtolower($title)
);

The 4% rule. The average LEGO piece costs around 0.45 zł. If an offer works out to less than 0.04 zł per piece, it is not a bargain - it is a feed bug or a single part listed as a set:

if ($pieces > 100 && $offer->price < $pieces * 0.04) {
    ++$skipped;
    continue;
}

The $pieces > 100 guard matters: for a 40-piece polybag, weird ratios are normal.

Every import prints what happened - seen, matched, saved, skipped, per shop - and a --dry-run flag runs the same code without writing. When a shop changes its feed format, the skip count jumps and you see it the same evening, not three weeks later in a graph.

What the numbers say
Once the pipeline behaved, the data became interesting:

Median price of a 2026 set: 151.99 zł, up from 221.52 zł average in 2025 - but that average is not comparable, the mix of sets changed.
Price per piece by year: 0.53 zł (2024) → 0.55 zł (2025) → 0.45 zł (2026).
Average discount against the shop-declared regular price: 25.3%.
That last number is the one to be suspicious about. 25% average discount across 2,054 sets does not mean the market is 25% off. It means shops are consistent about declaring a regular price that makes a discount look good.

Which is the whole argument for computing your own baseline: the shop's "regular" is an input, not a fact. The 30-day median is a fact, at least as long as your history is long enough to have one - and mine is only a few months old, which I say out loud on the site.

Five things I would do again

Snapshot the minimum, not the offer. Cheaper, and it answers the actual question.
Make every write idempotent. ON DUPLICATE KEY UPDATE has saved me from cron overlap, re-deploys and my own mistakes more times than any test.
Dry-run first, always. Same code path, no writes, full statistics.
Median for prices, mean for nothing. One bad feed row should not move the number.
A set with one shop is not a comparison. I only publish a set once at least two shops stock it. Fewer pages, more honest ones.

Disclosure: I run https://klockers.pl, a LEGO price comparison for Poland. The site earns affiliate commission from some of the shops it links to. The method described here is documented publicly at https://klockers.pl/jak-liczymy-ceny, and the aggregate numbers come from the 2026 price report. No data in this post comes from a source I was paid to mention.

DEV Community: klockers.pl

How I compute a "typical price" for 22,000 LEGO offers (and why shop discounts are mostly theatre)