Trading and investment strategy application

Problem Definition

You have developed an application to learn trading and investment strategy and you want to build a database to show records of the selling price of a virtual financial instrument in your application called meta-stocks. In your application, there are different meta-stocks markets, and for each meta-stock market you want to store the name and the acronym. Within a meta-stock market, there are several meta-stocks representing ownership on virtual businesses. For each meta-stock, you want to store the name and the acronym. You also would like to store information about users in your application, like the username and email address. Finally, you want to store in a table called meta-records timestamped records of buying and selling operations. For each record, you need to store a timestamp indicating the time and date of the operation, the selling price of the operation, a references to the meta-stock, and references to the buyer (user that bought the meta-stock) and the seller (user that sold the meta-stock). Design a database to support this fascinating application

Solution

The following diagram represents a possible solution of the database:

tradetrainer

This solution considers four entities: meta_markets, meta_stocks, users, and meta_records.

The meta_markets table has two columns, name and acronym, that store the name and acronym of a meta-stock market.

The meta_stocks table has three columns: name, acronym and market_id that store the name, acronym and the market_id of a meta-stock. The market_id is a foreign key referencing the id column of the meta_markets table.

The users table has two columns, username and email, that store the username and email address of a user.

The meta_records table has five columns, timestamp, selling_price, meta_stock_id, buyer_id, and seller_id. The timestamp column stores the time and date of the operation, the selling_price column stores the selling price of the operation, the meta_stock_id column stores a reference to the meta-stock, and the buyer_id and seller_id columns store references to the buyer and seller (users that bought and sold the meta-stock). The meta_stock_id, buyer_id and seller_id columns are foreign keys referencing the id column of meta_stocks and users tables respectively.

Please note that the code above is for demonstration purposes only and may need to be adapted to fit your specific requirements and constraints.

# First let´s create the database
CREATE DATABASE tradetrainer;

# Use the database we just created
USE tradetrainer;

# Create a table for the meta markets
CREATE TABLE meta_markets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  acronym VARCHAR(255) NOT NULL
);

# Create a table for the stocks, each stock is selled in a market.
CREATE TABLE meta_stocks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  acronym VARCHAR(255) NOT NULL,
  market_id INT NOT NULL,
  FOREIGN KEY (market_id) REFERENCES meta_markets(id)
);

# Create a table for the users, users can operate in any stock
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

# Create a table to store the records
CREATE TABLE meta_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp DATETIME NOT NULL,
  selling_price DECIMAL(10,2) NOT NULL,
  meta_stock_id INT NOT NULL,
  buyer_id INT NOT NULL,
  seller_id INT NOT NULL,
  FOREIGN KEY (meta_stock_id) REFERENCES meta_stocks(id),
  FOREIGN KEY (buyer_id) REFERENCES users(id),
  FOREIGN KEY (seller_id) REFERENCES users(id)
);

Try it yourself

The file Trader_test database contains an implementation of this model, plus some sample data useful to build queries on timestamped data.

Data analysis queries

  1. List All Transactions for a Specific Stock

  2. Summary of Total Sales Per Stock, including the total number of transactions and the total sales (sum of all selling prices)

  3. Write a query to find out who are the most active buyers in terms of purchases made This query identifies the most active buyers in terms of the number of purchases made.

  4. Calculate the average selling price Per stock

  5. Write a query to obtain all transactions between specific Dates

  6. Find out which are the 5 stocks with Highest Transaction Volumes, defined as the number of transactions

Data analysis queries (Solved)

  1. List All Transactions for a Specific Stock This query retrieves all transactions for a particular stock, identified by its acronym. It includes the selling price and the names of the seller and buyer of the transaction

SELECT mr.timestamp, mr.selling_price, u1.username AS Buyer, u2.username AS Seller
FROM meta_records mr
LEFT JOIN meta_stocks ms ON mr.meta_stock_id = ms.id
LEFT JOIN users u1 ON mr.buyer_id = u1.id
LEFT JOIN users u2 ON mr.seller_id = u2.id
WHERE ms.acronym = 'STARK'; -- Replace 'STARK' with the desired stock acronym

  1. Summary of Total Sales Per Stock, including the total number of transactions and the total sales (sum of all selling prices)

This query provides a summary of total sales per stock, including the total number of transactions and the total amount sold. The function COUNT is used to count the total number of records and the function SUM the total sales, grouped by meta stock.

SELECT ms.name, COUNT(mr.id) AS NumberOfTransactions, SUM(mr.selling_price) AS TotalSales
FROM meta_stocks ms
JOIN meta_records mr ON ms.id = mr.meta_stock_id
GROUP BY ms.id;

  1. Write a query to find out who are the most active buyers in terms of purchases made This query identifies the most active buyers in terms of the number of purchases made.

SELECT u.username, COUNT(mr.id) AS NumberOfPurchases
FROM users u
JOIN meta_records mr ON u.id = mr.buyer_id
GROUP BY u.username
ORDER BY NumberOfPurchases DESC;

  1. Calculate the average selling price Per stock

This query calculates the average selling price of stocks per market. The market name is included to provide additional context

SELECT mm.name AS MarketName, ms.name as StockName, AVG(mr.selling_price) AS AverageSellingPrice
FROM meta_markets mm
JOIN meta_stocks ms ON mm.id = ms.market_id
JOIN meta_records mr ON ms.id = mr.meta_stock_id
GROUP BY ms.id;

  1. Write a query to obtain all transactions between specific Dates

This query fetches records of transactions that occurred within a specific date range.

SELECT ms.name AS StockName, mr.timestamp, mr.selling_price
FROM meta_records mr
JOIN meta_stocks ms ON mr.meta_stock_id = ms.id
WHERE mr.timestamp >= '2023-01-15' AND mr.timestamp <= '2023-01-16';  -- Specify the date range

The clause BETWEEN can be used to obain the same result

SELECT ms.name AS StockName, mr.timestamp, mr.selling_price
FROM meta_records mr
JOIN meta_stocks ms ON mr.meta_stock_id = ms.id
WHERE mr.timestamp BETWEEN '2023-01-15' AND '2023-01-16';  -- Specify the date range

  1. Find out which are the 5 stocks with Highest Transaction Volumes, defined as the number of transactions This query lists stocks with the highest transaction volumes.

SELECT ms.name, COUNT(mr.id) AS TransactionVolume
FROM meta_stocks ms
JOIN meta_records mr ON ms.id = mr.meta_stock_id
GROUP BY ms.name
ORDER BY TransactionVolume DESC
LIMIT 5; -- Shows top 5 stocks by volume