x_portfolio_return, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

select version() as 'mysql version';

DROP TABLE IF EXISTS x_ports;
DROP TABLE IF EXISTS x_weights;
DROP TABLE IF EXISTS x_prices;

CREATE TABLE IF NOT EXISTS x_ports (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS x_weights (id INT NOT NULL AUTO_INCREMENT, port_id INT, inst_id INT, weight DOUBLE, PRIMARY KEY (id));
CREATE TABLE IF NOT EXISTS x_prices (id INT NOT NULL AUTO_INCREMENT, inst_id INT, trade_date DATE, price DOUBLE, PRIMARY KEY (id));

INSERT INTO x_ports (name) VALUES ('PORT A');
INSERT INTO x_ports (name) VALUES ('PORT B');

INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 1, 20.0);
INSERT INTO x_weights (port_id, inst_id, weight) VALUES (1, 2, 80.0);
INSERT INTO x_weights (port_id, inst_id, weight) VALUES (2, 1, 100.0);

INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-01', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-02', 1.13);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-03', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-04', 1.12);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-05', 1.13);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (1, '2018-01-06', 1.14);

INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-01', 50.23);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-02', 50.45);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-03', 50.30);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-04', 50.29);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-05', 50.40);
INSERT INTO x_prices (inst_id, trade_date, price) VALUES (2, '2018-01-06', 50.66);

# GETTING THE DATES

SET @DtShort='2018-01-01';
SET @DtLong=@DtShort;

SELECT
    @DtShort:=@DtLong as date_prev,
    @DtLong:=dt.trade_date as date_curent
FROM
    (SELECT DISTINCT trade_date FROM x_prices ORDER BY trade_date) dt;

# GETTING RETURN FOR SINGLE DAY

SET @DtToday='2018-01-03';
SET @DtYesterday='2018-01-02';

SELECT
    x2.trade_date,
    x2.portfolio,
    sum(x2.val*x2.weight)/sum(x2.weight) as ret
FROM

(SELECT
        x1.trade_date, 
        x1.portfolio,
        sum(x1.weight)/2.0 as weight,
        sum(x1.val_end)/sum(x1.val_start) as val, 
        sum(x1.val_start) as val_start,
        sum(x1.val_end) as val_end
    FROM

(SELECT
            @DtToday as trade_date,
            prt.name as portfolio,
            wts.inst_id as iid,
            wts.weight,
            if(prc.trade_date=@DtToday,prc.price*wts.weight,0) as val_start,
            if(prc.trade_date=@DtYesterday,prc.price*wts.weight,0) as val_end
        FROM
            x_ports prt,
            x_weights wts,
            x_prices prc
        WHERE
            wts.port_id=prt.id and 
            prc.inst_id=wts.inst_id and
            (prc.trade_date=@DtToday or prc.trade_date=@DtYesterday)) x1

GROUP BY x1.portfolio) x2

GROUP BY x2.portfolio;


absolute service time: 0,42 sec