Time-Travel Queries

Exploring Multi-Version data using bSQL.

What is a Time-Travel Query?

Financial Data Example

Understanding the Lifetime Query

SELECT symbol, price, timestamp
FROM LIFETIME financial.pricing;
Sherlock Codes

Discontinued Data

SELECT * 
FROM LIFETIME financial.pricing
WHERE DISCONTINUED(pricing);
SYMBOL    PRICE   ...    52_WEEK_HIGH       TIMESTAMP
A NULL ... NULL 2020–11–13 07:26:03.650678200 MMM NULL ... NULL 2020–11–13 07:26:03.670677500

Joining and Aggregating Histories

SELECT c.name, COUNT(*) AS number_of_versions, AVG(p.price)
FROM LIFETIME financial.pricing AS p
JOIN financial.companies AS c
ON c.symbol = p.symbol
GROUP BY name
FILTER
10;
C.NAME           NUMBER_OF_VERSIONS             AVG(P.PRICE) 
3M Co. 22 133.92297224564985 ACE Limited 21 103.51996685209728 AES Corp 21 18.495395614987327 AFLAC Inc 21 70.05538577125186 AGL Resources Inc. 21 56.701499938964844 AMETEK Inc 21 59.99528685070219 AT&T Inc 21 25.695445378621418 AbbVie Inc. 21 57.20997020176479 Abbott Laboratories 21 45.31997081211635 Accenture 21 89.67997051420666

Using the Timestamp Column

SELECT symbol, MAX(p.price) AS max_price, MIN(p.price) AS min_price,
MAX(p.timestamp) - MIN(p.timestamp) AS life_span
FROM LIFETIME financial.pricing AS p
GROUP BY p.symbol
ORDER BY life_span DESC
FILTER
10;
SYMBOL          MAX_PRICE         MIN_PRICE           LIFE_SPAN
VLO 52.99 40.63915 182
VMC 68.85 56.499134 182
ZMH 98.06 84.10976 182
VIAB 88.27 75.919136 182
YUM 77.16 63.209763 182
XYL 38.64 24.689758 182
VFC 61.71059 51.781025 182
XRAY 46.86 32.909756 182
XOM 94.99 81.03975 182
V 225.89061 215.96103 182

Conclusion