# Analytics Functions Example 2: Weighted Average

> ## Volume Weighted Average Buy and Sell Prices

> Given a table of trades with price and volume, the following SQL `select` statement will compare each trade price `Pn` against its previous one `Pn-1`. If `Pn-1 > Pn`, it is a buy initiated trade; if `Pn-1 < Pn`, it is a sell initiated trade; if `Pn-1 = Pn`, it will compare against `Pn-2`, or `Pn-3` and so on until it finds the last price movement. Then it calculates the volume weighted average price of all the buy initiated trades `VWAPB` and all the sell initiated trades `VWAPS`:

>         SELECT seq_wavg( seq_filter( seq_trend(price) < 0, volume),
>             seq_filter( seq_trend(price) < 0, price) ) as VWAPB,
>             seq_wavg( seq_filter( seq_trend(price) > 0, volume),
>             seq_filter( seq_trend(price) > 0, price)) as VWAPS
>         FROM Security

> This example relies on the function `seq_trend()` to determine which way the compared prices are “trending”; if `Pn-1` is less than `Pn` `seq_trend()` returns -1 which indicates a “sell” trade; if `Pn-1` is greater than `Pn` `seq_trend()` returns 1 which indicates a “buy” trade. For example, consider the sequence of price values:

>         { 1, 2, 3, 3, 2, 2, 4, 5, 6, 5, 5 }

> For this sequence `mco_seq_trend()` would produce the following resulting sequence:

>         { 0, 1, 1, 1,-1,-1, 1, 1, 1,-1,-1 }

> The two calls to `seq_filter()` return sequences with only those elements for which the “trend” for price is less than 0 for `VWAPB`, greater than 0 for `VWAPS`. Finally the call to `seq_wavg()` returns the weighted average of these two sequences. For example, given the sequence of (price, volume) values:

>         { (10.0, 100),  (10.1, 150),  (10.2,200),  (10.2,300),  (9.9, 90),  (9.8,110)}

> The “buy” trades (`Pn-1 > Pn`) would result from the pairs:

>         (10.2,300) <> (9.9, 90)
>         (9.9, 90)    <> (9.8,110)

> The “sell” trades (`Pn-1 < Pn`) would result from the pairs:

>         (10.0, 100) <> (10.1, 150)
>         (10.1, 150) <> (10.2,200)
>         (10.1,150)  <> (10.2,300)

> And the resulting values would be:

>         VWAPB = (9.9 * 90 + 9.8 * 110) / (90 + 110) = 9.845

>         VWAPS = (10.1 * 150 + 10.2 * 200 + 10.2 * 300) / (150 + 200 + 300) = 10.177

> ## To run this example

> A sample script to demonstrate this `select` statement using xSQL can be run from the `samples/xsql/scripts/financial` directory with the following command:

>         x 2