0% completed
Problem Statement
Table: PriceChanges
This table records the price changes of various products. Each row includes a product ID, the new price of the product, and the date when this new price became effective.
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| product_id | int |
| new_price | int |
| effective_date| date |
+---------------+---------+
(product_id, effective_date) is the primary key for this table.
Each row in this table indicates that the price of a product was changed to a new price on a specific date.
Write a solution to find the prices of all products on a specific date, 2019-08-16
. Assume the price of all products before any price change was 10.
Return the result table in the order of product_id
Example
Input:
PriceChanges table: +------------+-----------+---------------+ | product_id | new_price | effective_date| +------------+-----------+---------------+ | 1 | 20 | 2019-08-14 | | 2 | 50 | 2019-08-14 | | 1 | 30 | 2019-08-15 | | 1 | 35 | 2019-08-16 | | 2 | 65 | 2019-08-17 | | 3 | 20 | 2019-08-18 | +------------+-----------+---------------+
Output:
+------------+-------+ | product_id | price | +------------+-------+ | 1 | 35 | | 2 | 50 | | 3 | 10 | +------------+-------+
Try It Yourself
Solution
To determine the prices of all products on a specific date (2019-08-16
), considering that the default price before any changes is 10, we can follow a systematic approach. This involves identifying the most recent price change for each product up to the specified date and handling cases where no price changes have occurred by assigning the default price.
- Identify Distinct Products: Retrieve all unique
product_id
s from thePriceChanges
table. - Determine the Latest Price Before or On the Specified Date: For each product, find the most recent
new_price
that became effective on or before2019-08-16
. - Assign Default Price Where Applicable: If a product has no price changes before or on
2019-08-16
, assign the default price of 10. - Order the Results: Sort the final output by
product_id
in ascending order.
SQL Query
SELECT p.product_id, COALESCE( (SELECT new_price FROM PriceChanges pc WHERE pc.product_id = p.product_id AND pc.effective_date <= '2019-08-16' ORDER BY pc.effective_date DESC LIMIT 1), 10 ) AS price FROM (SELECT DISTINCT product_id FROM PriceChanges) p ORDER BY p.product_id;
Step-by-Step Approach
Step 1: Identify Distinct Products
Retrieve all unique product_id
s from the PriceChanges
table to ensure that each product is considered in the final report.
SQL Query:
SELECT DISTINCT product_id FROM PriceChanges;
Explanation:
SELECT DISTINCT product_id
:- Selects each unique
product_id
from thePriceChanges
table to avoid processing duplicate entries.
- Selects each unique
FROM PriceChanges
:- Specifies the
PriceChanges
table as the data source.
- Specifies the
Output After Step 1:
Assuming the example input, the subquery would produce:
+------------+ | product_id | +------------+ | 1 | | 2 | | 3 | +------------+
Step 2: Determine the Latest Price Before or On 2019-08-16
For each product, identify the most recent new_price
that became effective on or before 2019-08-16
. This step ensures that we capture the accurate price applicable on the specified date.
SQL Query:
SELECT new_price FROM PriceChanges pc WHERE pc.product_id = p.product_id AND pc.effective_date <= '2019-08-16' ORDER BY pc.effective_date DESC LIMIT 1
Explanation:
SELECT new_price
:- Retrieves the
new_price
for the product.
- Retrieves the
FROM PriceChanges pc
:- Specifies the
PriceChanges
table with an aliaspc
for reference.
- Specifies the
WHERE pc.product_id = p.product_id AND pc.effective_date <= '2019-08-16'
:- Filters the records to include only those where:
- The
product_id
matches the current product being evaluated. - The
effective_date
is on or before2019-08-16
.
- The
- Filters the records to include only those where:
ORDER BY pc.effective_date DESC
:- Orders the filtered records in descending order of
effective_date
to prioritize the most recent price change.
- Orders the filtered records in descending order of
LIMIT 1
:- Restricts the result to the top record, effectively selecting the latest applicable
new_price
.
- Restricts the result to the top record, effectively selecting the latest applicable
Purpose in Main Query:
This subquery is used within the COALESCE
function to fetch the latest price for each product up to the specified date. If no such price exists, COALESCE
will assign the default value of 10.
Step 3: Assign Default Price Where Applicable and Order the Results
Combine the distinct products with their corresponding latest prices or assign the default price of 10 if no price changes occurred before or on 2019-08-16
. Finally, sort the results by product_id
.
SQL Query:
SELECT p.product_id, COALESCE( (SELECT new_price FROM PriceChanges pc WHERE pc.product_id = p.product_id AND pc.effective_date <= '2019-08-16' ORDER BY pc.effective_date DESC LIMIT 1), 10 ) AS price FROM (SELECT DISTINCT product_id FROM PriceChanges) p ORDER BY p.product_id;
Explanation:
SELECT p.product_id, COALESCE(... ) AS price
:- Selects each
product_id
and determines its price using theCOALESCE
function.
- Selects each
COALESCE(..., 10)
:- Attempts to retrieve the latest
new_price
from the subquery. - If the subquery returns
NULL
(i.e., no price changes before or on2019-08-16
), it assigns the default price of 10.
- Attempts to retrieve the latest
ORDER BY p.product_id
:- Sorts the final results in ascending order of
product_id
for organized presentation.
- Sorts the final results in ascending order of
Final Output:
Based on the example input, the final output would be:
+------------+-------+ | product_id | price | +------------+-------+ | 1 | 35 | | 2 | 50 | | 3 | 10 | +------------+-------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible