-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathMostLikelyChurnAggregate.sql
More file actions
87 lines (82 loc) · 4.01 KB
/
MostLikelyChurnAggregate.sql
File metadata and controls
87 lines (82 loc) · 4.01 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
/****** Script for SelectTopNRows command from SSMS ******/
SELECT [gender] as Gender
,count(CASE WHEN age>= 30 AND age < 40 THEN 1 END) AS [Ages: 30 - 39]
,count(CASE WHEN age>= 40 AND age < 50 THEN 1 END) AS [Ages: 40 - 49]
,count(CASE WHEN age>= 50 AND age < 60 THEN 1 END) AS [Ages: 50 - 59]
,count(CASE WHEN age>= 60 AND age < 75 THEN 1 END) AS [Ages: 60 - 75]
FROM [Course Project BI].[dbo].[Most Likely Leave 100]
where [gender] like 'Female'
group by gender
union
select top 1 [gender],
round((select avg(balance) as avgBal_30_40 from [Most Likely Leave 100]
where age>= 30 AND age < 40
and gender like 'Female'),2) [Age 30 - 39],
round((select avg(balance) as avgBal_40_50 from [Most Likely Leave 100]
where age>= 40 AND age < 50
and gender like 'Female'),2) [Age 40 - 49],
round((select avg(balance) as avgBal_50_60 from [Most Likely Leave 100]
where age>= 50 AND age < 60
and gender like 'Female'),2) [Age 50 - 59],
round((select avg(balance) as avgBal_60_75 from [Most Likely Leave 100]
where age>= 60 AND age < 75
and gender like 'Female'),2) [Age 60 - 75]
FROM [Course Project BI].[dbo].[Most Likely Leave 100] t
where gender like 'Female'
union
select top 1 [gender],
round((select avg([credit score]) as avgCred_30_40 from [Most Likely Leave 100]
where age>= 30 AND age < 40
and gender like 'Female'),0) [Age 30 - 39],
round((select avg([credit score]) as avgCred_40_50 from [Most Likely Leave 100]
where age>= 40 AND age < 50
and gender like 'Female'),0) [Age 40 - 49],
round((select avg([credit score]) as avgCred_50_60 from [Most Likely Leave 100]
where age>= 50 AND age < 60
and gender like 'Female'),0) [Age 50 - 59],
round((select avg([credit score]) as avgCred_60_75 from [Most Likely Leave 100]
where age>= 60 AND age < 75
and gender like 'Female'),0) [Age 60 - 75]
FROM [Course Project BI].[dbo].[Most Likely Leave 100] t
where gender like 'Female'
union
SELECT [gender] as Gender
,count(CASE WHEN age>= 30 AND age < 40 THEN 1 END) AS [Ages: 30 - 39]
,count(CASE WHEN age>= 40 AND age < 50 THEN 1 END) AS [Ages: 40 - 49]
,count(CASE WHEN age>= 50 AND age < 60 THEN 1 END) AS [Ages: 50 - 59]
,count(CASE WHEN age>= 60 AND age < 75 THEN 1 END) AS [Ages: 60 - 75]
FROM [Course Project BI].[dbo].[Most Likely Leave 100]
where [gender] like 'Male'
group by gender
union
select top 1 [gender],
round((select avg(balance) as avgBal_30_40 from [Most Likely Leave 100]
where age>= 30 AND age < 40
and gender like 'Male'),2) [Age 30 - 39],
round((select avg(balance) as avgBal_40_50 from [Most Likely Leave 100]
where age>= 40 AND age < 50
and gender like 'Male'),2) [Age 40 - 49],
round((select avg(balance) as avgBal_50_60 from [Most Likely Leave 100]
where age>= 50 AND age < 60
and gender like 'Male'),2) [Age 50 - 59],
round((select avg(balance) as avgBal_60_75 from [Most Likely Leave 100]
where age>= 60 AND age < 75
and gender like 'Male'),2) [Age 60 - 75]
FROM [Course Project BI].[dbo].[Most Likely Leave 100] t
where gender like 'Male'
union
select top 1 [gender],
round((select avg([credit score]) as avgCred_30_40 from [Most Likely Leave 100]
where age>= 30 AND age < 40
and gender like 'Male'),0) [Age 30 - 39],
round((select avg([credit score]) as avgCred_40_50 from [Most Likely Leave 100]
where age>= 40 AND age < 50
and gender like 'Male'),0) [Age 40 - 49],
round((select avg([credit score]) as avgCred_50_60 from [Most Likely Leave 100]
where age>= 50 AND age < 60
and gender like 'Male'),0) [Age 50 - 59],
round((select avg([credit score]) as avgCred_60_75 from [Most Likely Leave 100]
where age>= 60 AND age < 75
and gender like 'Male'),0) [Age 60 - 75]
FROM [Course Project BI].[dbo].[Most Likely Leave 100] t
where gender like 'Male'