How to find the summed difference between multiple dates?

Question

Below table shows the user_portfolio and STOCK_DATA_TRANSPOSED.

In the first table, User 1 has a portfolio of 4 stocks added on the date 2019-12-30.

In the second table, we have the data for each stock the next 5 days.

I would like to get the difference for each stock for each day, to be able to sum it and create a graph that shows the change in profit for the users portfolio across all Names.

How can I do this the smartest way?

select * from user_portfolio where User_ID = 1


ID  Name    Date        Price   User_ID 
4   SO      2019-12-30  64.44   1   
5   RWE.DE  2019-12-30  32.11   1   
6   OPTT    2019-12-30  2.14    1   
7   MMM     2019-12-30  205.11  1   

Select Date, Name, Price from STOCK_DATA_TRANSPOSED

Date       Name Price   
2019-12-31 MMM 176.42
2020-01-02 MMM 180.00
2020-01-03 MMM 178.45
2020-01-06 MMM 178.62
2020-01-07 MMM 177.90
2019-12-31 SO 63.70
2020-01-02 SO 62.62
2020-01-03 SO 62.56
2020-01-06 SO 62.81
2020-01-07 SO 62.62
2020-01-02 RWE.DE 26.96
2020-01-03 RWE.DE 26.84
2020-01-06 RWE.DE 26.45
2020-01-07 RWE.DE 27.05
2020-01-08 RWE.DE 27.17
2019-12-31 OPTT 0.87
2020-01-02 OPTT 0.88
2020-01-03 OPTT 0.84
2020-01-06 OPTT 0.86
2020-01-07 OPTT 0.85

Please see this SQLFIDDLE for test data: http://sqlfiddle.com/#!9/b81035 You can ignore the timestamp and id column in STOCK_DATA_TRANSPOSED.

Desired output could look something like this ( I am aware prices from above table do no match with Date_Price):

Name    Date        Price User_ID Date_Diff Date_Price  % Diff
SO      2019-12-30  64.44   1   2019-12-31  176.4199982 173.77%
RWE.DE  2019-12-30  32.11   1   2019-12-31  180         460.57%
OPTT    2019-12-30  2.14    1   2019-12-31  178.4499969 8238.78%
MMM     2019-12-30  205.11  1   2019-12-31  178.6199951 -12.92%
SO      2019-12-30  64.44   1   2020-01-02  177.8999939 176.07%
RWE.DE  2019-12-30  32.11   1   2020-01-02  62.61999893 95.02%
OPTT    2019-12-30  2.14    1   2020-01-02  62.56000137 2823.36%
MMM     2019-12-30  205.11  1   2020-01-02  62.81000137 -69.38%
SO      2019-12-30  64.44   1   2020-01-03  62.61999893 -2.82%
RWE.DE  2019-12-30  32.11   1   2020-01-03  33.11        3.11%
OPTT    2019-12-30  2.14    1   2020-01-03  0.8799999952 -58.88%
MMM     2019-12-30  205.11  1   2020-01-03  0.8399999738 -99.59%

Meaning each entry in portfolio, will be compared to each Date_Diff after Date.

% Diff is calculated by: (Date_Price - Price)/Price

Answer 1

The basic calculation is to subtract the day price from the purchase price, divide by the purchase price, and turn that into a percentage.

concat((`Price` - `Price2`) / `Price2` * 100, '%') as Diff

A simple join will allow us to do this. Unfortunately there's no primary nor foreign keys declared on the table, so we just hope the name columns match up. The basic join is like so.

select *
from `STOCK_DATA_TRANSPOSED` sdt
join user_portfolio up on up.name2 = sdt.name

Then narrow it down to one user and order it as desired.

select *
from `STOCK_DATA_TRANSPOSED` sdt
join user_portfolio up on up.name2 = sdt.name
where up.user_id = 1
order by `sdt`.`Date` asc, sdt.Name desc

Now add in the desired columns, with some better names, and do the calculation.

select
  sdt.Name,
  up.Date2 as 'Purchase Date',
  up.Price2 as 'Purchase Price',
  up.User_ID,
  sdt.`Date` as 'Closing Date',
  sdt.Price as 'Closing Price',
  concat((sdt.Price - up.Price2) / up.Price2 * 100, '%') as Diff
from `STOCK_DATA_TRANSPOSED` sdt
join user_portfolio up on up.name2 = sdt.name
where up.user_id = 1
order by `sdt`.`Date` asc, sdt.Name desc;

Try it.

Note there is a fundamental flaw in this query. It assumes each stock has a single purchase price. People purchase a certain amount of stocks at a given time. Someone can purchase 5 shares at $3.50 on Monday, 10 shares at $3.60 on Tuesday, and 20 shares at $4 on Wednesday. user_portfolio lacks a amount field to reflect this, and the query doesn't make sense if there's multiple purchases.

---

This schema can be significantly improved.

• Change the charset to utf8mb4 to support full UTF-8.
• Make your ID types consistent, not some signed some unsigned.
• Use bigints for primary keys, 2 billion comes up fast.
• Use consistent table name styles.
• Use informative table names about what's in the table.
• Avoid keyword column names like Date.
  • purchased_at and closing_date are descriptive timestamp conventions.
• Don't use columns like Name2 to avoid conflicts in your queries.
  • Use table aliases and fully qualified names in your queries like up.price.
• Declare all primary and foreign keys.
• Relate tables by ID, not name. Names change.
• Use unique constraints to guarantee impossible situations do not happen.
• Use check constraints to prevent impossible values.
  • MySQL 5.7 ignores them, but eventually you will upgrade to MySQL 8, and they are useful for the reader.
• Don't set limits on columns if you don't have to, like names.
  • varchar(255) unless there's a fundamental reason to restrict it.
• Use numeric for exact numbers, not varchar.
• Use views to maintain backwards compatibility with old queries.

create table users (
    id bigint primary key auto_increment,
    email varchar(255) not null
) default charset=utf8mb4;

-- Store each entity in its own table.
-- This avoids repetition and allows referential integrity.
create table stocks (
    id bigint primary key auto_increment,
    symbol varchar(255) not null,
    name varchar(255) not null
) default charset=utf8mb4;

create table daily_stock_prices (
    -- Declaring a foreign key ensures the stock exists and lets us
    -- clean up related data when it is deleted.
    stock_id bigint not null,
      foreign key(stock_id) references stocks(id) on delete cascade,

    -- numeric provides accurate numbers
    price numeric(18,13) not null check(price > 0),
    closing_date date not null,
    retreived_at datetime not null default now(),
    
    -- prevent duplicate entries
    unique(stock_id, closing_date)
) default charset=utf8mb4;

create table user_portfolio (
    user_id bigint not null,
      foreign key(user_id) references users(id) on delete cascade,
    stock_id bigint not null,
      foreign key(stock_id) references stocks(id) on delete cascade,
    
    paid numeric(18,13) not null check(price > 0),
    amount integer not null check(amount > 0),
    
    purchased_at datetime not null default now()
) default charset=utf8mb4;