SQLize | PHPize | SQLtest

CREATE TABLE Customers ( CustomerID INT PRIMARY KEY IDENTITY(1,1), FullName NVARCHAR(200) NOT NULL, BirthDate DATE, Region NVARCHAR(100), Income DECIMAL(18,2), CreatedAt DATETIME DEFAULT GETDATE() ); CREATE TABLE Loans ( LoanID INT PRIMARY KEY IDENTITY(1,1), CustomerID INT NOT NULL, LoanAmount DECIMAL(18,2) NOT NULL, InterestRate DECIMAL(5,2) NOT NULL, TermMonths INT NOT NULL, StartDate DATE DEFAULT GETDATE(), Status NVARCHAR(50) NOT NULL CHECK (Status IN ('Active', 'Closed', 'Default')), CONSTRAINT FK_Loans_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); CREATE TABLE Payments ( PaymentID INT PRIMARY KEY IDENTITY(1,1), LoanID INT NOT NULL, PaymentDate DATE DEFAULT GETDATE(), Amount DECIMAL(18,2) NOT NULL, CONSTRAINT FK_Payments_Loans FOREIGN KEY (LoanID) REFERENCES Loans(LoanID) ); INSERT INTO Customers (FullName, BirthDate, Region, Income, CreatedAt) VALUES ('Іван Петренко', '1985-03-15', 'Київ', 25000.00, '2020-01-10'), ('Марія Сидорова', '1990-07-22', 'Львів', 18000.50, '2021-03-05'), ('Олександр Коваленко', '1978-11-30', 'Одеса', 35000.75, '2019-11-15'), ('Наталія Шевченко', '1995-05-18', 'Харків', 42000.00, '2022-02-20'), ('Віктор Мельник', '1982-09-12', 'Дніпро', 31000.25, '2021-07-08'); INSERT INTO Loans (CustomerID, LoanAmount, InterestRate, TermMonths, StartDate, Status) VALUES (1, 50000.00, 15.50, 24, '2023-01-15', 'Active'), (1, 30000.00, 12.75, 12, '2023-03-10', 'Closed'), (2, 75000.00, 14.25, 36, '2023-02-20', 'Active'), (3, 100000.00, 10.00, 48, '2022-11-05', 'Active'), (4, 25000.00, 18.00, 6, '2023-04-01', 'Default'), (5, 60000.00, 13.50, 24, '2023-01-30', 'Active'); INSERT INTO Payments (LoanID, PaymentDate, Amount) VALUES (1, '2023-02-15', 2500.00), (1, '2023-03-15', 2500.00), (2, '2023-04-10', 2700.00), (3, '2023-03-20', 3200.50), (3, '2023-04-20', 3200.50), (4, '2022-12-05', 2300.75), (4, '2023-01-05', 2300.75), (6, '2023-02-28', 2900.00), (6, '2023-03-31', 2900.00); SELECT MONTH(L.StartDate), COUNT(L.LoanID) AS 'Кількість нових кредитів' FROM Loans L LEFT JOIN Payments P ON L.LoanID = P.PaymentID WHERE L.StartDate >= DATEADD(YEAR, -1, GETDATE()); GROUP BY MONTH(L.StartDate);