Applying CTE for recursive queries

Question

I am trying to apply CTE and recursive queries. The database is MariaDB 10.2 or greater.

Business rules are as follows:

1. An account can either be a holding or a portfolio.
2. A holding consists of a given amount of money.
3. Holdings can be active and inactive.
4. A portfolio contains zero or more accounts, and these accounts can belong to more than one portfolio.
5. The total value of each account is multiplied by a "weight" factor when determining the value of a portfolio.

My schema is as follows (note char is used for id type for illustration purposes only, but I will really use int):

CREATE TABLE IF NOT EXISTS accounts (
  id CHAR(4) NOT NULL,
  name VARCHAR(45) NOT NULL,
  type ENUM('holding', 'portfolio') NULL,
  PRIMARY KEY (id))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS holdings (
  accounts_id CHAR(4) NOT NULL,
  value DECIMAL(6,2) NOT NULL,
  active TINYINT NOT NULL,
  PRIMARY KEY (accounts_id),
  CONSTRAINT fk_holdings_accounts
    FOREIGN KEY (accounts_id)
    REFERENCES accounts (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS portfolios (
  accounts_id CHAR(4) NOT NULL,
  PRIMARY KEY (accounts_id),
  CONSTRAINT fk_portfolios_accounts1
    FOREIGN KEY (accounts_id)
    REFERENCES accounts (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS portfolios_has_accounts (
  portfolios_id CHAR(4) NOT NULL,
  accounts_id CHAR(4) NOT NULL,
  weight DECIMAL(4,2) NOT NULL,
  PRIMARY KEY (portfolios_id, accounts_id),
  INDEX fk_portfolios_has_accounts_accounts1_idx (accounts_id ASC),
  INDEX fk_portfolios_has_accounts_portfolios1_idx (portfolios_id ASC),
  CONSTRAINT fk_portfolios_has_accounts_portfolios1
    FOREIGN KEY (portfolios_id)
    REFERENCES portfolios (accounts_id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_portfolios_has_accounts_accounts1
    FOREIGN KEY (accounts_id)
    REFERENCES accounts (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

Sample data is as follows:

INSERT INTO accounts(id,name,type) VALUES ('p1','portfolio1','portfolio'),('p2','portfolio2','portfolio'),('p3','portfolio3','portfolio'),('h1','holding1','holding'),('h2','holding2','holding'),('h3','holding3','holding'),('h4','holding4','holding');
INSERT INTO holdings(accounts_id,value,active) VALUES ('h1','50','1'),('h2','40','0'),('h3','70','1'),('h4','40','1');
INSERT INTO portfolios(accounts_id) VALUES ('p1'),('p2'),('p3');
INSERT INTO portfolios_has_accounts(portfolios_id,accounts_id,weight) VALUES ('p1','h1','1'),('p1','p2','0.5'),('p2','h2','2'),('p2','p3','1'),('p3','h3','2'),('p3','h4','0.5');

accounts

id  name        type
p1  portfolio1  portfolio
p2  portfolio2  portfolio
p3  portfolio3  portfolio
h1  holding1    holding
h2  holding2    holding
h3  holding3    holding
h4  holding4    holding

portfolios

portfolios_id
p1
p2
p3

holdings

id value active
h1  50   1
h2  40   0
h3  70   1
h4  40   1

portfolios_has_accounts

portfolios_id   accounts_id weight
p1               h1         1
p1               p2         0.5
p2               h2         2
p2               p3         1
p3               h3         2
p3               h4         0.5

My objectives are to find:

1. Find all accounts which only contain active holdings. Given sample data it is p3, h1, h3, and h4. p2 is not included because it includes h2 which is not active, and p1 is not included because it includes p2.

2. The total value of portfolio p1. Given sample data, it is 170: 1*50 + 0.5*( 2*40 + 1*( 2*70 + 0.5*40 ) )

3. The constants which the holdings are multiplied by to result in the total value of portfolio p1. Given the sample data, they are the following (note that 1*h1 + 1*h2 + 1*h3 + 0.25*h4 = 170)

.

id  weight
h1  1
h2  1
h3  1
h4  .25

How can I accomplish this?

Answer 1

Please comment whether these should be done differently, or from a performance perspective, whether they have any major issues?

Objective #1

MariaDB [recursion]> WITH RECURSIVE t AS (
    ->     SELECT accounts_id FROM holdings WHERE active=0
    ->     UNION ALL
    ->     SELECT pha.portfolios_id
    ->     FROM portfolios_has_accounts pha
    ->     INNER JOIN t ON t.accounts_id=pha.accounts_id
    -> )
    -> SELECT a.* FROM accounts a
    -> LEFT OUTER JOIN t ON t.accounts_id=a.id
    -> WHERE t.accounts_id IS NULL;
+----+------------+-----------+
| id | name       | type      |
+----+------------+-----------+
| h1 | holding1   | holding   |
| h3 | holding3   | holding   |
| h4 | holding4   | holding   |
| p3 | portfolio3 | portfolio |
+----+------------+-----------+
4 rows in set (0.00 sec)

Objective #2

MariaDB [recursion]> WITH RECURSIVE t AS (
    -> SELECT pha.*, h.value
    -> FROM portfolios_has_accounts pha
    -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id
    -> WHERE pha.portfolios_id="p1"
    -> UNION ALL
    -> SELECT pha.portfolios_id, pha.accounts_id, pha.weight*t.weight, h.value
    -> FROM t
    -> INNER JOIN portfolios_has_accounts pha ON pha.portfolios_id=t.accounts_id
    -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id
    -> )
    -> SELECT SUM(weight*value) FROM t WHERE value IS NOT NULL;
+-------------------+
| SUM(weight*value) |
+-------------------+
| 170.0000          |
+-------------------+
1 row in set (0.00 sec)

Objective #3

MariaDB [recursion]> WITH RECURSIVE t AS (
    -> SELECT pha.*, h.value
    -> FROM portfolios_has_accounts pha
    -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id
    -> WHERE pha.portfolios_id="p1"
    -> UNION ALL
    -> SELECT pha.portfolios_id, pha.accounts_id, pha.weight*t.weight, h.value
    -> FROM t
    -> INNER JOIN portfolios_has_accounts pha ON pha.portfolios_id=t.accounts_id
    -> LEFT OUTER JOIN holdings h ON h.accounts_id=pha.accounts_id
    -> )
    -> SELECT accounts_id, weight FROM t WHERE value IS NOT NULL;
+-------------+--------+
| accounts_id | weight |
+-------------+--------+
| h1          | 1.00   |
| h2          | 1.00   |
| h3          | 1.00   |
| h4          | 0.25   |
+-------------+--------+
4 rows in set (0.01 sec)

MariaDB [recursion]>