Schema.sql

{"metadata":{"kernel_spec":{"name":"SQL","language":"sql","display_name":"SQL"},"language_info":{"name":"sql","version":""}},"nbformat":4,"nbformat_minor":2,"cells":[{"cell_type":"markdown","source":["# [dbo].[core_banking_transactions]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='core_banking_transactions' and @Schema='dbo']","object_type":"Table"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["USE [CBS]\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='core_banking_transactions' and @Schema='dbo']","object_type":"Table"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  Table [dbo].[core_banking_transactions]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","CREATE TABLE [dbo].[core_banking_transactions](\r\n\t[tnx_id] [bigint] NOT NULL,\r\n\t[account_id] [bigint] NOT NULL,\r\n\t[branch_id] [int] NOT NULL,\r\n\t[tnx_type] [varchar](50) NULL,\r\n\t[channel] [varchar](30) NULL,\r\n\t[amount] [decimal](18, 2) NULL,\r\n\t[tnx_status] [varchar](20) NULL,\r\n\t[tnx_timestamp] [datetime] NULL,\r\nPRIMARY KEY CLUSTERED \r\n(\r\n\t[tnx_id] ASC\r\n)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]\r\n) ON [PRIMARY]\r\nGO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='core_banking_transactions' and @Schema='dbo']","object_type":"Table"}},{"cell_type":"markdown","source":["# [dbo].[vw_channel_transactions_summary]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_channel_transactions_summary' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_channel_transactions_summary]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_channel_transactions_summary] as\r\nselect channel, count(*) as total_transactions, sum(amount)\r\nas total_amount\r\nfrom core_banking_transactions group by channel;\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_channel_transactions_summary' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[vw_transaction_failure_rate]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_transaction_failure_rate' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_transaction_failure_rate]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_transaction_failure_rate] as \r\nselect tnx_status, count(*) as total_tnx,\r\ncount(*) *100.0/sum(count(*)) over() as percentages\r\nfrom core_banking_transactions\r\ngroup by tnx_status;\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_transaction_failure_rate' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[vw_peak_transaction_hours]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_peak_transaction_hours' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_peak_transaction_hours]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_peak_transaction_hours] as\r\nselect datepart(hour, tnx_timestamp) as tnx_hour,\r\ncount(*) as total_transactions\r\nfrom core_banking_transactions\r\ngroup by datepart(hour,tnx_timestamp);\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_peak_transaction_hours' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[atm_logs]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='atm_logs' and @Schema='dbo']","object_type":"Table"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  Table [dbo].[atm_logs]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","CREATE TABLE [dbo].[atm_logs](\r\n\t[atm_id] [int] NULL,\r\n\t[location] [varchar](100) NULL,\r\n\t[withdrawal_amount] [decimal](18, 2) NULL,\r\n\t[tnx_status] [varchar](20) NULL,\r\n\t[response_time_ms] [int] NULL,\r\n\t[timestamp] [datetime] NULL\r\n) ON [PRIMARY]\r\nGO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='atm_logs' and @Schema='dbo']","object_type":"Table"}},{"cell_type":"markdown","source":["# [dbo].[vw_atm_performance]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_atm_performance' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_atm_performance]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_atm_performance] as \r\nselect location, count(*) as total_transactions,\r\navg(response_time_ms) as avg_response_time\r\nfrom atm_logs\r\ngroup by location;\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_atm_performance' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[vw_top_accounts]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_top_accounts' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_top_accounts]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_top_accounts] as \r\nselect top 10 account_id, sum(amount) as total_transaction_value\r\nfrom core_banking_transactions\r\ngroup by account_id\r\norder by total_transaction_value desc;\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_top_accounts' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[replication_metrics]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='replication_metrics' and @Schema='dbo']","object_type":"Table"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  Table [dbo].[replication_metrics]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","CREATE TABLE [dbo].[replication_metrics](\r\n\t[environment] [varchar](10) NULL,\r\n\t[extract_lag_sec] [int] NULL,\r\n\t[replicat_lag_sec] [int] NULL,\r\n\t[trail_file_size_mb] [decimal](10, 2) NULL,\r\n\t[log_read_rate_mb] [decimal](10, 2) NULL,\r\n\t[metric_timestamp] [datetime] NULL\r\n) ON [PRIMARY]\r\nGO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='replication_metrics' and @Schema='dbo']","object_type":"Table"}},{"cell_type":"markdown","source":["# [dbo].[vw_replication_lag_summary]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_replication_lag_summary' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_replication_lag_summary]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_replication_lag_summary] as\r\nselect environment, avg(replicat_lag_sec) as avg_replication_lag,\r\nmax(replicat_lag_sec) as max_replication_lag\r\nfrom replication_metrics\r\ngroup by environment;\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_replication_lag_summary' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[vw_tnx_vs_lag]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_tnx_vs_lag' and @Schema='dbo']","object_type":"View"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  View [dbo].[vw_tnx_vs_lag]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","create view [dbo].[vw_tnx_vs_lag] as\r\nwith hourly_tnx as (\r\nselect datepart(hour,tnx_timestamp) as tnx_hour,\r\ncount(*) as tnx_count\r\nfrom core_banking_transactions\r\ngroup by datepart(hour,tnx_timestamp) )\r\n\r\nselect h.tnx_hour, h.tnx_count, r.replicat_lag_sec\r\nfrom hourly_tnx h join replication_metrics r \r\non datepart(hour, r.metric_timestamp)=h.tnx_hour;\r\n","GO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/View[@Name='vw_tnx_vs_lag' and @Schema='dbo']","object_type":"View"}},{"cell_type":"markdown","source":["# [dbo].[interest_logs]"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='interest_logs' and @Schema='dbo']","object_type":"Table"}},{"outputs":[],"execution_count":0,"cell_type":"code","source":["/****** Object:  Table [dbo].[interest_logs]    Script Date: 22-03-2026 20:23:22 ******/\r\nSET ANSI_NULLS ON\r\n","GO\r\n","SET QUOTED_IDENTIFIER ON\r\n","GO\r\n","CREATE TABLE [dbo].[interest_logs](\r\n\t[account_id] [bigint] NULL,\r\n\t[interest_rate] [decimal](5, 2) NULL,\r\n\t[daily_balance] [decimal](18, 2) NULL,\r\n\t[interest_amount] [decimal](18, 2) NULL,\r\n\t[accrual_date] [date] NULL\r\n) ON [PRIMARY]\r\nGO\r\n"],"metadata":{"urn":"Server[@Name='DESKTOP-NSVIS72']/Database[@Name='CBS']/Table[@Name='interest_logs' and @Schema='dbo']","object_type":"Table"}}]}