How to create a table with the "Revenue" column, taking into account the current price of the product?

Question

PostgreSQL

Hello! There are two tables:
1) "Prices" (prices) with columns "product name", "price", "price effective date" (for the same product, the price has changed over time)
2) "Sales" (sales ) with columns "product name", "date of sale", "number of sold units of goods"
You need to create a query to create a table with the columns "product name", "sale date", "revenue", where revenue is calculated as the product of the quantity and the current given date price.
How to write a condition so that the product takes into account the price indicated on the side closest to the lower date? As a result of my query, some records are calculated at wrong prices: CREATE TABLE revenue AS SELECT sales.product, sales.date, sales.amount * prices.price AS revenue FROM sales INNER JOIN prices ON sales.product = prices.product WHERE sales .date >= prices.date_start group by sales.date;
Tell me which way to think.

Answer 1

Well, for starters, it would be worth pulling out the hands of an afftor of just such a data structure ...
Then mourn and pervert-pervert with a bunch of reservations ...
For example, you can take and get the price later than the desired date as:

select top 1
цена
from Цены
where дата вступления цены в силу<=искомая дата and товар=товар
order by дата вступления цены в силу desc

at least the above piece suggests itself as a nested query or as a cte piece or as a join fragment