-
Notifications
You must be signed in to change notification settings - Fork 0
/
4.txt
92 lines (82 loc) · 1.47 KB
/
4.txt
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
88
89
90
91
92
-- Card Type Comparison
SELECT
CardType,
SUM(Amount) AS Total_Spending
FROM
Credit_Cards
GROUP BY
CardType;
-- Gender-Based Analysis
SELECT
Gender,
SUM(Amount) AS Total_Spending
FROM
Credit_Cards
GROUP BY
Gender;
-- Top Expenditure Categories
SELECT
ExpType,
SUM(Amount) AS Total_Spending
FROM
Credit_Cards
GROUP BY
ExpType
ORDER BY
Total_Spending DESC
LIMIT 10;
-- Customer Segmentation
SELECT
City,
CardType,
SUM(Amount) AS Total_Spending
FROM
Credit_Cards
GROUP BY
City,
CardType;
-- Customer Loyalty
SELECT
id,
COUNT(*) AS Transaction_Count,
SUM(Amount) AS Total_Spending
FROM
Credit_Cards
GROUP BY
id
HAVING
Transaction_Count >= 10; -- Assuming 10 transactions indicate loyalty
-- Refunds and Chargebacks
SELECT
ExpType,
COUNT(*) AS Chargeback_Count
FROM
Credit_Cards
WHERE
ExpType = 'Chargeback' OR ExpType = 'Refund'
GROUP BY
ExpType;
-- Outliers Detection
SELECT
*,
(Amount - AVG(Amount) OVER ()) / STDDEV(Amount) OVER () AS Z_Score
FROM
Credit_Cards
HAVING
ABS(Z_Score) > 3; -- You can adjust the threshold for outliers
-- Demographic Trends
SELECT
AgeGroup,
AVG(Amount) AS Avg_Spending
FROM
Credit_Cards
GROUP BY
AgeGroup;
-- Geographical Trends
SELECT
City,
SUM(Amount) AS Total_Spending
FROM
Credit_Cards
GROUP BY
City;