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.
