Views_Reports.sql

CREATE OR REPLACE VIEW VW_STOCK_STATISTICS AS
SELECT a.CUSTOMER_ID,c.CUSTOMER_NAME, STOCK_PURCHASE_COST, STOCK_SALE_COST FROM
(SELECT CUSTOMER_ID,(SUM(UNIT_STOCK_PRICE * Number_of_UNITS)) AS STOCK_PURCHASE_COST FROM STOCK_TRANSACTIONS
WHERE STOCK_TRANSACTION_TYPE = 'B' GROUP BY CUSTOMER_ID) A
INNER JOIN
(SELECT CUSTOMER_ID,(SUM(UNIT_STOCK_PRICE * Number_of_UNITS)) AS STOCK_SALE_COST FROM STOCK_TRANSACTIONS
WHERE STOCK_TRANSACTION_TYPE = 'S' GROUP BY CUSTOMER_ID) B
on a.CUSTOMER_ID = b.CUSTOMER_ID
INNER JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER_DETAILS) C
ON A.CUSTOMER_ID = C.CUSTOMER_ID;

select * from VW_STOCK_STATISTICS;

------------------------mutual funds-------------------------------------------------

CREATE OR REPLACE VIEW VW_MUTUAL_STATISTICS AS
SELECT a.CUSTOMER_ID,c.CUSTOMER_NAME, MUTUAL_PURCHASE_COST, MUTUAL_SALE_COST FROM
(SELECT CUSTOMER_ID,(SUM(MUTUAL_NAV_PRICE * MUTUAL_UNITS)) AS MUTUAL_PURCHASE_COST FROM MUTUAL_FUND_TRANSACTIONS
WHERE MUTUAL_TRANSACTION_TYPE = 'B' GROUP BY CUSTOMER_ID) a
INNER JOIN
(SELECT CUSTOMER_ID,(SUM(MUTUAL_NAV_PRICE * MUTUAL_UNITS)) AS MUTUAL_SALE_COST FROM MUTUAL_FUND_TRANSACTIONS
WHERE MUTUAL_TRANSACTION_TYPE = 'S' GROUP BY CUSTOMER_ID) b
on a.CUSTOMER_ID = b.CUSTOMER_ID
INNER JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER_DETAILS) C
ON A.CUSTOMER_ID = C.CUSTOMER_ID;

select * from VW_MUTUAL_STATISTICS;

----------------------------------Cryptocurrency--------------------------

CREATE OR REPLACE VIEW VW_CRYPTO_STATISTICS AS
SELECT a.CUSTOMER_ID,c.CUSTOMER_NAME, CRYPTO_PURCHASE_COST, CRYPTO_SALE_COST FROM
(SELECT CUSTOMER_ID,(SUM(CRYPTO_PRICE * CRYPTO_UNITS)) AS CRYPTO_PURCHASE_COST FROM CRYPTO_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'B' GROUP BY CUSTOMER_ID) a
INNER JOIN
(SELECT customer_id,(SUM(CRYPTO_PRICE * CRYPTO_UNITS)) AS CRYPTO_SALE_COST FROM CRYPTO_TRANSACTIONS
WHERE TRANSACTION_TYPE = 'S' GROUP BY CUSTOMER_ID) b
on a.CUSTOMER_ID = b.CUSTOMER_ID
INNER JOIN
(SELECT CUSTOMER_ID,  CUSTOMER_NAME FROM CUSTOMER_DETAILS) C
ON A.CUSTOMER_ID = C.CUSTOMER_ID;



select * from VW_CRYPTO_STATISTICS;

---------------------------------------------------foreign Exchange----------------------------------------------------

CREATE OR REPLACE VIEW VW_CURRENCY_STATISTICS AS
SELECT a.CUSTOMER_ID,c.CUSTOMER_NAME, CURRENCY_PURCHASE_COST, CURRENCY_SALE_COST FROM
(SELECT CUSTOMER_ID,(SUM(MONEY_INVESTMENT_VALUE * PURCHASED_CURRENCY_VALUE)) AS CURRENCY_PURCHASE_COST FROM FOREIGN_EXCHANGE_TRANSACTIONS
WHERE CURRENCY_TRANSACTION_TYPE = 'B' GROUP BY CUSTOMER_ID) a
INNER JOIN
(SELECT customer_id,(SUM(MONEY_INVESTMENT_VALUE * PURCHASED_CURRENCY_VALUE)) AS CURRENCY_SALE_COST FROM FOREIGN_EXCHANGE_TRANSACTIONS
WHERE CURRENCY_TRANSACTION_TYPE = 'S' GROUP BY CUSTOMER_ID) b
on a.CUSTOMER_ID = b.CUSTOMER_ID
INNER JOIN
(SELECT CUSTOMER_ID,  CUSTOMER_NAME FROM CUSTOMER_DETAILS) C
ON A.CUSTOMER_ID = C.CUSTOMER_ID;

select * from VW_CURRENCY_STATISTICS;

-------------------------------------------Combined queries--------------------------------------------------------

