Case Study #4 - Data Bank

SELECT * FROM 'regions.csv'

SELECT * FROM 'customer_nodes.csv'

SELECT * FROM 'Customer-Transactions.csv'

SELECT COUNT( DISTINCT node_id) as unique_nodes FROM customer_nodes

SELECT c.region_id, region_name, count(node_id) as total_nodes FROM customer_nodes c JOIN regions r ON c.region_id = r.region_id GROUP BY c.region_id,region_name ORDER BY c.region_id

SELECT c.region_id, region_name, COUNT(distinct customer_id) as total_customers FROM customer_nodes c JOIN regions r ON c.region_id = r.region_id GROUP BY c.region_id, region_name ORDER BY c.region_id

SELECT AVG(DATEDIFF(day,start_date,end_date)) FROM customer_nodes WHERE end_date != '9999-12-31';

WITH CTE AS (SELECT region_id, DATEDIFF(day,start_date,end_date) as allocation_days FROM customer_nodes WHERE end_date != '9999-12-31' ) SELECT distinct region_id , PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY allocation_days) OVER (PARTITION BY region_id) AS median, PERCENTILE_DISC(0.8) WITHIN GROUP (ORDER BY allocation_days) OVER (PARTITION BY region_id) AS 80th_percentile, PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY allocation_days) OVER (PARTITION BY region_id) AS 95TH_percentile FROM CTE

SELECT txn_type, COUNT(*) as total_transaction, SUM(txn_amount) as total_amount FROM customer_transactions GROUP BY txn_type;

WITH DEPOSIT_CTE AS ( SELECT customer_id, COUNT(customer_id) as time_deposit, AVG(txn_amount) as amount_deposit FROM customer_transactions WHERE txn_type = 'deposit' GROUP BY customer_id ) SELECT AVG(time_deposit) AS avg_count, AVG(amount_deposit) AS avg_amount FROM DEPOSIT_CTE;

WITH CTE AS ( SELECT customer_id, datepart(month,txn_date) as month, SUM(CASE WHEN txn_type ='deposit' then 1 else 0 end) as deposit_time, SUM(CASE WHEN txn_type ='purchase' then 1 else 0 end) as purchase_time, SUM(CASE WHEN txn_type ='withdrawal' then 1 else 0 end) as withdrawal_time FROM customer_transactions GROUP BY customer_id,datepart(month,txn_date)) SELECT month, count(*) FROM CTE WHERE deposit_time > 1 and (purchase_time =1 or withdrawal_time =1) GROUP BY month;

WITH CTE as ( SELECT customer_id, DATEPART(MONTH,txn_date) as month, SUM(CASE WHEN txn_type ='deposit' then txn_amount else 0 end) as deposit, SUM(CASE WHEN txn_type ='purchase' then - txn_amount else 0 end) as purchase , SUM(CASE WHEN txn_type ='withdrawal' then - txn_amount else 0 end) as withdrawal from customer_transactions GROUP BY customer_id,DATEPART(MONTH,txn_date) ), CTE_2 AS ( SELECT customer_id, month,(deposit +purchase +withdrawal) as total from CTE) SELECT customer_id, month, SUM(total) OVER (PARTITION BY customer_id ORDER BY customer_id,month ROWS BETWEEN UNBOUNDED PRECEDING AND current ROW) AS balance, total AS change_in_balance FROM CTE_2;

WITH CTE as ( SELECT customer_id, DATEPART(MONTH,txn_date) as month, SUM(CASE WHEN txn_type ='deposit' then txn_amount else 0 end) as deposit, SUM(CASE WHEN txn_type ='purchase' then - txn_amount else 0 end) as purchase , SUM(CASE WHEN txn_type ='withdrawal' then - txn_amount else 0 end) as withdrawal from customer_transactions GROUP BY customer_id,DATEPART(MONTH,txn_date) ), CTE_2 AS ( SELECT customer_id, month,(deposit +purchase +withdrawal) as total from CTE), CTE_3 AS ( SELECT customer_id, month, SUM(total) OVER (PARTITION BY customer_id ORDER BY customer_id,month ROWS BETWEEN UNBOUNDED PRECEDING AND current ROW) AS balance,total AS change_in_balance FROM CTE_2), CTE_4 AS ( SELECT distinct customer_id , first_value(balance) over (partition by customer_id order by customer_id) as start_balance, last_value(balance) over (partition by customer_id order by customer_id) as end_balance FROM cte_3 ), CTE_5 AS ( SELECT *, ((end_balance - start_balance) * 100 / start_balance) as growing_rate FROM CTE_4 WHERE ((end_balance - start_balance) * 100 / start_balance) >= 5 AND end_balance >start_balance) SELECT CAST(COUNT (customer_id) AS FLOAT) * 100 / (SELECT COUNT (DISTINCT customer_id) from customer_transactions) as Percent_Customer FROM CTE_5