user_client2024/79
1
0
Fork 0
generated from dhairya/scenario_template
Code Issues Pull Requests Packages Projects Releases Wiki Activity
main
79/main.ipynb
user_client2024 23aaa1a05a System save at 09/10/2024 09:45 by user_client2024
2024-10-09 04:15:51 +00:00

366 lines
14 KiB
Plaintext
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

{
"cells": [
{
"cell_type": "code",
"execution_count": 2,
"id": "e706cfb0-2234-4c4c-95d8-d1968f656aa0",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"import pandas as pd\n",
"\n",
"query = \"\"\"\n",
" select final.CUSTOMER_NUMBER_main as Focal_id,\n",
" final.Credit_transaction_amount,\n",
" final.Total_no_of_credit_transactions,\n",
" final.Debit_transaction_amount,\n",
" final.Total_no_of_debit_transactions,\n",
" final.SEGMENT,\n",
" final.RISK,\n",
" final.SAR_FLAG\n",
" from \n",
" (\n",
" (\n",
" select subquery.CUSTOMER_NUMBER_1 as CUSTOMER_NUMBER_main,\n",
" subquery.Credit_transaction_amount,\n",
" subquery.Total_no_of_credit_transactions,\n",
" case\n",
" when subquery.Debit_transaction_amount is NULL then 0\n",
" else Debit_transaction_amount\n",
" end as Debit_transaction_amount,\n",
" case\n",
" when subquery.Total_no_of_debit_transactions is NULL then 0\n",
" else Total_no_of_debit_transactions\n",
" end as Total_no_of_debit_transactions\n",
" from \n",
" (\n",
" (\n",
" select customer_number as CUSTOMER_NUMBER_1, \n",
" sum(transaction_amount) as Credit_transaction_amount, \n",
" count(*) as Total_no_of_credit_transactions\n",
" from \n",
" (\n",
" select * \n",
" from {trans_data} trans_table left join {acc_data} acc_table\n",
" on trans_table.benef_account_number = acc_table.account_number\n",
" )\n",
" where account_number not in ('None')\n",
" group by 1\n",
" ) credit left join\n",
" (\n",
" select customer_number as CUSTOMER_NUMBER_2, \n",
" sum(transaction_amount) as Debit_transaction_amount, \n",
" count(*) as Total_no_of_debit_transactions\n",
" from \n",
" (\n",
" select * \n",
" from {trans_data} trans_table left join {acc_data} acc_table\n",
" on trans_table.orig_account_number = acc_table.account_number\n",
" )\n",
" where account_number not in ('None')\n",
" group by 1\n",
" ) debit on credit.CUSTOMER_NUMBER_1 = debit.CUSTOMER_NUMBER_2 \n",
" ) subquery\n",
" ) main left join \n",
" (\n",
" select subquery.CUSTOMER_NUMBER_3 as CUSTOMER_NUMBER_cust,\n",
" subquery.SEGMENT,\n",
" subquery.RISK,\n",
" case\n",
" when subquery.SAR_FLAG is NULL then 'N'\n",
" else subquery.SAR_FLAG\n",
" end as SAR_FLAG \n",
" from\n",
" (\n",
" (\n",
" select customer_number as CUSTOMER_NUMBER_3, \n",
" business_segment as SEGMENT,\n",
" case\n",
" when RISK_CLASSIFICATION = 1 then 'Low Risk'\n",
" when RISK_CLASSIFICATION = 2 then 'Medium Risk'\n",
" when RISK_CLASSIFICATION = 3 then 'High Risk'\n",
" else 'Unknown Risk'\n",
" end AS RISK\n",
" from {cust_data}\n",
" ) cd left join\n",
" (\n",
" select customer_number as CUSTOMER_NUMBER_4, \n",
" sar_flag as SAR_FLAG\n",
" from {alert_data}\n",
" ) ad on cd.CUSTOMER_NUMBER_3 = ad.CUSTOMER_NUMBER_4\n",
" ) subquery\n",
" ) cust_alert on cust_alert.CUSTOMER_NUMBER_cust = main.CUSTOMER_NUMBER_main\n",
" ) final\n",
"\"\"\"\n",
"\n",
"from tms_data_interface import SQLQueryInterface\n",
" \n",
"class Scenario:\n",
" seq = SQLQueryInterface(schema=\"transactionschema\")\n",
" \n",
" def logic(self, **kwargs):\n",
" row_list = self.seq.execute_raw(query.format(trans_data=\"transaction10m\",\n",
" cust_data=\"customer_data_v1\",\n",
" acc_data=\"account_data_v1\",\n",
" alert_data=\"alert_data_v1\")\n",
" )\n",
" cols = [\"Focal_id\", \"Credit_transaction_amount\", \"Total_no_of_credit_transactions\",\n",
" \"Debit_transaction_amount\", \"Total_no_of_debit_transactions\",\n",
" \"Segment\", \"Risk\", \"SAR_FLAG\"]\n",
" df = pd.DataFrame(row_list, columns = cols)\n",
" return df"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "741546f6-df8f-4578-bbd2-79aa38a98c5b",
"metadata": {
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>Focal_id</th>\n",
" <th>Credit_transaction_amount</th>\n",
" <th>Total_no_of_credit_transactions</th>\n",
" <th>Debit_transaction_amount</th>\n",
" <th>Total_no_of_debit_transactions</th>\n",
" <th>Segment</th>\n",
" <th>Risk</th>\n",
" <th>SAR_FLAG</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>PN182121</td>\n",
" <td>1.644268e+09</td>\n",
" <td>1168</td>\n",
" <td>1.717572e+09</td>\n",
" <td>1198</td>\n",
" <td>SME</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>PN476208</td>\n",
" <td>2.792031e+09</td>\n",
" <td>1993</td>\n",
" <td>2.777591e+09</td>\n",
" <td>2010</td>\n",
" <td>Whole Sale Banking</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>PN413985</td>\n",
" <td>3.621153e+09</td>\n",
" <td>2438</td>\n",
" <td>3.290559e+09</td>\n",
" <td>2375</td>\n",
" <td>SME</td>\n",
" <td>High Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>PN906130</td>\n",
" <td>1.666937e+09</td>\n",
" <td>1222</td>\n",
" <td>1.756952e+09</td>\n",
" <td>1177</td>\n",
" <td>Whole Sale Banking</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>PN446914</td>\n",
" <td>3.379522e+09</td>\n",
" <td>2378</td>\n",
" <td>3.512353e+09</td>\n",
" <td>2454</td>\n",
" <td>SME</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10009</th>\n",
" <td>PN723207</td>\n",
" <td>4.765892e+07</td>\n",
" <td>298</td>\n",
" <td>2.651826e+07</td>\n",
" <td>378</td>\n",
" <td>Ultra High NetWorth</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10010</th>\n",
" <td>PN860773</td>\n",
" <td>3.537672e+07</td>\n",
" <td>256</td>\n",
" <td>2.578618e+07</td>\n",
" <td>363</td>\n",
" <td>Others</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10011</th>\n",
" <td>PN741843</td>\n",
" <td>3.787797e+07</td>\n",
" <td>255</td>\n",
" <td>2.909541e+07</td>\n",
" <td>346</td>\n",
" <td>Priority Banking</td>\n",
" <td>Medium Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10012</th>\n",
" <td>PN588393</td>\n",
" <td>3.567320e+07</td>\n",
" <td>254</td>\n",
" <td>2.676294e+07</td>\n",
" <td>391</td>\n",
" <td>Mass Market</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10013</th>\n",
" <td>PN168372</td>\n",
" <td>3.166679e+07</td>\n",
" <td>253</td>\n",
" <td>2.707380e+07</td>\n",
" <td>355</td>\n",
" <td>Mass Market</td>\n",
" <td>Low Risk</td>\n",
" <td>N</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>10014 rows × 8 columns</p>\n",
"</div>"
],
"text/plain": [
" Focal_id Credit_transaction_amount Total_no_of_credit_transactions \\\n",
"0 PN182121 1.644268e+09 1168 \n",
"1 PN476208 2.792031e+09 1993 \n",
"2 PN413985 3.621153e+09 2438 \n",
"3 PN906130 1.666937e+09 1222 \n",
"4 PN446914 3.379522e+09 2378 \n",
"... ... ... ... \n",
"10009 PN723207 4.765892e+07 298 \n",
"10010 PN860773 3.537672e+07 256 \n",
"10011 PN741843 3.787797e+07 255 \n",
"10012 PN588393 3.567320e+07 254 \n",
"10013 PN168372 3.166679e+07 253 \n",
"\n",
" Debit_transaction_amount Total_no_of_debit_transactions \\\n",
"0 1.717572e+09 1198 \n",
"1 2.777591e+09 2010 \n",
"2 3.290559e+09 2375 \n",
"3 1.756952e+09 1177 \n",
"4 3.512353e+09 2454 \n",
"... ... ... \n",
"10009 2.651826e+07 378 \n",
"10010 2.578618e+07 363 \n",
"10011 2.909541e+07 346 \n",
"10012 2.676294e+07 391 \n",
"10013 2.707380e+07 355 \n",
"\n",
" Segment Risk SAR_FLAG \n",
"0 SME Low Risk N \n",
"1 Whole Sale Banking Low Risk N \n",
"2 SME High Risk N \n",
"3 Whole Sale Banking Low Risk N \n",
"4 SME Low Risk N \n",
"... ... ... ... \n",
"10009 Ultra High NetWorth Low Risk N \n",
"10010 Others Low Risk N \n",
"10011 Priority Banking Medium Risk N \n",
"10012 Mass Market Low Risk N \n",
"10013 Mass Market Low Risk N \n",
"\n",
"[10014 rows x 8 columns]"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# sen = Scenario()\n",
"# sen.logic()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2f8eee99-046e-4d56-afb8-9007fdc2f5b2",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.8"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
Reference in New Issue View Git Blame Copy Permalink