Use most recent date for missing dates when joining tables

Question

I have 2 tables:

1.tran_test : (id, amount, currency(Currency_Name), date_1)
2.cur_test: (id, currency, date_2, price(amount In USD))

How to get the total sum of sales for each day considering that some of the dates in cur.test table are missing so you need to use the last available dates to calculate the result?

I wrote a query that calculates the sum, but it excludes days where the rates are missing.

How to calculates amounts for missing days?

SELECT date_1, SUM(amount*c.price) AS sum_by_day
 FROM tran_test AS t
 INNER JOIN cur_test AS c
 ON t.currency = c.currency AND date_1 = date_2
 GROUP BY date_1;

I don't need to just generate dates. I need to calculate amounts using data available in the tables. If some of the dates are missing I need to take the last available date when currency rates are available. Eg - The table tran.test has a transaction for September 28, but currency rates for this day are not provided, so i need to take the last available rate

Sample of similar data

INSERT INTO tran_test (amount, currency, date_1) VALUES 
(1000,'RUB','2018-09-01'),
(15,'USD','2018-09-01'),
(10,'EUR','2018-09-01'),

INSERT INTO cur_test (name,date_2, price) VALUES
('RUB','2018-01-09',0.02),
('USD','2018-01-09',1),

Output should be like date - total amount for the date 2018-02-21 - 2128

Full code is here - https://www.db-fiddle.com/f/v4SHW9XgtgyehbhanQFRx5/2 Actual data is little more complicated and includes dates in UNIX format

Answer 1

Write a subquery that finds the most recent date in the currency table for each date in the transaction table.

SELECT t.date_1, MAX(c.date_2) AS latest_date
FROM tran_test AS t
JOIN cur_test AS c ON t.date_1 >= c.date_2
GROUP BY t.date1

You can then join this with the rest of your query.

SELECT t.date_1, SUM(t.amount * c.price) AS sum_by_day
FROM tran_test AS t
JOIN (
    SELECT t.date_1, MAX(c.date_2) AS latest_date
    FROM tran_test AS t
    JOIN cur_test AS c ON t.date_1 >= c.date_2
    GROUP BY t.date1) AS g ON g.date_1 = t.date_1
JOIN cur_test AS c ON c.date_2 = g.latest_date
GROUP BY t.date_1

Answer 2

select date_1, SUM(amount*c.price) AS sum_by_day
from
(
    select *, max (date_2) as lastDate
    from tran_test AS t
    INNER JOIN cur_test AS c
    ON t.currency = c.currency AND date_1 >= date_2
    GROUP BY date_1;
) as t
INNER JOIN cur_test AS c
ON t.currency = c.currency AND lastdate = date_2
GROUP BY date_1;