Level 2 · Check and compare the price rise in SQL

What you are checking, and why

Before you compare anything, prove that you are looking at the right products and the right date. Thomas says “about 30 products, March 1”. The data can confirm both, because order_items stores the price paid on every line, while products.unit_price only holds today’s list price.

A product whose price changed has two different prices across its order lines. That is a grouped condition, so it belongs in HAVING:

SELECT product_id, MIN(unit_price) AS old_price, MAX(unit_price) AS new_price
FROM order_items
GROUP BY product_id
HAVING MIN(unit_price) <> MAX(unit_price);

This returns 29 products. The check in step 1 then labels each of their lines twice, by date and by price, and counts the combinations. If the change happened cleanly on March 1, only two combinations exist: before with the old price, after with the new one. Any other row is a finding in itself.

Like for like, with a control group

Step 2 answers the agreed question. Three choices make the comparison fair:

• Same months, a year apart. March to August 2025 against March to August 2024. The season is the same on both sides.
• Like-for-like products. Only products launched before January 1, 2024, so every product was on sale for the whole of both windows.
• A control group. Every other product is labelled unchanged. Its growth is the baseline: what the store did anyway.

The pattern for two periods side by side is conditional aggregation, the same one used for year-over-year reports:

SUM(CASE WHEN yr = 2024 THEN quantity END) AS u24,
SUM(CASE WHEN yr = 2025 THEN quantity END) AS u25

Keep these raw sums in a CTE and compute the percentage in the final SELECT, so it is calculated from unrounded numbers.

Reading the result

price_group	units_change_pct	revenue_change_pct
raised	21.0	44.3
unchanged	9.2	13.4

If the rise had hurt demand, raised products would grow less than unchanged ones. They grew more. Revenue grew faster than units for raised products because each unit earned about 6% more and because sales shifted towards the dearer items in the group.

Pitfalls

• BETWEEN '2025-03-01' AND '2025-08-31' on a timestamp drops the evening of August 31; use a half-open range.
• Counting cancelled or refunded orders adds sales that never happened.
• IN (SELECT ...) is fine for 29 ids; on millions of rows, a join to the CTE is the faster habit.