I’m trying to migrate my SQL query to Flux. there are two tables, Order_hist and Quote. the query needs to get the quote price for 3 seconds after the Order is placed. and then calculate the profit/loss.

in SQL, it’s very simple. a sub query to join the Quote table on product and _time(range) and then get the last price value. I researched many days. but I cannot figure out how to do in Flux.

**order_hist**

_time | orderID | product | price | qty |
---|---|---|---|---|

2020-06-01 00:00:01.000 | Order-1 | prod-A | 100 | 10 |

2020-06-01 00:00:01.000 | Order-2 | prod-B | 20 | 100 |

2020-06-01 00:00:02.000 | Order-3 | prod-B | 21 | 100 |

**quote**

_time | product | price |
---|---|---|

2020-06-01 00:00:01.000 | prod-A | 100 |

2020-06-01 00:00:02.000 | prod-A | 101 |

2020-06-01 00:00:03.000 | prod-A | 102 |

2020-06-01 00:00:04.000 | prod-A | 103 |

2020-06-01 00:00:05.000 | prod-A | 104 |

2020-06-01 00:00:01.000 | prod-B | 20 |

2020-06-01 00:00:02.000 | prod-B | 21 |

2020-06-01 00:00:03.000 | prod-B | 22 |

2020-06-01 00:00:04.000 | prod-B | 23 |

2020-06-01 00:00:05.000 | prod-B | 24 |

select *

,price_3s = (select top 1 price from quote q where q.product = o.product and q._time between o._time and DATEADD(second,3,o._time) order by _time desc )

from order_hist o

_time | orderID | product | price | qty | price_3s |
---|---|---|---|---|---|

2020-06-01 00:00:01.000 | Order-1 | prod-A | 100 | 10 | 103 |

2020-06-01 00:00:01.000 | Order-2 | prod-B | 20 | 100 | 23 |

2020-06-01 00:00:02.000 | Order-3 | prod-B | 21 | 100 | 24 |