{ "cells": [ { "cell_type": "code", "execution_count": 1, "id": "e706cfb0-2234-4c4c-95d8-d1968f656aa0", "metadata": { "tags": [] }, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 3, "id": "2edb58c0-33a2-4436-8128-05645af9990d", "metadata": { "tags": [] }, "outputs": [], "source": [ "from tms_data_interface import SQLQueryInterface\n", "seq = SQLQueryInterface(schema=\"transactionschema\")" ] }, { "cell_type": "code", "execution_count": 4, "id": "0ccc875a-6ff5-4741-9495-93c6871b1027", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/plain": [ "[['account_data_v1'],\n", " ['account_data_v2'],\n", " ['alert_data_v1'],\n", " ['alert_data_v2'],\n", " ['customer_data_v1'],\n", " ['customer_data_v2'],\n", " ['transaction10m'],\n", " ['transaction60m']]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "seq.execute_raw(\"show tables\")" ] }, { "cell_type": "code", "execution_count": 5, "id": "01502887-b11f-46d2-9a52-e493df19d049", "metadata": { "tags": [] }, "outputs": [], "source": [ "query = \"\"\"\n", " select final.CUSTOMER_NUMBER_main as Focal_id,\n", " CAST(final.Total_hrc_transaction_amount AS DECIMAL(18, 2)) AS Total_hrc_transaction_amount,\n", " final.Unique_country_codes,\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.Total_hrc_transaction_amount,\n", " subquery.Unique_country_codes\n", " from \n", " (\n", " select customer_number as CUSTOMER_NUMBER_1, \n", " sum(transaction_amount) as Total_hrc_transaction_amount, \n", " array_join(array_agg(DISTINCT benef_cntry_code), ',') AS unique_country_codes\n", " from \n", " (\n", " select * \n", " from {trans_data} trans_table \n", " left join {acc_data} acc_table\n", " on trans_table.benef_account_number = acc_table.account_number\n", " ) trans\n", " where account_number not in ('None')\n", " group by customer_number\n", " ) subquery\n", " ) main \n", " left join \n", " (\n", " select cd.CUSTOMER_NUMBER_3 as CUSTOMER_NUMBER_cust,\n", " cd.SEGMENT,\n", " cd.RISK,\n", " case\n", " when ad.SAR_FLAG is NULL then 'N'\n", " else ad.SAR_FLAG\n", " end as SAR_FLAG \n", " from\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 \n", " left join\n", " (\n", " select customer_number as CUSTOMER_NUMBER_4, \n", " sar_flag as SAR_FLAG\n", " from {alert_data}\n", " ) ad \n", " on cd.CUSTOMER_NUMBER_3 = ad.CUSTOMER_NUMBER_4\n", " ) as cust_alert\n", " on cust_alert.CUSTOMER_NUMBER_cust = main.CUSTOMER_NUMBER_main\n", " ) as final\n", "\"\"\"" ] }, { "cell_type": "code", "execution_count": 6, "id": "c28e15b5-4b09-46a6-849e-7ffd5cefee7f", "metadata": { "tags": [] }, "outputs": [], "source": [ "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\", \"Total_hrc_transaction_amount\", \"Unique_country_codes\",\n", " \"Segment\", \"Risk\", \"SAR_FLAG\"]\n", " df = pd.DataFrame(row_list, columns = cols)\n", " return df" ] }, { "cell_type": "code", "execution_count": 7, "id": "dc94e713-7267-499a-897f-672209d563c0", "metadata": { "tags": [] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Focal_idTotal_hrc_transaction_amountUnique_country_codesSegmentRiskSAR_FLAG
0PN7391875288386944.18NoneSMELow RiskN
1PN5733733984448017.83NoneWhole Sale BankingLow RiskY
2PN7911132203599126.03NoneSMEHigh RiskN
3PN5720584424937298.78NoneWhole Sale BankingLow RiskN
4PN375785496146009.32NoneWhole Sale BankingHigh RiskN
.....................
10009PN75957242099733.92INUltra High NetWorthMedium RiskY
10010PN14733839374120.63INPrivate BankingMedium RiskN
10011PN63219637892236.97LKOthersMedium RiskN
10012PN10040635620658.59INOthersHigh RiskN
10013PN45276935792822.47AMOthersLow RiskN
\n", "

10014 rows × 6 columns

\n", "
" ], "text/plain": [ " Focal_id Total_hrc_transaction_amount Unique_country_codes \\\n", "0 PN739187 5288386944.18 None \n", "1 PN573373 3984448017.83 None \n", "2 PN791113 2203599126.03 None \n", "3 PN572058 4424937298.78 None \n", "4 PN375785 496146009.32 None \n", "... ... ... ... \n", "10009 PN759572 42099733.92 IN \n", "10010 PN147338 39374120.63 IN \n", "10011 PN632196 37892236.97 LK \n", "10012 PN100406 35620658.59 IN \n", "10013 PN452769 35792822.47 AM \n", "\n", " Segment Risk SAR_FLAG \n", "0 SME Low Risk N \n", "1 Whole Sale Banking Low Risk Y \n", "2 SME High Risk N \n", "3 Whole Sale Banking Low Risk N \n", "4 Whole Sale Banking High Risk N \n", "... ... ... ... \n", "10009 Ultra High NetWorth Medium Risk Y \n", "10010 Private Banking Medium Risk N \n", "10011 Others Medium Risk N \n", "10012 Others High Risk N \n", "10013 Others Low Risk N \n", "\n", "[10014 rows x 6 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# sen = Scenario()\n", "# sen.logic()" ] }, { "cell_type": "code", "execution_count": null, "id": "7e585bbc-7baf-41a5-8f05-ca25c08e7ca8", "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 }