Investment Portfolio Data Modeling Example for Cassandra

Create a keyspace

Create the investment_data keyspace:

CREATE KEYSPACE investment_data
WITH replication = {
  'class': 'NetworkTopologyStrategy', 
  'DC-Houston': 1 };

Set the current working keyspace:

USE investment_data;

Create tables

Create table accounts_by_user:

CREATE TABLE accounts_by_user (
  username TEXT,
  account_number TEXT,
  cash_balance DECIMAL,
  name TEXT STATIC,
  PRIMARY KEY ((username),account_number)
);

Create table positions_by_account:

CREATE TABLE positions_by_account (
  account TEXT,
  symbol TEXT,
  quantity DECIMAL,
  PRIMARY KEY ((account),symbol)
);

Create table trades_by_a_d:

CREATE TABLE trades_by_a_d (
  account TEXT,
  trade_id TIMEUUID,
  type TEXT,
  symbol TEXT,
  shares DECIMAL,
  price DECIMAL,
  amount DECIMAL,
  PRIMARY KEY ((account),trade_id)
) WITH CLUSTERING ORDER BY (trade_id DESC);

Create table trades_by_a_td:

CREATE TABLE trades_by_a_td (
  account TEXT,
  trade_id TIMEUUID,
  type TEXT,
  symbol TEXT,
  shares DECIMAL,
  price DECIMAL,
  amount DECIMAL,
  PRIMARY KEY ((account),type,trade_id)
) WITH CLUSTERING ORDER BY (type ASC, trade_id DESC);

Create table trades_by_a_std:

CREATE TABLE trades_by_a_std (
  account TEXT,
  trade_id TIMEUUID,
  type TEXT,
  symbol TEXT,
  shares DECIMAL,
  price DECIMAL,
  amount DECIMAL,
  PRIMARY KEY ((account),symbol,type,trade_id)
) WITH CLUSTERING ORDER BY (symbol ASC, type ASC, trade_id DESC);

Create table trades_by_a_sd:

CREATE TABLE trades_by_a_sd (
  account TEXT,
  trade_id TIMEUUID,
  type TEXT,
  symbol TEXT,
  shares DECIMAL,
  price DECIMAL,
  amount DECIMAL,
  PRIMARY KEY ((account),symbol,trade_id)
) WITH CLUSTERING ORDER BY (symbol ASC, trade_id DESC);

Populate tables

Execute the CQL script to insert sample data:

SOURCE '~/investment_data.cql'

Retrieve all rows from table accounts_by_user:

SELECT * FROM accounts_by_user;

Retrieve all rows from table positions_by_account:

SELECT * FROM positions_by_account;

Retrieve all rows from table trades_by_a_d:

SELECT * FROM trades_by_a_d;

Retrieve all rows from table trades_by_a_td:

SELECT * FROM trades_by_a_td;

Retrieve all rows from table trades_by_a_std:

SELECT * FROM trades_by_a_std;

Retrieve all rows from table trades_by_a_sd:

SELECT * FROM trades_by_a_sd;

Design query Q1

Find information about all investment accounts of a user with username joe:

Solution

SELECT *
FROM accounts_by_user
WHERE username = 'joe';

Design query Q2

Find all positions in account joe001; order by instrument symbol (asc):

Solution

SELECT * 
FROM positions_by_account
WHERE account = 'joe001';

Design query Q3.1

Find all trades for account joe001; order by trade date (desc):

Solution

SELECT account, 
       TODATE(DATEOF(trade_id)) AS date, 
       trade_id, type, symbol,
       shares, price, amount 
FROM trades_by_a_d
WHERE account = 'joe001';

Design query Q3.2

Find all trades for account joe001 and date range 2020-09-07 - 2020-09-11; order by trade date (desc):

Solution

SELECT account, 
       TODATE(DATEOF(trade_id)) AS date, 
       trade_id, type, symbol,
       shares, price, amount 
FROM trades_by_a_d
WHERE account = 'joe001'
  AND trade_id > maxTimeuuid('2020-09-07')
  AND trade_id < minTimeuuid('2020-09-12');

Design query Q3.3

Find all trades for account joe001, date range 2020-09-07 - 2020-09-11 and transaction type buy; order by trade date (desc):

Solution

SELECT account, 
       TODATE(DATEOF(trade_id)) AS date, 
       trade_id, type, symbol,
       shares, price, amount 
FROM trades_by_a_td
WHERE account = 'joe001'
  AND type = 'buy'
  AND trade_id > maxTimeuuid('2020-09-07')
  AND trade_id < minTimeuuid('2020-09-12');

Design query Q3.4

Find all trades for account joe001, date range 2020-09-07 - 2020-09-11, transaction type buy and instrument symbol AAPL; order by trade date (desc):

Solution

SELECT account, 
       TODATE(DATEOF(trade_id)) AS date, 
       trade_id, type, symbol,
       shares, price, amount 
FROM trades_by_a_std
WHERE account = 'joe001'
  AND symbol = 'AAPL'
  AND type = 'buy'
  AND trade_id > maxTimeuuid('2020-09-07')
  AND trade_id < minTimeuuid('2020-09-12');

Design query Q3.5

Find all trades for account joe001, date range 2020-09-07 - 2020-09-11 and instrument symbol AAPL; order by trade date (desc):

Solution

SELECT account, 
       TODATE(DATEOF(trade_id)) AS date, 
       trade_id, type, symbol,
       shares, price, amount 
FROM trades_by_a_sd
WHERE account = 'joe001'
  AND symbol = 'AAPL'
  AND trade_id > maxTimeuuid('2020-09-07')
  AND trade_id < minTimeuuid('2020-09-12');

cd <directory>	Change directory
ls	List directory
echo 'contents' > <file>	Write contents to a file
cat <file>	Output contents of file

i	In Command Mode	Change into Insert Mode, you can now insert and edit text in the file
esc	In Insert Mode	Change into Command Mode, you can now execute commands
:wq	In Command Mode	Save and Exit

Welcome!

Congratulations!

You've completed the scenario!

Scenario Rating

Steps