-- CREATE TABLE Customers (
-- CustomerID SERIAL PRIMARY KEY,
-- FirstName VARCHAR(100),
-- LastName VARCHAR(100),
-- Email VARCHAR(100)
-- );
-- CREATE TABLE AccountTypes (
-- AccountTypeID SERIAL PRIMARY KEY,
-- AccountTypeName VARCHAR(100)
-- );
-- CREATE TABLE Accounts (
-- AccountID SERIAL PRIMARY KEY,
-- CustomerID INT REFERENCES Customers(CustomerID),
-- AccountTypeID INT REFERENCES AccountTypes(AccountTypeID),
-- Balance DECIMAL(18, 2)
-- );
-- CREATE TABLE Transactions (
-- TransactionID SERIAL PRIMARY KEY,
-- AccountID INT REFERENCES Accounts(AccountID),
-- TransactionDate DATE,
-- Amount DECIMAL(18, 2)
-- );
-- --Inserting some sample data
-- INSERT INTO Customers(FirstName, LastName, Email) VALUES
-- ('John', 'Doe', 'john.doe@example.com'),
-- ('Jane', 'Doe', 'jane.doe@example.com'),
-- ('Jim', 'Smith', 'jim.smith@example.com');
-- INSERT INTO AccountTypes(AccountTypeName) VALUES
-- ('Savings'),
-- ('Current');
-- INSERT INTO Accounts(CustomerID, AccountTypeID, Balance) VALUES
-- (1, 1, 10000.00),
-- (1, 2, 5000.00),
-- (2, 1, 20000.00),
-- (2, 2, 10000.00),
-- (3, 1, 15000.00),
-- (3, 2, 7500.00);
-- INSERT INTO Transactions(AccountID, TransactionDate, Amount) VALUES
-- (1, '2023-01-01', 500.00),
-- (1, '2023-01-15', -200.00),
-- (1, '2023-02-01', 500.00),
-- (1, '2023-02-15', -200.00),
-- (2, '2023-01-01', 200.00),
-- (2, '2023-01-15', -100.00),
-- (2, '2023-02-01', 200.00),
-- (2, '2023-02-15', -100.00),
-- (3, '2023-01-01', 1000.00),
-- (3, '2023-01-15', -500.00),
-- (3, '2023-02-01', 1000.00),
-- (3, '2023-02-15', -500.00);
-- CREATE TABLE AccountStatus (
-- AccountStatusID SERIAL PRIMARY KEY,
-- AccountID INT REFERENCES Accounts(AccountID),
-- Status VARCHAR(50),
-- StatusDate DATE
-- );
-- --Inserting some additional data
-- INSERT INTO Customers(FirstName, LastName, Email) VALUES
-- ('Jack', 'Johnson', 'jack.johnson@example.com'),
-- ('Jill', 'Johnson', 'jill.johnson@example.com'),
-- ('Tom', 'Cruise', 'tom.cruise@example.com');
-- INSERT INTO AccountTypes(AccountTypeName) VALUES
-- ('Investment'),
-- ('Loan');
-- INSERT INTO Accounts(CustomerID, AccountTypeID, Balance) VALUES
-- (4, 1, 30000.00),
-- (4, 3, 15000.00),
-- (5, 1, 25000.00),
-- (5, 4, -10000.00),
-- (6, 2, 20000.00),
-- (6, 3, 12000.00);
-- INSERT INTO Transactions(AccountID, TransactionDate, Amount) VALUES
-- (4, '2023-03-01', 1500.00),
-- (4, '2023-03-15', -700.00),
-- (4, '2023-04-01', 1500.00),
-- (4, '2023-04-15', -700.00),
-- (5, '2023-03-01', 1250.00),
-- (5, '2023-03-15', -600.00),
-- (5, '2023-04-01', 1250.00),
-- (5, '2023-04-15', -600.00),
-- (6, '2023-03-01', 2000.00),
-- (6, '2023-03-15', -1000.00),
-- (6, '2023-04-01', 2000.00),
-- (6, '2023-04-15', -1000.00);
-- INSERT INTO AccountStatus(AccountID, Status, StatusDate) VALUES
-- (1, 'Active', '2023-01-01'),
-- (1, 'Suspended', '2023-02-01'),
-- (1, 'Active', '2023-03-01'),
-- (2, 'Active', '2023-01-01'),
-- (2, 'Closed', '2023-04-01'),
-- (3, 'Active', '2023-01-01'),
-- (4, 'Active', '2023-02-01'),
-- (5, 'Active', '2023-02-01'),
-- (6, 'Active', '2023-03-01');
-- Case study questions
-- 1. Find the total number of transactions each customer made in 2023.
SELECT cust.FirstName, cust.LastName, COUNT(trans.TransactionID)'total number of transactions'
FROM Customers cust
LEFT JOIN Accounts acct ON cust.CustomerID = acct.CustomerID
LEFT JOIN Transactions trans ON acct.AccountID = trans.AccountID
GROUP BY cust.FirstName, cust.LastName;
-- 2. Calculate the total income (positive transactions) for each customer in 2023.
SELECT * FROM Transactions;
--3 Find the month with the highest total expenses (negative transactions) in 2023.
--4. Calculate the total income, total expenses, and net amount (income - expenses) for each account type for the year 2023.
--5. Find the customer with the highest average monthly net transactions (income - expenses) in 2023.
--6. For each month in 2023, identify the customer with the highest net transactions (income - expenses) and rank them according to this value.
Telegram: DB developers channel