CODEWITHSUNDEEP
mysqlsqltime-series
IKnowHowBitcoinWorks
IKnowHowBitcoinWorks

Reputation: 349

Using quarterly data on daily data in sql

I'm trying to do some feature engineering with daily stock prices and their financial statements.

Say if the stock_price table columns look like this, with rows updating every day.

OPEN | HIGH | LOW | CLOSE | VOL | DATE
1    | 3    | 0.8 | 1.2   | 5   | 2020-10-1
1.2  | 1.3  | 1   | 1.28  | 3   | 2020-10-2
1.4  | 2.1  | 1.4 | 2.0   | 5   | 2020-10-3
...
0.9  | 1.3  | 0.7 | 1     | 3   | 2021-1-8
1.2  | 1.5  | 1   | 1.4   | 3   | 2021-1-9

The financial_statements table columns look like this

REVENUE | EPS | PUBLISHED_DATE
0.8     | 0.8 | 2020-7-1           < Q2 data
1       | 1   | 2020-10-2          < Q3 data
1.6     | 1.6 | 2021-1-9           < Q4 data

Then I want to get this

OPEN | HIGH | LOW | CLOSE | VOL | DATE       | REVENUE_X_10
1    | 3    | 0.8 | 1.2   | 5   | 2020-10-1  | 8
1.2  | 1.3  | 1   | 1.28  | 3   | 2020-10-2  | 10        < Q3 statement published this day
1.4  | 2.1  | 1.4 | 2.0   | 5   | 2020-10-3  | 10
...                                                      < all these rows use Q3 data
0.9  | 1.3  | 0.7 | 1     | 3   | 2021-1-8   | 10        
1.2  | 1.5  | 1   | 1.4   | 3   | 2021-1-9   | 16        < Q4 statement published this day

Is this possible in MySQL?

Upvotes: 1

Views: 249

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I think you just want a correlated subquery:

select sp.*,
       (select fs.revenue * 10
        from financial_statements fs
        where fs.published_date <= sp.date
        order by fs.publichsed_date desc
        limit 1
       ) as fs_revenue_times_10
from stock_price sp

Upvotes: 1

Related Questions