CREATE TABLE Loans (
loan_nbr INT NOT NULL PRIMARY KEY,
loan_date DATETIME NOT NULL,
loan_amount DECIMAL(12, 2) NOT NULL
DEFAULT 0.0,
customer_nbr INT NOT NULL,
loan_type CHAR(1) NOT NULL
DEFAULT 'P'
CHECK (loan_type IN ('P', -- personal
'B')) -- business
);
INSERT INTO Loans
VALUES (1, '20080801', 12000.00, 2, 'P'),
(2, '20080805', 15700.00, 1, 'B'),
(3, '20080610', 12000.00, 3, 'P'),
(4, '20080401', 5000.00, 1, 'P'),
(5, '20080715', 25000.00, 4, 'B'),
(6, '20080610', 25000.00, 5, 'P'),
(7, '20080501', 1000.00, 6, 'P'),
(8, '20080810', 6000.00, 7, 'B'),
(9, '20080815', 2000.00, 8, 'B'),
(10, '20080815', 1000.00, 9, 'P'),
(11, '20080715', 5500.00, 10, 'P'),
(12, '20080615', 1000.00, 11, 'B'),
(13, '20080820', 6000.00, 12, 'P'),
(14, '20080510', 28000.00, 6, 'B'),
(15, '20080815', 2000.00, 10, 'P'),
(16, '20080810', 1500.00, 8, 'P'),
(17, '20080817', 10000.00, 10, 'B'),
(18, '20080816', 2500.00, 9, 'P');
-- Aggregate window functions with the OVER clause
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS total_customer_loans,
AVG(loan_amount) OVER(PARTITION BY customer_nbr) AS avg_customer_loan_amt,
MAX(loan_amount) OVER(PARTITION BY customer_nbr) AS max_customer_loan_amt,
MIN(loan_amount) OVER(PARTITION BY customer_nbr) AS min_customer_loan_amt,
COUNT(*) OVER(PARTITION BY customer_nbr) AS count_customer_loans
FROM Loans;
-- Calculate percent for current loan based on total customer loans
-- and total of all loans
SELECT loan_nbr, loan_type, loan_amount, customer_nbr,
loan_amount /
SUM(loan_amount) OVER(PARTITION BY customer_nbr) AS percent_of_customer_loans,
loan_amount /
SUM(loan_amount) OVER() AS percent_of_all_loans
FROM Loans;
-- Get customers (and all their loans) with more than 2 loans
SELECT customer_nbr, loan_nbr, loan_amount, cnt
FROM (SELECT customer_nbr, loan_nbr, loan_amount,
COUNT(*) OVER(PARTITION BY customer_nbr) AS cnt
FROM Loans) AS L
WHERE cnt >= 2;
Labels: t-sql programming