CREATING VIEWS
1. View for Users with Long Trades:
Query Title: View for Users with Long Trades
Description: This view lists all users who have placed long trades.
CREATE VIEW Users_with_Long_Trades AS
SELECT U.First_Name, U.Last_Name, T.Trade_ID
FROM Users U
INNER JOIN Trade T ON U.User_ID = T.User_ID
WHERE [Link] = 'Long';
2. View for Stocks Traded Most Often:
Query Title: View for Most Often Traded Stocks
Description: This view lists stocks that have been traded the most time.
CREATE VIEW Most_Traded_Stocks AS
SELECT S.Stock_Symbol, S.Company_Name, COUNT(T.Trade_ID) as
Trade_Count
FROM Stock S
INNER JOIN Trade T ON S.Stock_Symbol = T.Stock_Symbol
GROUP BY S.Stock_Symbol, S.Company_Name
ORDER BY Trade_Count DESC;
Advance Queries:
1. Query for Average ROI by User:
Query Title: Average ROI by User
Description: This query calculates the average ROI for each user.
SELECT U.First_Name, U.Last_Name, AVG([Link]) AS "Average ROI"
FROM Users U
INNER JOIN Performance_Metric P ON U.User_ID = P.User_ID
GROUP BY U.First_Name, U.Last_Name;
2. Query for Trades with Attached Journal Notes:
Query Title: Trades with Attached Journal Notes
Description: This query lists all trades that have associated journal notes
SELECT T.Trade_ID, [Link]
FROM Trade T
INNER JOIN Journal_Entry J ON T.Trade_ID = J.Trade_ID
WHERE [Link] IS NOT NULL;
3. Query for Users Without Performance Metrics:
Query Title: Users Without Performance Metrics
Description: This query lists all users who don’t have performance metrics
SELECT U.First_Name, U.Last_Name
FROM Users U
LEFT JOIN Performance_Metric P ON U.User_ID = P.User_ID
WHERE P.Metrics_ID IS NULL;
4. Query for Most Traded Stock by User:
Query Title: Most Traded Stock by User
Description: This query determines which stock each user has traded the
most
WITH StockTradeCounts AS (
SELECT U.User_ID, T.Stock_Symbol, COUNT(T.Trade_ID) AS Trade_Count
FROM Users U
INNER JOIN Trade T ON U.User_ID = T.User_ID
GROUP BY U.User_ID, T.Stock_Symbol
SELECT U.First_Name, U.Last_Name, S.Company_Name
FROM StockTradeCounts STC
INNER JOIN Users U ON STC.User_ID = U.User_ID
INNER JOIN Stock S ON STC.Stock_Symbol = S.Stock_Symbol
WHERE STC.Trade_Count = (SELECT MAX(Trade_Count) FROM
StockTradeCounts WHERE User_ID = STC.User_ID);
5. Query to identify Users and their high ROI Trades:
Query Title: Users and Their High ROI Trades
Description: This query lists users, their trades, and performance metrics
for trades with an ROI greater than 0.1.
SELECT U.First_Name, U.Last_Name, T.Trade_ID, [Link]
FROM Users U
JOIN Trade T ON U.User_ID = T.User_ID
JOIN Performance_Metric P ON U.User_ID = P.User_ID
WHERE [Link] > 0.1;
6. Query to find trades with volume greater than 50 but less than
200.
Query Title: Filter Trades By Volume Range
Description: This query lists all trades where the volume is greater
than 50 but less than 200.
SELECT *
FROM TRADE
WHERE Volume > 50 AND Volume < 200;
7. Query to list all the stocks where the sector is ‘Technology’
Query Title: List Technology Stocks
Description: This query lists all the stocks in the 'Technology'
sector.
SELECT *
FROM STOCK
WHERE Sector = 'Technology';