Semaine 85 – intermédiaire

Il est temps d’essayer la nouvelle opération de jointure ASOF introduite dans Snowflake !

Au cœur d’un quartier financier animé se trouve le parquet de négociation d’une société de courtage, où les traders prennent des décisions en une fraction de seconde au milieu d’une activité frénétique. Les transactions et les cotations, enregistrées dans des tables distinctes, contiennent des informations essentielles sur les transactions et les prix du marché.

Pour garantir que les traders disposent des dernières données de marché, une tâche critique émerge : associer chaque transaction à la cotation la plus récente disponible à ou avant l’heure de la transaction. Les analystes conçoivent une requête SQL sophistiquée, exploitant des jointures temporelles pour intégrer parfaitement les données des transactions et des cotations en fonction des horodatages.

Étant donné deux tables, trade et quotes, contenant des données transactionnelles sur un marché financier, où la table trade enregistre les transactions et la table quotes enregistre les cotations pour un instrument financier spécifique, construisez une instruction SQL de jointure ASOF pour associer chaque enregistrement de transaction à l’enregistrement de cotation le plus récent disponible à ou avant l’heure de la transaction.

-- votre code pour démarrer
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);