Variable vs Fixed Rates

#Credit Karma

Write a query that returns binary description of rate type per loan_id. The results should have one row per loan_id and two columns: for fixed and variable type.

table name: submissions

Solution:

select loan_id, 
(case when rate_type='fixed' then 1 else 0 end) as fixed,
(case when rate_type='variable' then 1 else 0 end) as variable
from submissions

Output:

SQL Script:

USE [StrataScratch]
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