Wishlisted Products That Are Now Cheaper — SQL Problem
The "Price drop on your wishlist" push notification goes to customers whose saved product now costs less than when they saved it.
- Difficulty: Medium
- Topics: Subqueries, Joins
- Dialect: MySQL
- Problem: #115
Problem statement
The "Price drop on your wishlist" push notification goes to customers whose saved product now costs less than when they saved it. A product's current price is the price of its price_history row with the latest effective_from; a product with no price history is skipped.
Return every wishlist entry whose current price is strictly below price_when_added, with the columns customer_id, product_id, price_when_added, current_price and saving (the difference). Order by saving descending, then customer_id, then product_id.
Tables
Table: wishlist
| Column | Type |
|---|---|
| customer_id | int |
| product_id | int |
| added_on | date |
| price_when_added | int |
Primary key: customer_id, product_id.
One row per saved product; price_when_added is the price shown at the moment it was saved.
Table: price_history
| Column | Type |
|---|---|
| product_id | int |
| effective_from | date |
| price | int |
Primary key: product_id, effective_from.
One row per price change; a price holds from effective_from until the next change.
Examples
Example 1
wishlist
| customer_id | product_id | added_on | price_when_added |
|---|---|---|---|
| 1 | 501 | 2024-08-01 | 17999 |
| 2 | 501 | 2024-09-10 | 16499 |
| 1 | 502 | 2024-08-05 | 1299 |
| 3 | 503 | 2024-08-20 | 2199 |
| 3 | 504 | 2024-08-21 | 399 |
price_history
| product_id | effective_from | price |
|---|---|---|
| 501 | 2024-07-01 | 17999 |
| 501 | 2024-09-01 | 16499 |
| 501 | 2024-10-01 | 15999 |
| 502 | 2024-06-15 | 1299 |
| 502 | 2024-10-02 | 1399 |
| 503 | 2024-08-01 | 2199 |
| 503 | 2024-09-15 | 1899 |
Output
| customer_id | product_id | price_when_added | current_price | saving |
|---|---|---|---|---|
| 1 | 501 | 17999 | 15999 | 2000 |
| 2 | 501 | 16499 | 15999 | 500 |
| 3 | 503 | 2199 | 1899 | 300 |
How to solve Wishlisted Products That Are Now Cheaper
The current price is a latest row per group lookup: for each product, the price_history row whose effective_from equals the product's maximum. The reference joins the wishlist to the history and keeps only the history row matching a correlated MAX(effective_from); because (product_id, effective_from) is the primary key, exactly one row survives per product.
With the current price beside each wishlist entry, the rest is a filter and a subtraction. The comparison is strict: customer 2 saved product 501 at ₹16,499 and it is now ₹15,999, a saving of ₹500; customer 1 saved the same product at ₹17,999 and saves ₹2,000. Product 502 went up, so its entry is out, and 504 has no history at all — the inner join drops it.
The window alternative numbers each product's history newest first and joins row 1. Both read the history once per product; the window version is usually faster on a long history because it sorts once instead of probing per entry.
Reference solution (MySQL)
SELECT w.customer_id, w.product_id, w.price_when_added, h.price AS current_price,
w.price_when_added - h.price AS saving
FROM wishlist w
JOIN price_history h ON h.product_id = w.product_id
WHERE h.effective_from = (SELECT MAX(effective_from) FROM price_history x WHERE x.product_id = w.product_id)
AND h.price < w.price_when_added
ORDER BY saving DESC, w.customer_id, w.product_idAnother way
WITH latest AS (
SELECT product_id, price, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY effective_from DESC) AS rn
FROM price_history
)
SELECT w.customer_id, w.product_id, w.price_when_added, l.price AS current_price, w.price_when_added - l.price AS saving
FROM wishlist w JOIN latest l ON l.product_id = w.product_id AND l.rn = 1
WHERE l.price < w.price_when_added
ORDER BY saving DESC, w.customer_id, w.product_id← GST Split Into CGST, SGST and IGST by Seller · Shopping Sessions Abandoned After Add to Cart →