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;
8weeksqlchallenge - Week 4
Case Study #4 - Data Bank
Introduction
Danny is building digital Bank without physical branches. So data storage plays vital role in Bank development. Its responsibility is tracking customer activity and creating the forecasts for the future.
Case Study Insights
Although this case study is really simple comparing to the Database of Bank in reality, it shows us a some basic metrics need to be calculated to have forecast and plan for Bank future development.
Part A Customer Nodes Exploration focuses on giving the overall picture of customer allocation in Banking system. With given data , we can generate some ideas about leading data security system:
Part B. Customer Transactions
In this section, we will delve into the intricacies of customer transactions, categorizing them by months.
By presenting this comprehensive information, we aim to generate monthly reports for customers, enabling them to gain a clearer understanding of their spending habits.
Moreover, these reports will serve as a valuable tool for supervisors to provide insightful advice, especially concerning significant expenses like housing or automobiles.
Or you can refer these resources for more information about banking data security & customer metrics:
SQL code
The techniques that you can learn from this case study :
Let’s get through some questions of this study case to review of technique.
Part A.Customer Nodes Exploration
5. What is the median, 80th and 95th percentile for this same reallocation days metric for each region?
Click for code
Part B.Customer Transactions
First CTE, We calculate the amount of deposits, purchases and withdrawals for each customer grouped by month.
Second CTE, We compute the total balance (deposit - purchase - withdrawal) each month, customer
Finally Select, We calculate the balance after each month of each customer by sliding method (ROWS BETWEEN UNBOUNDED PRECEDING AND current ROW)
4.What is the closing balance for each customer at the end of the month? Also show the change in balance each month in the same table output.
This is a particularly difficult question - there are 2 CTEs!
Click for code
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;
We have total 10 questions. You can access my github link for full code and explanation
My Github