Most Recent Transaction
MediumPremium
Given an e-commerce database with the schema shown below, write a query to fetch the product name and stock of the most recently purchased product
Your query should return a result in the following format:
id | name | stock ----+---------+------- int | varchar | int
This question tests your ability to make use of date fields and the ORDER BY clause alongside LIMIT in order to get the most recently sold item.
WITH last_transaction AS (
SELECT *
FROM transactions
ORDER BY date DESC
LIMIT 1
)
SELECT
p.id,
p.name,
p.stock
FROM last_transaction lt
JOIN products p
ON lt.product_id = p.idThe query will output a result in the following format:
id | name | stock ----+-------------+------- 4 | Notorola X7 | 396
select name, stock from products p left join transactions t on p.id = t.product_id order by date desc limit 1