CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Ray
Ray

Reputation: 3225

Calculate subtraction from cumulative sum

Here's what my data looks like:

PAIR    TIMESTAMP   SIDE    PRICE       AMOUNT      PROCEEDS    DEPTH_RANK  CUM_SUM_AMOUNT 
BTC-USD 1592328691  ask     9478.383    0.2         1895.6766   1           0.2
BTC-USD 1592328691  ask     9478.384    0.20517     1944.680045 2           0.40517
BTC-USD 1592328691  ask     9478.479    0.26        2464.40454  3           0.66517
BTC-USD 1592328691  ask     9479.784    0.4         3791.9136   4           1.06517
BTC-USD 1592328691  ask     9480.126    0.1335      1265.596821 5           1.19867
BTC-USD 1592328691  ask     9485.722    0.081       768.343482  6           1.27967
BTC-USD 1592328691  ask     9485.723    0.2         1897.1446   7           1.47967
BTC-USD 1592328691  ask     9485.833    0.28082357  2663.845487 8           1.76049357
BTC-USD 1592328691  ask     9485.84     0.00136758  12.97264507 9           1.76186115
BTC-USD 1592328691  ask     9486.874    4.80287064  45564.2286  10          6.56473179
BTC-USD 1592328691  ask     9486.875    5.58780566  53010.81382 11          12.15253745
BTC-USD 1592328691  ask     9488.702    0.3665      3477.609283 12          12.51903745
BTC-USD 1592328691  ask     9496.899    1.1406      10832.163   13          13.65963745
BTC-USD 1592328691  ask     9496.9      0.14785281  1404.143351 14          13.80749026
BTC-USD 1592328691  ask     9496.902    0.00535416  50.84793281 15          13.81284442
BTC-USD 1592328691  ask     9500.749    3.7257      35396.94055 16          17.53854442
BTC-USD 1592328691  ask     9500.75     1.65528473  15726.4464  17          19.19382915
BTC-USD 1592328691  ask     9508.518    0.039       370.832202  18          19.23282915
BTC-USD 1592328691  ask     9512.745    0.15830434  1505.908819 19          19.39113349
BTC-USD 1592328691  ask     9512.746    1.74885185  16636.38344 20          21.13998534

Suppose I have some arbitrary INT-type ORDER_SIZE. I need an additional column FILLED which shows the difference between ORDER_SIZE and AMOUNT such that the cumulative sum of FILLED is equal to the ORDER_SIZE.

It will need to be PARTITION BY-ed on PAIR, TIMESTAMP, and SIDE.

So for example, if my ORDER_SIZE was 5, I would get the following:

PAIR    TIMESTAMP   SIDE    PRICE       AMOUNT      PROCEEDS    DEPTH_RANK  CUM_SUM_AMOUNT  FILLED
BTC-USD 1592328691  ask     9478.383    0.2         1895.6766   1           0.2             0.2
BTC-USD 1592328691  ask     9478.384    0.20517     1944.680045 2           0.40517         0.20517
BTC-USD 1592328691  ask     9478.479    0.26        2464.40454  3           0.66517         0.26
BTC-USD 1592328691  ask     9479.784    0.4         3791.9136   4           1.06517         0.4
BTC-USD 1592328691  ask     9480.126    0.1335      1265.596821 5           1.19867         0.1335
BTC-USD 1592328691  ask     9485.722    0.081       768.343482  6           1.27967         0.081
BTC-USD 1592328691  ask     9485.723    0.2         1897.1446   7           1.47967         0.2
BTC-USD 1592328691  ask     9485.833    0.28082357  2663.845487 8           1.76049357      0.28082357
BTC-USD 1592328691  ask     9485.84     0.00136758  12.97264507 9           1.76186115      0.00136758
BTC-USD 1592328691  ask     9486.874    4.80287064  45564.2286  10          6.56473179      3.23813885
BTC-USD 1592328691  ask     9486.875    5.58780566  53010.81382 11          12.15253745     0
BTC-USD 1592328691  ask     9488.702    0.3665      3477.609283 12          12.51903745     0
BTC-USD 1592328691  ask     9496.899    1.1406      10832.163   13          13.65963745     0
BTC-USD 1592328691  ask     9496.9      0.14785281  1404.143351 14          13.80749026     0
BTC-USD 1592328691  ask     9496.902    0.00535416  50.84793281 15          13.81284442     0

Thus, the cumulative sum of FILLED, down to the 10th deepest order would be our original ORDER_SIZE (5).

How can I accomplish this in a single SELECT? I need this to be return in a table function. I'm actually doing this in Snowflake, but assume I have access to all common SQL window functions (e.g. LEAD, LAG, RANK, etc.)

Thanks!!

Upvotes: 1

Views: 156

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This seems pretty simple ascomparison logic:

select . . .,
       (case when cum_sum_amount < 5 then amount
             when cum_sum_amount > 5 and cum_sum_amount - amount < 5 then 5 - cum_sum_amount
             else 0
        end)
from t;

You can also express this more obtusely as:

select . . . ,
       greatest(0,
                least(amount,
                      5 - cum_sum_amount
                     )
               )
from t;

Upvotes: 1

Related Questions