Queries.sql

-- Create and use database
-- CREATE DATABASE BankDWH;
-- GO

USE BankDWH;
GO

-- ==========================
-- Create Schemas
-- ==========================
CREATE SCHEMA STG;
GO

CREATE SCHEMA DWH;
GO

-- ==========================
-- Time Dimension
-- ==========================
-- Dropping if needed to avoid conflict from merge
IF OBJECT_ID('DWH.Time_Dim', 'U') IS NOT NULL DROP TABLE DWH.Time_Dim;
GO

CREATE TABLE DWH.Time_Dim (
    time_id INT IDENTITY(1,1) PRIMARY KEY,
    date DATE NOT NULL UNIQUE,
    day INT NOT NULL,
    month INT NOT NULL,
    quarter INT NOT NULL,
    year INT NOT NULL
);
CREATE NONCLUSTERED INDEX IX_Time_Dim_Date ON DWH.Time_Dim (date);

-- Sample Queries
SELECT TOP 10 * FROM DWH.Time_Dim ORDER BY date; -- Check 1990 dates
SELECT COUNT(*) FROM DWH.Time_Dim; -- Should return 14,975 rows (1990-01-01 to 2030-12-31)

-- ==========================
-- STAGING TABLES (STG)
-- ==========================

CREATE TABLE STG.Treasury_Product (  
    product_id          INT PRIMARY KEY,  
    account_id          INT,  
    Product_Name        VARCHAR(100),  
    product_type        VARCHAR(100),  
    interest_rate       FLOAT,  
    Minimum_Balance     FLOAT,  
    Maximum_Balance     FLOAT,  
    fees                FLOAT,  
    Availability        VARCHAR(100),  
    Terms               VARCHAR(255),  
    buy_date            DATETIME,  
    Last_Update         DATETIME  
);

CREATE TABLE STG.Payment (  
    Payment_ID        INT PRIMARY KEY,  
    account_id        INT,  
    branch_id         INT,         -- OLTP branch_id (not surrogate key)  
    Payment_date      DATETIME,  
    Payment_method    VARCHAR(150),  
    Payment_amount    FLOAT,  
    Last_Update       DATETIME  
);



CREATE TABLE STG.Branch (
    branch_id INT,
    branch_name NVARCHAR(100),
    zip_code NVARCHAR(20),
    location NVARCHAR(100), 
    update_date DATETIME
);

CREATE TABLE STG.Deposit (
    deposite_id INT,
    account_id INT,
    branch_id INT,
    deposit_date DATETIME,
    deposit_method VARCHAR(150),
    deposit_amount FLOAT
);

CREATE TABLE STG.Loan_Dim (
    loan_id INT NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    loan_type VARCHAR(100) NOT NULL,
    loan_amount FLOAT NOT NULL,
    src_update_date DATETIME NULL, 
    account_balance FLOAT NOT NULL,
    account_type VARCHAR(100) NOT NULL,
	branch_name NVARCHAR(50) NOT NULL ,
	create_timestamp DATETIME

);

CREATE TABLE STG.Account (
    account_id INT,
    customer_id INT,
    branch_id INT,
    creation_date DATETIME,
    account_balance FLOAT,
    account_type VARCHAR(100),
    customer_name NVARCHAR(100),
    customer_phone NVARCHAR(20),
    gender CHAR(1),
    src_update_date DATETIME
);

create table STG.Conf_Table
(
   tableName varchar(50),
   last_extract_date DATETIME
);

insert into STG.Conf_Table values
( 'Account' , '1990-01-01'),
( 'Loan' , '1990-01-01'),
( 'Branch' , '1990-01-01')
insert into STG.Conf_Table values
( 'Loan_Fact' , '1990-01-01')
insert into stg.Conf_Table values
('Treausry_Products', '1900-01-01')
insert into stg.Conf_Table values
('Payment', '1900-01-01')


select * from stg.Conf_Table 
go
CREATE TABLE STG.Loan_Fact (
    Staging_Id INT IDENTITY(1,1) PRIMARY KEY,
    loan_id INT NOT NULL, -- to look up Loan_Dim_Key
    branch_id INT NOT NULL, -- to calculate NumOfLoansPerBranch
    loan_type VARCHAR(100) NOT NULL, -- to calculate TotalAmountPerLoanType
    loan_amount FLOAT NOT NULL, -- to calculate TotalAmountPerLoanType
    start_date DATETIME NOT NULL, -- to look up date_id
    src_update_date DATETIME NULL, --  for incremental loading
    create_timestamp DATETIME DEFAULT GETDATE()
);

-- ==========================
-- DATA WAREHOUSE TABLES (DWH)
-- ==========================

-- Treasury Products Dimension recreate the table 
CREATE TABLE [DWH].[Treasury_Products_Dim] (
    [product_id] INT NOT NULL,
    [product_name] VARCHAR(100) NULL,
    [product_type] VARCHAR(100) NULL,
    [interest_rate] FLOAT NULL,
    [fees] FLOAT NULL,
    [availability] VARCHAR(100) NULL,
    [terms] VARCHAR(255) NULL,
    [src_update_date] DATETIME NULL,
    [start_date] DATETIME NOT NULL DEFAULT (GETDATE()),
    [end_date] DATETIME NULL,
    [is_current] BIT NOT NULL DEFAULT (1),
    [Treasury_Product_Key] INT IDENTITY(1,1) NOT NULL,
    PRIMARY KEY CLUSTERED ([Treasury_Product_Key] ASC)
);

