Share of Loan Balance

#2001

Write a query that returns the rate_type, loan_id, loan balance , and a column that shows with what percentage the loan's balance contributes to the total balance among the loans of the same rate type.

table name: submissions


Solution:

select loan_id, rate_type,balance,round(1.00*balance*100/
 sum(balance) OVER(PARTITION BY rate_type),2) AS balance_share
from submissions
order by rate_type

Output:


SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[submissions]    Script Date: 12-01-2024 15:57:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[submissions](
	[id] [int] NULL,
	[balance] [float] NULL,
	[interest_rate] [float] NULL,
	[rate_type] [varchar](50) NULL,
	[loan_id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[submissions] ([id], [balance], [interest_rate], [rate_type], [loan_id]) VALUES (1, 5229.12, 8.75, N'variable', 2)
GO
INSERT [dbo].[submissions] ([id], [balance], [interest_rate], [rate_type], [loan_id]) VALUES (2, 12727.52, 11.37, N'fixed', 4)
GO
INSERT [dbo].[submissions] ([id], [balance], [interest_rate], [rate_type], [loan_id]) VALUES (3, 14996.58, 8.25, N'fixed', 9)
GO
INSERT [dbo].[submissions] ([id], [balance], [interest_rate], [rate_type], [loan_id]) VALUES (4, 21149, 4.75, N'variable', 7)
GO
INSERT [dbo].[submissions] ([id], [balance], [interest_rate], [rate_type], [loan_id]) VALUES (5, 14379, 3.75, N'variable', 5)
GO
INSERT [dbo].[submissions] ([id], [balance], [interest_rate], [rate_type], [loan_id]) VALUES (6, 6221.12, 6.75, N'variable', 11)
GO

Comments (0)
Questions List