by 
Time to try out this ASOF join that they’ve introduced to Snowflake!
In the heart of a bustling financial district lies a brokerage firm’s trading floor, where traders make split-second decisions amid a frenzy of activity. Trades and quotes, recorded in separate tables, hold vital information about transactions and market prices.
To ensure traders have the latest market data, a critical task emerges: pairing each trade with the most recent quote available at or before its time. Analysts devise a sophisticated SQL query, leveraging temporal joins to seamlessly integrate trade and quote data based on timestamps.
Given two tables, trade and quotes, containing transactional data in a financial market, where the trade table records trades and the quotes table records quotes for a specific financial instrument, construct an ASOF join SQL statement to pair each trade record with the most recent quote record available at or before the time of the trade.
Start up Code
create or replace temporary table trade (
type VARCHAR,
id INT,
ticker VARCHAR,
datetime TIMESTAMP_TZ,
price FLOAT,
volume INT
);
create or replace temporary table quotes(
type VARCHAR,
id INT,
ticker VARCHAR,
datetime TIMESTAMP_TZ,
ask FLOAT,
bid FLOAT
);
insert into trade values
('trade', 2, 'AAPL', '2020-01-06 09:00:30.000+09:00', 305, 1),
('trade', 2, 'AAPL', '2020-01-06 09:01:00.000+09:00', 310, 2),
('trade', 2, 'AAPL', '2020-01-06 09:01:30.000+09:00', 308, 1),
('trade', 3, 'GOOGL', '2020-01-06 09:02:00.000+09:00', 1500, 2),
('trade', 3, 'GOOGL', '2020-01-06 09:03:00.000+09:00', 1520, 3),
('trade', 3, 'GOOGL', '2020-01-06 09:03:30.000+09:00', 1515, 1);
insert into quotes values
('quote', 2, 'AAPL', '2020-01-06 08:59:59.999+09:00', 305, 304),
('quote', 2, 'AAPL', '2020-01-06 09:02:00.000+09:00', 311, 309),
('quote', 3, 'GOOGL', '2020-01-06 09:01:00.000+09:00', 1490, 1485),
('quote', 3, 'GOOGL', '2020-01-06 09:04:00.000+09:00', 1530, 1528);
insert into quotes values
('quote', 2, 'AAPL', '2020-01-06 09:00:30.000+09:00', 307, 305),
('quote', 2, 'AAPL', '2020-01-06 09:01:30.000+09:00', 308, 306),
('quote', 3, 'GOOGL', '2020-01-06 09:02:00.000+09:00', 1502, 1498),
('quote', 3, 'GOOGL', '2020-01-06 09:03:30.000+09:00', 1518, 1513); Remember if you want to participate:
- Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘ (note joining our mailing list does not give you a Frosty Friday account)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.
Love this! The ASOF join is useful in so many situations: it could have been used to solve Week 13, had it been available then.
I can see this new functionality being really useful for all sorts of scenarios. I’m looking forward to when ASOF joins start being supported for incremental dynamic tables!
First time using the ASOF JOINs. It’s a cool concept when working with transaction timestamps. It can be applied to analyze trading but also long term investing strategies using DCA (dollar cost averaging for stocks, bonds, crypto, etc.