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
users products +---------------+---------+ +-----------------+---------+ +--| id | int | +-----| id | int | | | first_name | varchar | | | name | varchar | | | last_name | varchar | | +->| product_line_id | date | | | user_city | int | | | | stock | int | | | email | int | | | +-----------------+---------+ | +---------------+---------+ | | | | | | transactions | | product_lines | +---------------+---------+ | | +--------+--------+ +---->| id | int |<----+ +--| id | int | | customer_id | int | | name | varchar| | product_id | int | +--------+--------+ | amount | int | | currency_code | varchar | | date | date | +---------------+---------+ exchange_rate +----------------------+---------+ | id | int | | source_currency_code | varchar | | target_currency_code | varchar | | rate | numeric | +----------------------+---------+
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