In this course project, I designed and implemented a reporting system for the Bank of Canada to manage and analyze collateral pledging data. The project involved several critical steps:
Data Categorization: I created new tables to classify counterparties and securities into specific types and asset classes using SQL CASE statements. This categorization was essential for accurately reflecting the risk profiles of different financial instruments.
Data Integration: I merged the categorized data into transaction tables using SQL LEFT JOIN operations. This step ensured that all transactions were linked accurately to the corresponding counterparty types and asset classes, providing a complete dataset for further analysis.
Handling Complex Data Structures: I addressed challenges such as dealing with multiple security identifiers by using subqueries to accurately match and retrieve the appropriate asset class for each transaction. This approach ensured data accuracy without compromising performance.
Final Data Summarization: I consolidated the transaction data into a summary report, grouping it by counterparty type, direction of collateral, and asset class. This report provided valuable insights for strategic decision-making and compliance with regulatory standards.
Structured Reporting: I developed a structured header table using cross joins to ensure the final report could accommodate all possible data scenarios. The report was finalized by integrating the structured headers with the summarized data, using COALESCE to manage any missing values and ensure completeness.
This project gave me hands-on experience with SQL and data management techniques, particularly in the context of financial data and regulatory reporting.