CREATE OR REPLACE VIEW VW_FINAL_INVESTMENT AS 
SELECT A.CUSTOMER_ID, A.CUSTOMER_NAME, B.STOCK_RESULT, C.MUTUAL_RESULT, D.CRYPTO_RESULT, E.CURRENCY_RESULT FROM
(SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER_DETAILS) A
LEFT JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME, STOCK_SALE_COST - STOCK_PURCHASE_COST AS STOCK_RESULT FROM VW_STOCK_STATISTICS) B
ON A.CUSTOMER_ID = B.CUSTOMER_ID
LEFT JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME, MUTUAL_SALE_COST - MUTUAL_PURCHASE_COST AS MUTUAL_RESULT FROM VW_MUTUAL_STATISTICS) C
ON A.CUSTOMER_ID = C.CUSTOMER_ID
LEFT JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME, CRYPTO_SALE_COST - CRYPTO_PURCHASE_COST AS CRYPTO_RESULT FROM VW_CRYPTO_STATISTICS) D
ON A.CUSTOMER_ID = D.CUSTOMER_ID
LEFT JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME, CURRENCY_SALE_COST - CURRENCY_PURCHASE_COST AS CURRENCY_RESULT FROM VW_CURRENCY_STATISTICS) E
ON A.CUSTOMER_ID = E.CUSTOMER_ID;


SELECT * FROM VW_FINAL_INVESTMENT;


------------------------predicted profit or loss---------------------------------

CREATE OR REPLACE VIEW VW_STOCK_STATISTICS AS
SELECT a.CUSTOMER_ID,c.CUSTOMER_NAME, PURCHASE_COST, PREDICTED_SALE_COST FROM
(SELECT CUSTOMER_ID,(SUM(UNIT_STOCK_PRICE * Number_of_UNITS)) AS PURCHASE_COST FROM STOCK_TRANSACTIONS
WHERE STOCK_TRANSACTION_TYPE = 'B' GROUP BY CUSTOMER_ID) A
INNER JOIN
(SELECT CUSTOMER_ID,(SUM(STOCK_LATEST_PRICE * Number_of_UNITS)) AS PREDICTED_SALE_COST FROM STOCK_TRANSACTIONS
WHERE STOCK_TRANSACTION_TYPE = 'S' GROUP BY CUSTOMER_ID) B
on a.CUSTOMER_ID = b.CUSTOMER_ID
INNER JOIN
(SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER_DETAILS) C
ON A.CUSTOMER_ID = C.CUSTOMER_ID;


-------------------------------------------CURRENT PRICE CALCULATION VIEW-------------------------------------------



SELECT * FROM STOCK_TRANSACTIONS;

-------------------------------------------Report-------------------------------------------------

SELECT CUSTOMER_ID, CUSTOMER_NAME, STOCK_SALE_COST - STOCK_PURCHASE_COST AS STOCK_INVESTMENT_RESULT
FROM
VW_STOCK_STATISTICS;

SELECT CUSTOMER_ID, CUSTOMER_NAME, MUTUAL_SALE_COST - MUTUAL_PURCHASE_COST AS MUTUAL_FUND_INVESTMENT_RESULT
FROM
VW_MUTUAL_STATISTICS;

SELECT CUSTOMER_ID, CUSTOMER_NAME, CRYPTO_SALE_COST - CRYPTO_PURCHASE_COST AS CRYPTO_INVESTMENT_RESULT
FROM
VW_CRYPTO_STATISTICS;

SELECT CUSTOMER_ID, CUSTOMER_NAME, CURRENCY_SALE_COST - CURRENCY_PURCHASE_COST AS CURRENCY_INVESTMENT_RESULT
FROM
VW_CURRENCY_STATISTICS;

SELECT CUSTOMER_ID, CUSTOMER_NAME, (NVL(STOCK_RESULT,0) + NVL(MUTUAL_RESULT,0) + NVL(CRYPTO_RESULT,0) + NVL(CURRENCY_RESULT,0)) AS TOTAL_RESULT
FROM
VW_FINAL_INVESTMENT;


------------------------------REPORT BASED ON CUSTOMER FEEDBACK-------------------------------------


SELECT ASSET_NAME, AVG(ASSET_RATING) FROM FEEDBACK GROUP BY ASSET_NAME ;


-----------------------------------LAST 6 MONTHS TRANSACTIONS----------------------------------------

SELECT ASSET_CATAGORY, round(avg(ASSET_RATING),2) FROM FEEDBACK GROUP BY ASSET_CATAGORY;



--------------------------------------MAX ASSET PURCHASED---------------------------------------------


SELECT STOCK_TRANSACTIONS.STOCK_ID, STOCK_DETAILS.STOCK_NAME, COUNT(STOCK_DETAILS.STOCK_NAME)
FROM STOCK_TRANSACTIONS, STOCK_DETAILS
WHERE STOCK_TRANSACTIONS.STOCK_ID = STOCK_DETAILS.STOCK_ID
GROUP BY STOCK_TRANSACTIONS.STOCK_ID, STOCK_DETAILS.STOCK_NAME 
ORDER BY COUNT(STOCK_DETAILS.STOCK_NAME) DESC;

----------------------------------------MIN ASSET PURCHASED------------------------------------------

SELECT STOCK_TRANSACTIONS.STOCK_ID, STOCK_DETAILS.STOCK_NAME, COUNT(STOCK_DETAILS.STOCK_NAME)
FROM STOCK_TRANSACTIONS, STOCK_DETAILS
WHERE STOCK_TRANSACTIONS.STOCK_ID = STOCK_DETAILS.STOCK_ID
GROUP BY STOCK_TRANSACTIONS.STOCK_ID, STOCK_DETAILS.STOCK_NAME 
ORDER BY COUNT(STOCK_DETAILS.STOCK_NAME) ASC;