Transaction Analysis

1-- 1. Daily transaction volume & value
2SELECT 
3  transaction_date,
4  COUNT(*) AS txn_count,
5  SUM(amount) AS total_value,
6  ROUND(AVG(amount), 0) AS avg_txn_value,
7  COUNT(DISTINCT customer_id) AS unique_customers
8FROM fact_transaction
9GROUP BY transaction_date
10ORDER BY transaction_date;

1-- 2. Transaction mix theo loại
2SELECT 
3  transaction_type,
4  COUNT(*) AS txn_count,
5  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct_count,
6  SUM(amount) AS total_value,
7  ROUND(SUM(amount) * 100.0 / SUM(SUM(amount)) OVER(), 1) AS pct_value,
8  ROUND(AVG(amount), 0) AS avg_value,
9  ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER (PARTITION BY transaction_type), 0) AS median_value
10FROM fact_transaction
11GROUP BY transaction_type
12ORDER BY txn_count DESC;

1-- 3. Giao dịch theo ngày trong tuần
2SELECT 
3  DATENAME(WEEKDAY, transaction_date) AS day_name,
4  DATEPART(WEEKDAY, transaction_date) AS day_number,
5  COUNT(*) AS txn_count,
6  ROUND(AVG(amount), 0) AS avg_amount,
7  COUNT(DISTINCT customer_id) AS unique_customers
8FROM fact_transaction
9GROUP BY DATENAME(WEEKDAY, transaction_date), DATEPART(WEEKDAY, transaction_date)
10ORDER BY day_number;

1-- 4. Channel analysis
2SELECT 
3  channel,
4  COUNT(*) AS txn_count,
5  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1) AS pct_count,
6  SUM(amount) AS total_value,
7  ROUND(AVG(amount), 0) AS avg_value,
8  COUNT(DISTINCT customer_id) AS unique_customers
9FROM fact_transaction
10GROUP BY channel
11ORDER BY txn_count DESC;

1-- 5. Digital Adoption theo tháng (trend)
2SELECT 
3  FORMAT(transaction_date, 'yyyy-MM') AS month,
4  COUNT(*) AS total_txn,
5  SUM(CASE WHEN channel IN ('Mobile', 'Online') THEN 1 ELSE 0 END) AS digital_txn,
6  ROUND(
7      SUM(CASE WHEN channel IN ('Mobile', 'Online') THEN 1.0 ELSE 0 END) 
8      / COUNT(*) * 100, 1
9  ) AS digital_adoption_pct
10FROM fact_transaction
11GROUP BY FORMAT(transaction_date, 'yyyy-MM')
12ORDER BY month;

1-- 6. Channel preference theo income segment
2SELECT 
3  CASE 
4      WHEN c.monthly_income >= 50000000 THEN 'Priority'
5      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
6      ELSE 'Mass'
7  END AS segment,
8  t.channel,
9  COUNT(*) AS txn_count,
10  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (
11      PARTITION BY CASE 
12          WHEN c.monthly_income >= 50000000 THEN 'Priority'
13          WHEN c.monthly_income >= 20000000 THEN 'Affluent'
14          ELSE 'Mass'
15      END
16  ), 1) AS pct_within_segment
17FROM fact_transaction t
18JOIN dim_customer c ON t.customer_id = c.customer_id
19GROUP BY 
20  CASE 
21      WHEN c.monthly_income >= 50000000 THEN 'Priority'
22      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
23      ELSE 'Mass'
24  END,
25  t.channel
26ORDER BY segment, txn_count DESC;

1-- 7. Anomaly detection — giao dịch ngoài 3 sigma
2WITH customer_stats AS (
3  SELECT 
4      customer_id,
5      AVG(amount) AS avg_amount,
6      STDEV(amount) AS std_amount,
7      COUNT(*) AS txn_count
8  FROM fact_transaction
9  GROUP BY customer_id
10  HAVING COUNT(*) >= 10
11)
12SELECT 
13  t.transaction_id,
14  t.customer_id,
15  t.transaction_date,
16  t.transaction_type,
17  t.amount,
18  t.channel,
19  cs.avg_amount,
20  cs.std_amount,
21  ROUND((t.amount - cs.avg_amount) / NULLIF(cs.std_amount, 0), 2) AS z_score
22FROM fact_transaction t
23JOIN customer_stats cs ON t.customer_id = cs.customer_id
24WHERE ABS((t.amount - cs.avg_amount) / NULLIF(cs.std_amount, 0)) > 3
25ORDER BY ABS((t.amount - cs.avg_amount) / NULLIF(cs.std_amount, 0)) DESC;

1-- 8. Average balance theo customer segment
2SELECT 
3  CASE 
4      WHEN c.monthly_income >= 50000000 THEN 'Priority'
5      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
6      ELSE 'Mass'
7  END AS segment,
8  COUNT(DISTINCT t.customer_id) AS customers,
9  ROUND(AVG(t.balance_after), 0) AS avg_balance,
10  ROUND(MIN(t.balance_after), 0) AS min_balance,
11  ROUND(MAX(t.balance_after), 0) AS max_balance
12FROM fact_transaction t
13JOIN dim_customer c ON t.customer_id = c.customer_id
14GROUP BY 
15  CASE 
16      WHEN c.monthly_income >= 50000000 THEN 'Priority'
17      WHEN c.monthly_income >= 20000000 THEN 'Affluent'
18      ELSE 'Mass'
19  END
20ORDER BY avg_balance DESC;

1-- 9. Tài khoản ngủ — không giao dịch > 90 ngày
2SELECT 
3  c.customer_id,
4  c.monthly_income,
5  c.city,
6  MAX(t.transaction_date) AS last_txn_date,
7  DATEDIFF(DAY, MAX(t.transaction_date), '2024-12-31') AS days_inactive,
8  COUNT(*) AS total_txn_before_dormant
9FROM dim_customer c
10JOIN fact_transaction t ON c.customer_id = t.customer_id
11GROUP BY c.customer_id, c.monthly_income, c.city
12HAVING DATEDIFF(DAY, MAX(t.transaction_date), '2024-12-31') > 90
13ORDER BY days_inactive DESC;