Wishlisted Products That Are Now Cheaper

Medium
115.

Wishlisted Products That Are Now Cheaper

MediumSubqueriesJoins

MySQL

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

ColumnType
customer_idint
product_idint
added_ondate
price_when_addedint

Primary key (together): 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

ColumnType
product_idint
effective_fromdate
priceint

Primary key (together): product_id, effective_from.

One row per price change; a price holds from effective_from until the next change.

Example

The rows must come back in the order shown.

wishlist

customer_idproduct_idadded_onprice_when_added
15012024-08-0117999
25012024-09-1016499
15022024-08-051299
35032024-08-202199
35042024-08-21399

price_history

product_ideffective_fromprice
5012024-07-0117999
5012024-09-0116499
5012024-10-0115999
5022024-06-151299
5022024-10-021399
5032024-08-012199
5032024-09-151899

Output

customer_idproduct_idprice_when_addedcurrent_pricesaving
150117999159992000
25011649915999500
350321991899300
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
customer_idint
product_idint
added_ondate
price_when_addedint

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

ColumnType
product_idint
effective_fromdate
priceint

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_idproduct_idadded_onprice_when_added
15012024-08-0117999
25012024-09-1016499
15022024-08-051299
35032024-08-202199
35042024-08-21399

price_history

product_ideffective_fromprice
5012024-07-0117999
5012024-09-0116499
5012024-10-0115999
5022024-06-151299
5022024-10-021399
5032024-08-012199
5032024-09-151899

Output

customer_idproduct_idprice_when_addedcurrent_pricesaving
150117999159992000
25011649915999500
350321991899300

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_id

Another 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 →