-- Branch Dimension
CREATE TABLE DWH.Branch_Dim (
    branch_sk INT IDENTITY(1,1) PRIMARY KEY,  
    branch_id INT,                           
    branch_name NVARCHAR(100),
    zip_code NVARCHAR(20),
    location NVARCHAR(100),
    src_update_date DATETIME
);

-- Deposit Dimension
CREATE TABLE DWH.Deposite_Dim (
    deposite_sk INT IDENTITY(1,1) PRIMARY KEY,      
    account_id INT,                 
    branch_id INT,                     
    deposite_date DATETIME,
    deposite_method VARCHAR(150),
    account_type VARCHAR(50)          
);

 -- Account Dimension
CREATE TABLE DWH.Account_Dim (
    account_sk INT IDENTITY(1,1) PRIMARY KEY,
    account_id INT,
    customer_id INT,
    branch_id INT,
    creation_date DATETIME,
    account_balance FLOAT,
    account_type VARCHAR(100),
    customer_name NVARCHAR(100),
    customer_phone NVARCHAR(20),
    gender CHAR(1),
    effective_date DATETIME,
    expiry_date DATETIME,
    is_current BIT
);
--exec sp_rename 'dwh.account_dim.surrogate_key', 'account_sk', 'column' 


-- Deposit Fact Table
CREATE TABLE DWH.Deposite_Fact (
    deposite_fact_id INT IDENTITY(1,1) PRIMARY KEY,
    deposite_sk INT,     
    account_id INT,      
    branch_id INT,        
    date_id INT,        
    deposite_amount FLOAT,
    account_balance FLOAT,
    TotalDepositPerAccount FLOAT
);

-- Payment Fact Table
CREATE TABLE DWH.Payment_Fact (  
    payment_fact_id  INT IDENTITY(1,1) PRIMARY KEY,  
    account_dim_id   INT FOREIGN KEY REFERENCES DWH.Account_Dim(surrogate_key),  
    branch_id        INT FOREIGN KEY REFERENCES DWH.Branch_Dim(branch_sk),  
    time_id          INT FOREIGN KEY REFERENCES DWH.Time_Dim(time_id),  
    payment_method   VARCHAR(150),  
    payment_amount   FLOAT,  
    payment_count    INT
	TotalPaymentAmountPerBranch Float
);
--run if you didnt
--alter table dwh.payment_fact
--alter column payment_method varchar(150)

CREATE TABLE  DWH.Loan_Dim (
    Loan_Dim_Key INT IDENTITY(1,1) PRIMARY KEY,
    loan_id INT NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    loan_type VARCHAR(100) NOT NULL,
    loan_amount FLOAT NOT NULL,
    src_update_date DATETIME NULL, 
    account_balance FLOAT NOT NULL,
    account_type VARCHAR(100) NOT NULL,
	branch_name NVARCHAR(50) NOT NULL,
	is_last bit ,
	create_timestamp DATETIME,
	update_timestamp DATETIME

);
CREATE TABLE DWH.Loan_Fact (
    Loan_Fact_Key INT IDENTITY(1,1) PRIMARY KEY,
    Loan_Dim_Key INT NOT NULL,
    date_id INT NOT NULL,
    NumOfLoansPerBranch INT NOT NULL,
    TotalAmountPerLoanType FLOAT NOT NULL,
    CONSTRAINT FK_Loan_Fact_Loan_Dim FOREIGN KEY (Loan_Dim_Key)
        REFERENCES DWH.Loan_Dim(Loan_Dim_Key),
    CONSTRAINT FK_Loan_Fact_Time_Dim FOREIGN KEY (date_id)
        REFERENCES DWH.Time_Dim(time_id)
);




-- ==========================
-- Sample Queries and Testing
-- ==========================
-- Treasury Product tests
SELECT * FROM STG.Treasury_Product;
select * from dwh.Treasury_Products_Dim;
SELECT 
    product_id, product_name,
    COUNT(*) AS record_count
FROM 
    dwh.Treasury_Products_Dim
GROUP BY 
    product_id
HAVING 
    COUNT(*) > 1;

INSERT INTO STG.Payment (
    Payment_ID, account_id, branch_id, Payment_date, 
    Payment_method, Payment_amount, Last_Update
) VALUES (
    1001, 500, 10, '2023-10-01', 'Credit Card', 1500.00, GETDATE()
);


-- Sample updates
UPDATE DWH.Treasury_Products_Dim
SET fees = fees - 1
WHERE product_name = 'Bonds';

UPDATE STG.Treasury_Product  
SET fees = 200.00  
WHERE product_id = 2;


select * from STG.Account
select * from DWH.Account_Dim

-- Truncate (if needed)
-- TRUNCATE TABLE DWH.Treasury_Products_Dim;
-- TRUNCATE TABLE STG.Treasury_Product;
--truncate table DWH.Loan_Fact
--truncate table STG.Conf_Table
--truncate table STG.Account

select max(last_update), min(last_update) 
from 
BankDB.dbo.payment;