{"id":1007,"date":"2023-07-04T09:56:11","date_gmt":"2023-07-04T01:56:11","guid":{"rendered":"https:\/\/five.co\/?p=1007"},"modified":"2026-05-12T10:24:23","modified_gmt":"2026-05-12T02:24:23","slug":"five-hackathon-2023-meet-the-winners","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/five-hackathon-2023-meet-the-winners\/","title":{"rendered":"Five Hackathon 2023: Meet the Winners and The BudgetHub Web App"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Five Hackathon 2023: Meet the Winners and The BudgetHub Web App<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Congratulations to <a href=\"https:\/\/www.linkedin.com\/in\/ngrq94\/\" rel=\"nofollow noopener\" target=\"_blank\">Rui Qin Ng<\/a> and <a href=\"https:\/\/www.linkedin.com\/in\/jiahwee-wong\/\" rel=\"nofollow noopener\" target=\"_blank\">Jia Hwee Wong<\/a> from Singapore for winning <a href=\"https:\/\/preview-php85.dzine.org\/five\/hackathon\/\">Five&#8217;s 2023 Low-Code Hackathon<\/a>!<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img width=\"1280\" height=\"720\" src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-2.png\" alt=\"Five.Co - Low-Code Hackathon 2023 - Meet The Winners and Inventors of the BudgetHub Web App\" class=\"wp-image-1009\" srcset=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-2.png 1280w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-2-300x169.png 300w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-2-1024x576.png 1024w, https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-2-768x432.png 768w\" sizes=\"(max-width: 1280px) 100vw, 1280px\" \/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Rui Qin is a Technical Solutions Architect at Mastercard with a Bachelor of Science in Information Systems and Operations Analytics from the <span aria-hidden=\"true\">Singapore Management University. Jia Hwee is a Data Scientist at Carro, Southeast Asia&#8217;s largest online used car marketplace.&nbsp;<\/span><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Together they created&nbsp;<strong>BudgetHub<\/strong>, a budgeting application that allows users to conveniently associate transactions with all of their bank accounts and cards so that they can manage their finances better and budget for their future.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Here&#8217;s what the judges liked about the application:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>The application solves a real-world problem<\/strong>: who doesn&#8217;t struggle with various credit cards, e-wallets, and bank accounts? BudgetHub makes it easy to get a comprehensive, all-in-one view onto your finances.<\/li>\n\n\n\n<li><strong>Use of Five&#8217;s features<\/strong>: the application makes excellent use of Five&#8217;s pre-built features, such as forms, charts, and dashboards. By having the application&#8217;s navigation menu running along the top, Rui Qin and Jia Hwee made sure there&#8217;s more space for data visualizations &#8211; exactly what their end-users would demand.<\/li>\n\n\n\n<li><strong>The application is very well documented:<\/strong> Rui Qin and Jia Hwee excelled in writing an excellent Medium article that covers everything about their application: functionality, developer notes, SQL queries, and JavaScript functions. And they made great use of screenshots to illustrate what their application looks like.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Below is a copy of their winning <a href=\"https:\/\/medium.com\/@ruiqinng\/five-hackathon-2023-budgethub-738d2c78f07a\" rel=\"nofollow noopener\" target=\"_blank\">Medium post about their application<\/a>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Budgeting Made Simple: Save, Simplify, Succeed!<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"28f5\">Money in, money out. We have different bank accounts and financial products (such as debit\/credit cards) across many banks. Without a consolidated view of all of our transactions, we are unable to gain a holistic view of our spending, much less being able to budget for the future. This can lead to financial stress, which is felt by about 70% of Americans in a&nbsp;<a href=\"https:\/\/www.cnbc.com\/2023\/04\/11\/58percent-of-americans-are-living-paycheck-to-paycheck-cnbc-survey-reveals.html\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">recent survey conducted by CNBC<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"d2a6\">Amidst the rising inflation,&nbsp;<a href=\"https:\/\/newsroom.wf.com\/English\/news-releases\/news-release-details\/2022\/New-Study-Americans-Lean-Into-Credit-Card-Rewards-to-Offset-Rising-Costs--Including-Travel\/default.aspx\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">many people are leaning into credit card rewards to offset rising costs<\/a>. However, we recognise the inconvenience of having to remember all the criteria necessary to earn credit card rewards.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"350d\"><a href=\"https:\/\/www.forbes.com\/advisor\/banking\/are-budgeting-apps-worth-it\/\" target=\"_blank\" rel=\"noreferrer noopener nofollow\">Budgeting applications bring convenience, ease of use, financial awareness and a comprehensive view of one\u2019s personal finances to users<\/a>. Therefore, we developed&nbsp;<strong>BudgetHub<\/strong>, a budgeting application that allows the user to conveniently associate transactions with all of their bank accounts and cards so that they can manage their finances better and budget for their future.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Bank Accounts and Cards<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Under the&nbsp;<strong><em>Bank Accounts<\/em><\/strong>&nbsp;and&nbsp;<strong><em>Cards<\/em><\/strong>&nbsp;menu, users can enter their bank account and card details respectively. This allows BudgetHub to differentiate between the various financial products in order to segment transactions to calculate account balances and accounts payable.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:2964\/1*sGG_6Dn-Q-5ZvQ9mmGRfEw.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:2964\/1*XT2Cx735YKMcGUlPVNu3xA.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"has-text-align-center wp-block-paragraph\">Bank Account and Card Forms<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"638f\">Transactions<\/h2>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"1686\">Transactions can be entered into BudgetHub in the&nbsp;<strong class=\"le fp\"><em class=\"ma\">Bank Accounts<\/em><\/strong>&nbsp;and&nbsp;<strong class=\"le fp\"><em class=\"ma\">Cards<\/em><\/strong>&nbsp;menu. The transactions will automatically be associated. In addition to the amount, the following fields are available:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Type: Income or Expense<\/li>\n\n\n\n<li>Currency (3 letter code; BudgetHub supports multi-currency)<\/li>\n\n\n\n<li>Category (optional)<\/li>\n\n\n\n<li>Merchant Name (optional)<\/li>\n<\/ul>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1400\/1*0PTSkI83BoiyR2HWCPUyYg.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"has-text-align-center wp-block-paragraph\">Transaction Form<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"fd76\">Currency Standardisation<\/h2>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"07e4\">To visualise transactions with multiple currencies, all transactions should be standardised to one currency for a fair comparison. Currency pairs and their rates are being stored on BudgetHub in order for conversions to the home currency (USD) to be calculated.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1282\/1*ozS7KHfmoXfdo-9jviV7Tw.png\" alt=\"\"\/><figcaption class=\"wp-element-caption\">Interaction with ExchangeRate-API<\/figcaption><\/figure>\n<\/div>\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"a367\">Since rates fluctuate over time, BudgetHub allows users to refresh the rates by integrating with ExchangeRate-API\u2019s&nbsp;<a class=\"af ne\" href=\"https:\/\/www.exchangerate-api.com\/docs\/standard-requests\" target=\"_blank\" rel=\"noopener ugc nofollow\">Standard API<\/a>.&nbsp;<em class=\"ma\">(Thanks Dom&nbsp;<\/em><a class=\"af ne\" href=\"https:\/\/medium.com\/@dom_five\" rel=\"noopener nofollow\" target=\"_blank\"><em class=\"ma\">@dom_five<\/em><\/a><em class=\"ma\">&nbsp;for the&nbsp;<\/em><a href=\"https:\/\/preview-php85.dzine.org\/five\/building-an-application-that-interacts-with-the-google-maps-api\/\" target=\"_blank\" rel=\"noreferrer noopener\"><em class=\"ma\">blog post on API integration<\/em><\/a><em class=\"ma\">!)<\/em><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"0341\">Overview Dashboard<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1000\/1*Wegq1ZyOmuxNDsS_oTMR2A.jpeg\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"1c8a\">Budgeting is very important in managing one\u2019s finances. BudgetHub is a helpful and convenient tool in making budgeting easier for users. Users can access the&nbsp;<strong class=\"le fp\"><em class=\"ma\">Overview<\/em><\/strong>&nbsp;dashboard to get an aggregated view of their historical transactions and how their total balance changed over time.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1400\/1*ZwqArzO7H_R_r-zb4zalew.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"0b52\">The total balance is the start-of-week balance of their bank accounts, less the spend on their cards. The four weeks rolling percentage savings, calculated by deducting the expenses from incomes (numerator) over the incomes (denominator), are also calculated and visualised in a bar chart. These will allow users to monitor their savings progress easily. Following the 50\u201330\u201320 budgeting rule, one should aim to save at least 20% of their savings.<\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">Referencing the 4 weeks rolling percentage savings chart, and assuming the user receives their monthly salary at about the 20th of each month, the user saved about 75% of their salary in March, but only saved about 20% in April.<\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">At a glance, users can monitor their financial inflows and outflows too, identifying periods where spend is higher.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"bf68\">Insights Dashboard<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1400\/1*zeKMGRcH73wZN-p2U8ZL1Q.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"d1f1\">For those who want to find out more about their finances, the&nbsp;<strong class=\"le fp\"><em class=\"ma\">Insights<\/em>&nbsp;<\/strong>dashboard provides just that.<\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"862e\">Here, the top 5 categories (with the rest grouped under&nbsp;<em class=\"ma\">Others<\/em>) is plotted, giving users the ability to quickly see what they spend on the most. The weekly percentage change in savings is derived from the weekly total balance, while the weekly estimated points and cashback are calculated with&nbsp;<strong class=\"le fp\"><em class=\"ma\">Rewards<\/em><\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"4dbf\">Rewards: Cashback and Points<\/h2>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1362\/1*1zv0r4ddjdtZLUTFI62hFQ.jpeg\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"4162\">Too many credit cards, too many perks and too many hoops to jump through? BudgetHub is here to help. We want to help users store, track and compare all these information in one application.<\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"6732\">On top of recording transactions made on cards, users can also store their card perks and their benefits in the&nbsp;<strong class=\"le fp\"><em class=\"ma\">Rewards<\/em><\/strong>&nbsp;menu, such as the cashback percentage for certain merchant categories, and the minimum spend to qualify for the cashback.<\/p>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\" id=\"40e3\">The following fields are captured in the cashback and points tables:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Category (optional)<\/li>\n\n\n\n<li>Merchant Name (optional)<\/li>\n\n\n\n<li>Currency<\/li>\n\n\n\n<li>Minimum Spend (optional)<\/li>\n\n\n\n<li>Percentage (cashback) \/ Points per Unit (points)<\/li>\n\n\n\n<li>Constant (optional)<\/li>\n<\/ul>\n\n\n\n<p class=\"has-text-align-left wp-block-paragraph\">For instance, the criteria for points is 10 points plus 1.5 points per dollar with a minimum spend of US$100. The currency will be USD, minimum spend will be 100, points per unit will be 1.5 and constant will be 10.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:2964\/1*hPcizoWjR7ghlw7cQ7yMeg.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:2964\/1*85uFBC9Cun2LOECl14GOrg.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Still a hassle to go through the perks that were recorded to select the best card for your purchase? Look for&nbsp;<strong><em>Card Recommender<\/em><\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Card Recommender<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The&nbsp;<strong><em>Card Recommender<\/em><\/strong>&nbsp;does exactly what it does \u2014 recommend the best card for cashback and points based on transaction attributes. Simply fill in the amount, currency, category and merchant name (if applicable), and it will show all the perks that your cards can give. All that is left is to decide what\u2019s best and make that transaction!<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1400\/1*GEEZzpVHjrqQPRYFUimUvQ.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The card recommendations for both points and cashback are sorted in descending order, so the first card on each list is the best card to use for each reward type.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Conclusion<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">That\u2019s what BudgetHub is. A simple, user-friendly application that stores transactions, maps them to the bank account or card, and visualises one\u2019s cashflow. Furthermore, it has refreshable currency exchange rates for a uniform comparison, and the ability to recommend the best card for each transaction for the most savings.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\" id=\"8673\">We hope that end-users will be able to track and visualise their savings, progressing toward their financial goals.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Developer Notes<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The following are some design and engineering assets that we would like to showcase.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Entity-Relationship Diagram (ERD)<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">The following diagram shows how the database is structured.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img src=\"https:\/\/miro.medium.com\/v2\/resize:fit:1400\/1*TmQjElTq9GHAyb_GhcKQpw.png\" alt=\"\"\/><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Four Weeks Rolling Percentage Savings Query<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This query is used for the&nbsp;<em>Four Weeks Rolling Percentage Savings<\/em>&nbsp;chart in the&nbsp;<strong><em>Overview&nbsp;<\/em><\/strong>dashboard. We first standardise all transaction amounts to US Dollar, before calculating the weekly inflow and outflow. The four week rolling numbers are then calculated, then deriving the percentage savings.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH transaction_standardised AS (\n    SELECT\n        Date,\n        (CASE WHEN Currency = \"USD\" THEN Amount ELSE Amount * (1 \/ Rate.Rate) END) AS Amount,\n        Type\n    FROM \n        Transaction\n    LEFT JOIN\n        Rate\n    ON \n        Transaction.Currency = Rate.TargetCurrency\n),\ninflow_outflow AS (\n    SELECT \n        WEEK(Date, 2) as week_number,\n        CASE\n            WHEN WEEKDAY(Date) &lt;> 6 \n                THEN DATE_FORMAT(DATE_SUB(Date, INTERVAL WEEKDAY(Date) + 1 DAY), GET_FORMAT(DATE, 'ISO'))\n            ELSE DATE_FORMAT(Date, GET_FORMAT(DATE, 'ISO'))\n        END as week_start_date,\n        SUM(CASE WHEN Type = \"Income\" THEN Amount ELSE 0 END) AS inflow, \n        SUM(CASE WHEN `Type` = \"Expense\" THEN Amount ELSE 0 END) AS outflow,\n        ROW_NUMBER() OVER (ORDER BY WEEK(Date, 2)) AS row_num\n    FROM transaction_standardised\n    GROUP BY 1, 2\n),\nrolling_inflow_outflow AS (\n    SELECT\n        week_number,\n        week_start_date,\n        SUM(inflow) OVER (ORDER BY row_num ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_inflow,\n        SUM(outflow) OVER (ORDER BY row_num ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_outflow,\n        row_num\n    FROM inflow_outflow\n)\nSELECT\n    week_number,\n    week_start_date,\n    rolling_inflow,\n    rolling_outflow,\n    IF(rolling_inflow = 0 OR rolling_outflow > rolling_inflow, \n        0, \n        ROUND((rolling_inflow - rolling_outflow) \/ rolling_inflow * 100, 2)) AS savings_percentage,\n    row_num\nFROM rolling_inflow_outflow<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">WITH<\/span><span style=\"color: #D8DEE9FF\"> transaction_standardised <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        (<\/span><span style=\"color: #81A1C1\">CASE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> Currency <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">USD<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #D8DEE9FF\"> Rate.Rate) <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> Amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">Type<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">Transaction<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">LEFT JOIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        Rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        Transaction.Currency <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> Rate.TargetCurrency<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">inflow_outflow <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">WEEK<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> week_number,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">CASE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WEEKDAY<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">&lt;&gt;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">6<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DATE_FORMAT<\/span><span style=\"color: #D8DEE9FF\">(DATE_SUB(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, INTERVAL <\/span><span style=\"color: #81A1C1\">WEEKDAY<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">+<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DAY<\/span><span style=\"color: #D8DEE9FF\">), GET_FORMAT(<\/span><span style=\"color: #81A1C1\">DATE<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">ISO<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DATE_FORMAT<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, GET_FORMAT(<\/span><span style=\"color: #81A1C1\">DATE<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">ISO<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> week_start_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">CASE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">Type<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">Income<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> inflow, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">CASE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><span style=\"color: #A3BE8C\">Type<\/span><span style=\"color: #ECEFF4\">`<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">Expense<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> outflow,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">ROW_NUMBER<\/span><span style=\"color: #D8DEE9FF\">() <\/span><span style=\"color: #81A1C1\">OVER<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">ORDER BY<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WEEK<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">)) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> row_num<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> transaction_standardised<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">GROUP BY<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">rolling_inflow_outflow <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        week_number,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        week_start_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(inflow) <\/span><span style=\"color: #81A1C1\">OVER<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">ORDER BY<\/span><span style=\"color: #D8DEE9FF\"> row_num <\/span><span style=\"color: #81A1C1\">ROWS<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">BETWEEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">3<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">PRECEDING<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">AND<\/span><span style=\"color: #D8DEE9FF\"> CURRENT <\/span><span style=\"color: #81A1C1\">ROW<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> rolling_inflow,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(outflow) <\/span><span style=\"color: #81A1C1\">OVER<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">ORDER BY<\/span><span style=\"color: #D8DEE9FF\"> row_num <\/span><span style=\"color: #81A1C1\">ROWS<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">BETWEEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">3<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">PRECEDING<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">AND<\/span><span style=\"color: #D8DEE9FF\"> CURRENT <\/span><span style=\"color: #81A1C1\">ROW<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> rolling_outflow,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        row_num<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> inflow_outflow<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    week_number,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    week_start_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    rolling_inflow,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    rolling_outflow,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">IF<\/span><span style=\"color: #D8DEE9FF\">(rolling_inflow <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">OR<\/span><span style=\"color: #D8DEE9FF\"> rolling_outflow <\/span><span style=\"color: #81A1C1\">&gt;<\/span><span style=\"color: #D8DEE9FF\"> rolling_inflow, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\">, <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">ROUND<\/span><span style=\"color: #D8DEE9FF\">((rolling_inflow <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\"> rolling_outflow) <\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #D8DEE9FF\"> rolling_inflow <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">100<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">)) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> savings_percentage,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    row_num<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> rolling_inflow_outflow<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Weekly Percentage Change in Savings Query<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">This query is used for the&nbsp;<em>Weekly Percentage Change in Savings<\/em>&nbsp;chart in the&nbsp;<strong><em>Insights<\/em><\/strong>&nbsp;dashboard. Like the previous query, we first standardise all transaction amounts to US Dollar, before calculating the weekly inflow and outflow. The results are then joined and the weekly percentage change is calculated using&nbsp;<code>LAG()<\/code>.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WITH transaction_standardised AS (\n    SELECT\n        Date,\n        (CASE WHEN Currency = \"USD\" THEN Amount ELSE Amount * (1 \/ Rate.Rate) END) AS Amount,\n        Type\n    FROM \n        Transaction\n    LEFT JOIN\n        Rate\n    ON \n        Transaction.Currency = Rate.TargetCurrency\n),\nweekly_money_in AS (\n    SELECT\n        WEEK(Date, 2) AS week_number_in,\n        CASE\n            WHEN WEEKDAY(Date) &lt;> 6 \n                THEN DATE_FORMAT(DATE_SUB(Date, INTERVAL WEEKDAY(Date) + 1 DAY), GET_FORMAT(DATE, 'ISO'))\n            ELSE DATE_FORMAT(Date, GET_FORMAT(DATE, 'ISO'))\n        END AS week_start_date_in,\n        SUM(CASE WHEN Type IN ('Income', 'Cashback') THEN Amount ELSE 0 END) AS amount_in\n    FROM \n        transaction_standardised\n    GROUP BY \n        week_number_in, week_start_date_in\n),\nweekly_money_out AS (\n    SELECT\n        WEEK(Date, 2) AS week_number_out,\n        CASE\n            WHEN WEEKDAY(Date) &lt;> 6 \n                THEN DATE_FORMAT(DATE_SUB(Date, INTERVAL WEEKDAY(Date) + 1 DAY), GET_FORMAT(DATE, 'ISO'))\n            ELSE DATE_FORMAT(Date, GET_FORMAT(DATE, 'ISO'))\n        END AS week_start_date_out,\n        SUM(CASE WHEN Type = 'Expense' THEN Amount ELSE 0 END) AS amount_out\n    FROM \n        transaction_standardised\n    GROUP BY \n        week_number_out, week_start_date_out\n),\ncombined_result AS (\n    SELECT\n        combined.week_number_in,\n        combined.week_start_date_in,\n        combined.week_number_out,\n        combined.week_start_date_out,\n        ROUND(combined.amount_in, 2) AS amount_in,\n        ROUND(combined.amount_out, 2) AS amount_out,\n        ROUND(SUM(combined.amount_in - combined.amount_out) OVER (ORDER BY combined.week_start_date_in), 2) AS weekly_total_balance\n    FROM (\n        SELECT *\n        FROM weekly_money_in\n        LEFT JOIN weekly_money_out ON weekly_money_in.week_start_date_in = weekly_money_out.week_start_date_out\n        UNION\n        SELECT *\n        FROM weekly_money_in\n        RIGHT JOIN weekly_money_out ON weekly_money_in.week_start_date_in = weekly_money_out.week_start_date_out\n    ) AS combined\n),\npercentage_savings AS (\n    SELECT\n        week_start_date_in,\n        week_number_in,\n        amount_in,\n        amount_out,\n        weekly_total_balance,\n        ROUND(((weekly_total_balance - LAG(weekly_total_balance) OVER (ORDER BY week_start_date_in ASC)) \/ weekly_total_balance) * 100, 2) AS percentage_savings_per_week\n    FROM combined_result\n)\nSELECT\n    week_number_in as week_number,\n    week_start_date_in as week_start_date,\n    amount_in,\n    amount_out,\n    weekly_total_balance,\n    percentage_savings_per_week\nFROM percentage_savings\nORDER BY week_start_date_in ASC<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">WITH<\/span><span style=\"color: #D8DEE9FF\"> transaction_standardised <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        (<\/span><span style=\"color: #81A1C1\">CASE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> Currency <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">USD<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #D8DEE9FF\"> Rate.Rate) <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> Amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">Type<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">Transaction<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">LEFT JOIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        Rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        Transaction.Currency <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> Rate.TargetCurrency<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">weekly_money_in <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">WEEK<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> week_number_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">CASE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WEEKDAY<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">&lt;&gt;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">6<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DATE_FORMAT<\/span><span style=\"color: #D8DEE9FF\">(DATE_SUB(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, INTERVAL <\/span><span style=\"color: #81A1C1\">WEEKDAY<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">+<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DAY<\/span><span style=\"color: #D8DEE9FF\">), GET_FORMAT(<\/span><span style=\"color: #81A1C1\">DATE<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">ISO<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DATE_FORMAT<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, GET_FORMAT(<\/span><span style=\"color: #81A1C1\">DATE<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">ISO<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> week_start_date_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">CASE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">Type<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">IN<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Income<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Cashback<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> amount_in<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        transaction_standardised<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">GROUP BY<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        week_number_in, week_start_date_in<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">weekly_money_out <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">WEEK<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> week_number_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">CASE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WEEKDAY<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">&lt;&gt;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">6<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DATE_FORMAT<\/span><span style=\"color: #D8DEE9FF\">(DATE_SUB(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, INTERVAL <\/span><span style=\"color: #81A1C1\">WEEKDAY<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">+<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">1<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DAY<\/span><span style=\"color: #D8DEE9FF\">), GET_FORMAT(<\/span><span style=\"color: #81A1C1\">DATE<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">ISO<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">DATE_FORMAT<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">Date<\/span><span style=\"color: #D8DEE9FF\">, GET_FORMAT(<\/span><span style=\"color: #81A1C1\">DATE<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">ISO<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\">))<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> week_start_date_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #81A1C1\">CASE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">WHEN<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">Type<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #A3BE8C\">Expense<\/span><span style=\"color: #ECEFF4\">&#39;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">THEN<\/span><span style=\"color: #D8DEE9FF\"> Amount <\/span><span style=\"color: #81A1C1\">ELSE<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">END<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> amount_out<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        transaction_standardised<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">GROUP BY<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        week_number_out, week_start_date_out<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">combined_result <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        combined.week_number_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        combined.week_start_date_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        combined.week_number_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        combined.week_start_date_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">ROUND<\/span><span style=\"color: #D8DEE9FF\">(combined.amount_in, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> amount_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">ROUND<\/span><span style=\"color: #D8DEE9FF\">(combined.amount_out, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> amount_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">ROUND<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #88C0D0\">SUM<\/span><span style=\"color: #D8DEE9FF\">(combined.amount_in <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\"> combined.amount_out) <\/span><span style=\"color: #81A1C1\">OVER<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">ORDER BY<\/span><span style=\"color: #D8DEE9FF\"> combined.week_start_date_in), <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> weekly_total_balance<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_in<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">LEFT JOIN<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_out <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_in.week_start_date_in <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_out.week_start_date_out<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">UNION<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">SELECT<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_in<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">RIGHT JOIN<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_out <\/span><span style=\"color: #81A1C1\">ON<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_in.week_start_date_in <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> weekly_money_out.week_start_date_out<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    ) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> combined<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">percentage_savings <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        week_start_date_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        week_number_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        amount_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        amount_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        weekly_total_balance,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #88C0D0\">ROUND<\/span><span style=\"color: #D8DEE9FF\">(((weekly_total_balance <\/span><span style=\"color: #81A1C1\">-<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">LAG<\/span><span style=\"color: #D8DEE9FF\">(weekly_total_balance) <\/span><span style=\"color: #81A1C1\">OVER<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">ORDER BY<\/span><span style=\"color: #D8DEE9FF\"> week_start_date_in <\/span><span style=\"color: #81A1C1\">ASC<\/span><span style=\"color: #D8DEE9FF\">)) <\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #D8DEE9FF\"> weekly_total_balance) <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">100<\/span><span style=\"color: #D8DEE9FF\">, <\/span><span style=\"color: #B48EAD\">2<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #81A1C1\">AS<\/span><span style=\"color: #D8DEE9FF\"> percentage_savings_per_week<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> combined_result<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">SELECT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    week_number_in <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> week_number,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    week_start_date_in <\/span><span style=\"color: #81A1C1\">as<\/span><span style=\"color: #D8DEE9FF\"> week_start_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    amount_in,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    amount_out,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    weekly_total_balance,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    percentage_savings_per_week<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">FROM<\/span><span style=\"color: #D8DEE9FF\"> percentage_savings<\/span><\/span>\n<span class=\"line\"><span style=\"color: #81A1C1\">ORDER BY<\/span><span style=\"color: #D8DEE9FF\"> week_start_date_in <\/span><span style=\"color: #81A1C1\">ASC<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Recommend Cards Function<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><code>RecommendCards()<\/code>&nbsp;queries both the&nbsp;<code>Cashback<\/code>&nbsp;and&nbsp;<code>Point<\/code>&nbsp;tables for eligible rewards, then calculating them respectively and inserting into the&nbsp;<code>CashbackRecommendation<\/code>&nbsp;and&nbsp;<code>PointRecommendation<\/code>&nbsp;tables respectively.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>function RecommendCards(five: Five, context: any, result: FiveError) : FiveError {\n\n    const amount: number = Number(context.Amount);\n    \n    \/\/ Query tables\n    const sqlStatement1: string = `\n        SELECT CashbackKey, Percentage, Constant \n        FROM Cashback \n        WHERE \n            ? >= MinimumSpend AND Currency IN (?, \"Any\") AND \n            (\n                (Category = ? AND MerchantName = \"Any\") OR\n                (Category = \"Any\" AND MerchantName = ?)\n            )`;\n    const queryresults1: QueryResult = five.executeQuery(sqlStatement1, 0, \n        amount, \n        context.Currency, \n        context.Category, \n        context.MerchantName);\n\n    const sqlStatement2: string = `\n        SELECT PointKey, PointsPerUnit, Constant \n        FROM Point \n        WHERE \n            ? >= MinimumSpend AND Currency IN (?, \"Any\") AND \n            (\n                (Category = ? AND MerchantName = \"Any\") OR\n                (Category = \"Any\" AND MerchantName = ?)\n            )`;;\n    const queryresults2: QueryResult = five.executeQuery(sqlStatement2, 0, \n        amount, \n        context.Currency, \n        context.Category, \n        context.MerchantName);\n    \n    if (queryresults1.values === null &amp;&amp; queryresults2.values === null) {\n        return five.createError(queryresults2, \"No cards offer cashback or points for this spend\");\n    }\n\n    five.executeQuery(\"DELETE FROM CashbackRecommendation\", 0);\n    five.executeQuery(\"DELETE FROM PointRecommendation\", 0);\n\n    \/\/ Calculate cashback\n    if (queryresults1.values !== null) {\n        const insertSqlStatement: string = `\n            INSERT INTO CashbackRecommendation \n            (\n                CashbackRecommendationKey,\n                CashbackKey,\n                Category,\n                MerchantName,\n                Amount,\n                Currency,\n                CashbackAmount\n            ) VALUES (?, ?, ?, ?, ?, ?, ?)\n        `\n\n        for (let i = 0; i &lt; queryresults1.values.length; i++) {\n            const row = queryresults1.values&#091;i&#093;;\n            const percentage: number = Number(row.Percentage);\n            const constant: number = Number(row.Constant);\n\n            const cashback: number = amount * percentage \/ 100.0 + constant;\n\n            five.executeQuery(insertSqlStatement, 0, \n                five.uuid(), \n                row.CashbackKey, \n                context.Category,\n                context.MerchantName,\n                amount, \n                context.Currency,\n                cashback\n            );\n        }\n    }\n    \n    \/\/ Calculate points\n    if (queryresults2.values !== null) {\n        const insertSqlStatement: string = `\n            INSERT INTO PointRecommendation \n            (\n                PointRecommendationKey,\n                PointKey,\n                Category,\n                MerchantName,\n                Amount,\n                Currency,\n                Point\n            ) VALUES (?, ?, ?, ?, ?, ?, ?)\n        `\n\n        for (let i = 0; i &lt; queryresults2.values.length; i++) {\n            const row = queryresults2.values&#091;i&#093;;\n            const pointsPerUnit: number = Number(row.PointsPerUnit);\n            const constant: number = Number(row.Constant);\n\n            const points: number = amount * pointsPerUnit + constant;\n\n            five.executeQuery(insertSqlStatement, 0, \n                five.uuid(), \n                row.PointKey, \n                context.Category,\n                context.MerchantName,\n                amount, \n                context.Currency,\n                points\n            );\n        }\n    }\n    \n    return five.success(result);\n}<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">function<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">RecommendCards<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> Five<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">any<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">result<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> FiveError<\/span><span style=\"color: #ECEFF4\">)<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> FiveError <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">Number<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Amount<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">    <\/span><span style=\"color: #616E88\">\/\/ Query tables<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">sqlStatement1<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">string<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        SELECT CashbackKey, Percentage, Constant <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        FROM Cashback <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        WHERE <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            ? &gt;= MinimumSpend AND Currency IN (?, &quot;Any&quot;) AND <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                (Category = ? AND MerchantName = &quot;Any&quot;) OR<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                (Category = &quot;Any&quot; AND MerchantName = ?)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            )<\/span><span style=\"color: #ECEFF4\">`<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults1<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> QueryResult <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">sqlStatement1<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Currency<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Category<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">MerchantName<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">sqlStatement2<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">string<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        SELECT PointKey, PointsPerUnit, Constant <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        FROM Point <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        WHERE <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            ? &gt;= MinimumSpend AND Currency IN (?, &quot;Any&quot;) AND <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                (Category = ? AND MerchantName = &quot;Any&quot;) OR<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                (Category = &quot;Any&quot; AND MerchantName = ?)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            )<\/span><span style=\"color: #ECEFF4\">`<\/span><span style=\"color: #81A1C1\">;;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults2<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> QueryResult <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">sqlStatement2<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Currency<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Category<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">MerchantName<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">if<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #D8DEE9\">queryresults1<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">===<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">&amp;&amp;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults2<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">===<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">return<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">createError<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">queryresults2<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">No cards offer cashback or points for this spend<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #ECEFF4\">}<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">DELETE FROM CashbackRecommendation<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">DELETE FROM PointRecommendation<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">    <\/span><span style=\"color: #616E88\">\/\/ Calculate cashback<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">if<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #D8DEE9\">queryresults1<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">!==<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">insertSqlStatement<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">string<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            INSERT INTO CashbackRecommendation <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                CashbackRecommendationKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                CashbackKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                MerchantName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Currency,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                CashbackAmount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            ) VALUES (?, ?, ?, ?, ?, ?, ?)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">let<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #81A1C1\">;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">&lt;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults1<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9FF\">length<\/span><span style=\"color: #81A1C1\">;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #81A1C1\">++<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults1<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #D8DEE9FF\">&#091;<\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #D8DEE9FF\">&#093;<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">percentage<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">Number<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Percentage<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">constant<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">Number<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Constant<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">cashback<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">percentage<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">\/<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">100.0<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">+<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">constant<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">insertSqlStatement<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">uuid<\/span><span style=\"color: #D8DEE9FF\">()<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">CashbackKey<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Category<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">MerchantName<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Currency<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">cashback<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            )<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #ECEFF4\">}<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #ECEFF4\">}<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">    <\/span><span style=\"color: #616E88\">\/\/ Calculate points<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">if<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #D8DEE9\">queryresults2<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">!==<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">insertSqlStatement<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">string<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            INSERT INTO PointRecommendation <\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                PointRecommendationKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                PointKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Category,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                MerchantName,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Amount,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Currency,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">                Point<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            ) VALUES (?, ?, ?, ?, ?, ?, ?)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">for<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #81A1C1\">let<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #81A1C1\">;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">&lt;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults2<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9FF\">length<\/span><span style=\"color: #81A1C1\">;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #81A1C1\">++<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">queryresults2<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">values<\/span><span style=\"color: #D8DEE9FF\">&#091;<\/span><span style=\"color: #D8DEE9\">i<\/span><span style=\"color: #D8DEE9FF\">&#093;<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">pointsPerUnit<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">Number<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">PointsPerUnit<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">constant<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">Number<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Constant<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">points<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">number<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">*<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">pointsPerUnit<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">+<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">constant<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">insertSqlStatement<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">uuid<\/span><span style=\"color: #D8DEE9FF\">()<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">row<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">PointKey<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Category<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">MerchantName<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">amount<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">Currency<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">                <\/span><span style=\"color: #D8DEE9\">points<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            )<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #ECEFF4\">}<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #ECEFF4\">}<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">return<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">success<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">result<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\"><strong>Get Rates Function<\/strong><\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><code>GetRates()<\/code>&nbsp;retrieves all exchange rates from US Dollar (USD) from ExchangeRate-API\u2019s Standard API and stores them in the&nbsp;<code>Rate<\/code>&nbsp;table.<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#2e3440ff\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#d8dee9ff;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>function GetRates(five: Five, context: any, result: FiveError) : FiveError {\n    const API_URL = `https:\/\/v6.exchangerate-api.com\/v6\/&lt;API_KEY>\/latest\/USD`\n    const client = five.httpClient();\n    let httpResult = client.get(API_URL);\n\n    if (httpResult.response === null) {\n        return five.createError(httpResult, \"Error in retrieving rates\");\n    }\n\n    const rates = httpResult.response.conversion_rates;\n\n    const insertSqlStatement: string = `\n        INSERT INTO Rate\n        (\n            RateKey,\n            BaseCurrency,\n            TargetCurrency,\n            Rate\n        ) VALUES (?, ?, ?, ?)\n    `\n\n    Object.keys(rates).forEach(targetCurrency => {\n        five.executeQuery(insertSqlStatement, 0, \n            five.uuid(), \n            \"USD\", \n            targetCurrency,\n            rates&#091;targetCurrency&#093;\n        );\n    })\n\n    return five.success(result, \"Rates refreshed\");\n}<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki nord\" style=\"background-color: #2e3440ff\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #81A1C1\">function<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #88C0D0\">GetRates<\/span><span style=\"color: #ECEFF4\">(<\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> Five<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">context<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">any<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">result<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> FiveError<\/span><span style=\"color: #ECEFF4\">)<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> FiveError <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">API_URL<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><span style=\"color: #A3BE8C\">https:\/\/v6.exchangerate-api.com\/v6\/&lt;API_KEY&gt;\/latest\/USD<\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">client<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">httpClient<\/span><span style=\"color: #D8DEE9FF\">()<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">let<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">httpResult<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">client<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">get<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">API_URL<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">if<\/span><span style=\"color: #D8DEE9FF\"> (<\/span><span style=\"color: #D8DEE9\">httpResult<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">response<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">===<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">null<\/span><span style=\"color: #D8DEE9FF\">) <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #81A1C1\">return<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">createError<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">httpResult<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">Error in retrieving rates<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #ECEFF4\">}<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">rates<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">httpResult<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">response<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #D8DEE9\">conversion_rates<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">const<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">insertSqlStatement<\/span><span style=\"color: #81A1C1\">:<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">string<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        INSERT INTO Rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            RateKey,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            BaseCurrency,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            TargetCurrency,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">            Rate<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">        ) VALUES (?, ?, ?, ?)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #A3BE8C\">    <\/span><span style=\"color: #ECEFF4\">`<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #D8DEE9\">Object<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">keys<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">rates<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">forEach<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">targetCurrency<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #81A1C1\">=&gt;<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">{<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">executeQuery<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">insertSqlStatement<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #B48EAD\">0<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">uuid<\/span><span style=\"color: #D8DEE9FF\">()<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">USD<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #D8DEE9\">targetCurrency<\/span><span style=\"color: #ECEFF4\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">            <\/span><span style=\"color: #D8DEE9\">rates<\/span><span style=\"color: #D8DEE9FF\">&#091;<\/span><span style=\"color: #D8DEE9\">targetCurrency<\/span><span style=\"color: #D8DEE9FF\">&#093;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">        )<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #ECEFF4\">}<\/span><span style=\"color: #D8DEE9FF\">)<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #D8DEE9FF\">    <\/span><span style=\"color: #81A1C1\">return<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #D8DEE9\">five<\/span><span style=\"color: #ECEFF4\">.<\/span><span style=\"color: #88C0D0\">success<\/span><span style=\"color: #D8DEE9FF\">(<\/span><span style=\"color: #D8DEE9\">result<\/span><span style=\"color: #ECEFF4\">,<\/span><span style=\"color: #D8DEE9FF\"> <\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #A3BE8C\">Rates refreshed<\/span><span style=\"color: #ECEFF4\">&quot;<\/span><span style=\"color: #D8DEE9FF\">)<\/span><span style=\"color: #81A1C1\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ECEFF4\">}<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><em>BudgetHub&nbsp;<\/em><\/strong><em>is jointly developed by&nbsp;<\/em><a href=\"https:\/\/www.linkedin.com\/in\/jiahwee-wong\/\" target=\"_blank\" rel=\"noreferrer noopener nofollow\"><em>Jia Hwee Wong<\/em><\/a><em>&nbsp;and&nbsp;<\/em><a href=\"https:\/\/www.linkedin.com\/in\/ngrq94\/\" target=\"_blank\" rel=\"noreferrer noopener nofollow\"><em>Rui Qin Ng<\/em><\/a><em>&nbsp;on Five version 2.1.0.<\/em><\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\">\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Rapid Application Development<\/strong>\n<span style=\"font-size: 14pt;\">Download Five for Free and Start Developing<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Free Sign Up<\/strong><\/button><br><\/a><\/p>\n<hr style=\"height: 5px;\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Five Hackathon 2023: Meet the Winners and The BudgetHub Web App Congratulations to Rui Qin Ng and Jia Hwee Wong from Singapore for winning Five&#8217;s 2023 Low-Code Hackathon! Rui Qin is a Technical Solutions Architect at Mastercard with a Bachelor of Science in Information Systems and Operations Analytics from the Singapore Management University. Jia Hwee [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":1008,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-1007","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners.png",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners-1024x576.png",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners.png",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2023\/07\/Five.Co-Low-Code-Hackathon-2023-Meet-The-Winners.png",1280,720,false]},"uagb_author_info":{"display_name":"Dominik Keller","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed3_my\/"},"uagb_comment_info":0,"uagb_excerpt":"Five Hackathon 2023: Meet the Winners and The BudgetHub Web App Congratulations to Rui Qin Ng and Jia Hwee Wong from Singapore for winning Five&#8217;s 2023 Low-Code Hackathon! Rui Qin is a Technical Solutions Architect at Mastercard with a Bachelor of Science in Information Systems and Operations Analytics from the Singapore Management University. Jia Hwee&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/1007","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=1007"}],"version-history":[{"count":2,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/1007\/revisions"}],"predecessor-version":[{"id":17882,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/1007\/revisions\/17882"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/1008"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=1007"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=1007"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=1007"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}