Find the Times of the Minimum and Maximum Prices

Question

Given the following table of prices throughout the trading day:

p:([] time:12:00:00 13:00:00 14:00:00 15:00:00 16:00:00;
      price:3.9 5.1 5.0 4.0 1.7)

Result:

time     price
--------------
12:00:00 3.9
13:00:00 5.1
14:00:00 5
15:00:00 4
16:00:00 1.7

Return:

minTime — the time at which the minimum price occurred
maxTime — the time at which the maximum price occurred

The result may be returned as either a table or a dictionary.

Expected result:

minTime  maxTime
----------------
16:00:00 13:00:00

Solution

select
  minTime:time where price=min price,
  maxTime:time where price=max price
  from p

Result:

minTime  maxTime
----------------
16:00:00 13:00:00

Alternative Solution

`minTime`maxTime!p[`time] p[`price]?(min;max)@\:p[`price]

Result:

minTime| 16:00:00
maxTime| 13:00:00

Explanation

The query:

select
  minTime:time where price=min price,
  maxTime:time where price=max price
  from p

uses a conditional expression within each select clause.

Finding the Minimum Price

The expression:

min price

returns:

1.7

The expression:

time where price=min price

returns:

16:00:00

because that is the time associated with the minimum price.

Finding the Maximum Price

Similarly:

max price

returns:

5.1

and:

time where price=max price

returns:

13:00:00

Handling Duplicate Prices

One advantage of this approach is that it naturally handles duplicate minimum or maximum values.

For example:

p2:([] time:12:00:00 13:00:00 14:00:00;
       price:1.7 5.1 1.7)

Then:

exec time where price=min price from p2

returns:

12:00:00 14:00:00

because both rows contain the minimum price.

If only a single result is required, first can be used:

select
  minTime:first time where price=min price,
  maxTime:first time where price=max price
  from p2

first also makes each result column a simple list of times. Without it, each cell holds a one-item list (meta shows the column type as V), even though the console display looks the same.

Alternative Solution

The expression:

(min;max)@\:p[`price]

returns:

1.7 5.1

The Find operator:

p[`price]?1.7 5.1

returns the indices of those values:

4 1

Those indices are then used to index into the time column:

p[`time] 4 1

Result:

16:00:00 13:00:00

Finally, `minTime`maxTime! pairs the values with descriptive keys, producing:

minTime| 16:00:00
maxTime| 13:00:00

Summary

This question demonstrates two useful approaches:

Using where inside a select statement to locate rows matching an aggregate value.
Using min, max, and ? together to locate the positions of extreme values.

The qSQL solution is generally the most readable and naturally handles cases where multiple rows share the same minimum or maximum price.