get the savings of a specific year for an estimation of a TransferWise saving

savings_year.sql

use role "pedro.martin@transferwise.com";

CREATE OR REPLACE table ANALYST_SANDBOX.year_savings AS (
    WITH comparison_data AS (
        SELECT lookup_countries.CODE_2CHAR                                         AS comparison_country,
               avg(((FEES / AMOUNT) + ((MIDMARKET_RATE - RATE) / MIDMARKET_RATE))) AS comparison_total_pct
        FROM reports.LOOKUP_COUNTRIES AS lookup_countries
                 INNER JOIN comparison.rates AS rates ON rates.source_country = lookup_countries.code_3char
                 LEFT JOIN COMPARISON.PROVIDERS On providers.ID = rates.PROVIDER_ID
            AND rates.date_collected::DATE BETWEEN '2019-04-01' AND '2020-03-31'
            AND rates.AMOUNT between '50' AND '10000'
        group by 1
    ),

         saving_coefficient AS (
             SELECT IFNULL(lookup_bank_name.bank_country, IFNULL(countries.TWODIGIT, profile.TWODIGIT)) AS country,
                    SUM(ras.invoice_value_gbp)                                                          AS conversion_volume_gbp,
                    SUM(ras.fee_value_gbp)                                                              AS conversion_revenue_gbp
             FROM reports.report_action_step AS ras
                      LEFT JOIN reports.lookup_bank_name AS lookup_bank_name
                                ON ras.request_id = lookup_bank_name.request_id
                      LEFT JOIN (select TWODIGIT,
                                        ccy
                                 from REPORTS.LOOKUP_COUNTRY_AND_CURRENCY_CODES
                                 where ccy NOT IN ('EUR', 'XCD', 'AUD', 'XOF', 'XAF', 'USD', 'CHF')
                                 group by 1, 2) countries
                                ON UPPER(ras.SOURCE_CURRENCY) = UPPER(countries.ccy)
                      LEFT JOIN (select lccc.TWODIGIT,
                                        up.ID
                                 from FX.USER_PROFILE up
                                          LEFT JOIN FX.USER_PROFILE_ADDRESS upa ON upa.ID = up.address_id
                                          LEFT JOIN reports.LOOKUP_COUNTRY_AND_CURRENCY_CODES lccc
                                                    ON upper(lccc.THREEDIGIT) = upper(upa.COUNTRY_CODE)) profile
                                on profile.id = ras.USER_PROFILE_ID
             WHERE 1 = 1
               AND ras.action_completion_time::DATE BETWEEN '2019-04-01' AND '2020-03-31'
               AND ras.not_duplicate = 1
               AND ras.product_type = 'SENDMONEY'
               AND ras.action_state = 'TRANSFERRED'
               AND ras.not_test_account = 1
               AND ras.successful_action = 1
               AND ras.source_currency != ras.target_currency
             GROUP BY 1
             ORDER BY 2 DESC
         ),

         pr_calculation AS (
             SELECT saving_coefficient.country                                            country,
                    (((AVG(comparison_data.comparison_total_pct)) -
                      ((COALESCE(SUM(saving_coefficient.conversion_revenue_gbp), 0)) /
                       (COALESCE(SUM(saving_coefficient.conversion_volume_gbp), 0))))) AS savings_factor
             FROM saving_coefficient
                      LEFT JOIN comparison_data ON comparison_data.comparison_country = saving_coefficient.country
             WHERE 1 = 1
               AND comparison_country IN
                   ('CA', 'FR', 'GB', 'IT', 'NZ', 'ES', 'AU', 'US', 'DE', 'BR', 'PT', 'MX', 'HU', 'RU', 'PH', 'TH',
                    'NO', 'TW', 'CZ', 'IN', 'MY', 'DK', 'CH', 'SE', 'IL', 'IE', 'NL')
             GROUP BY 1
         ),

         carlito_savings AS (
             SELECT IFF(split_part(AMOUNT_IN_GBP_BUCKET, '-', 1) = '50000+', 50000,
                        split_part(AMOUNT_IN_GBP_BUCKET, '-', 1))::number amount_lo,
                    IFF(split_part(AMOUNT_IN_GBP_BUCKET, '-', 1) = '50000+', 5000000,
                        split_part(AMOUNT_IN_GBP_BUCKET, '-', 2))::number amount_up,
                    TWODIGIT                                              country,
                    comp_price_pct - price_pct                            savings_factor
             FROM (
                      select s.AMOUNT_IN_GBP_BUCKET,
                             lccc.TWODIGIT,
                             avg(round(s.FEE_VALUE_GBP / s.INVOICE_VALUE_GBP, 4))                              price_pct,
                             avg(
                                     (s.AMOUNT_SAVED_GBP_BY_ROUTE + s.FEE_VALUE_GBP) / s.INVOICE_VALUE_GBP) AS comp_price_pct
                      FROM ANALYST_SANDBOX.CARL_S_SAVINGS_20200422 s
                               LEFT JOIN reports.LOOKUP_COUNTRY_AND_CURRENCY_CODES lccc
                                         ON upper(lccc.CCY) = upper(s.SOURCE_CURRENCY)
                      WHERE lccc.CCY NOT IN ('EUR', 'USD', 'XAF', 'XOF', 'XCD')
                      group by 1, 2)
             WHERE comp_price_pct is not null
         )

    SELECT ras.REQUEST_ID,
           ras.INVOICE_VALUE_GBP,
           s.AMOUNT_SAVED_GBP_BY_ROUTE,
           cs.savings_factor * ras.INVOICE_VALUE_GBP as comp_savings
    FROM REPORTS.REPORT_ACTION_STEP ras
             LEFT JOIN PROFILE.ADDRESS a ON a.USER_PROFILE_ID = ras.USER_PROFILE_ID
             LEFT JOIN reports.LOOKUP_COUNTRY_AND_CURRENCY_CODES lcprofile
                       ON upper(lcprofile.THREEDIGIT) = upper(a.COUNTRY_CODE)
             LEFT JOIN ANALYST_SANDBOX.CARL_S_SAVINGS_20200422 s ON s.REQUEST_ID = ras.REQUEST_ID
             LEFT JOIN REPORTS.lookup_sender_bank_name bank ON bank.TRANSFER_ID = ras.request_id
             LEFT JOIN (
        select CASE
                   WHEN CCY = 'AUD' THEN 'AU'
                   WHEN CCY = 'USD' THEN 'US'
                   WHEN CCY = 'GBP' THEN 'GB'
                   WHEN CCY = 'CHF' THEN 'CH'
                   WHEN CCY = 'DKK' THEN 'DK'
                   WHEN CCY = 'NZD' THEN 'NZ'
                   ELSE TWODIGIT END   as twodigit,
               CASE
                   WHEN CCY = 'AUD' THEN 'AUS'
                   WHEN CCY = 'USD' THEN 'USA'
                   WHEN CCY = 'GBP' THEN 'GBR'
                   WHEN CCY = 'CHF' THEN 'CHE'
                   WHEN CCY = 'DKK' THEN 'DNK'
                   WHEN CCY = 'NZD' THEN 'NZL'
                   ELSE THREEDIGIT END as THREEDIGIT,
               CCY
        from reports.LOOKUP_COUNTRY_AND_CURRENCY_CODES
        group by 1, 2, 3) lcbank
                       ON upper(lcbank.THREEDIGIT) = upper(bank.BANK_COUNTRY)
             LEFT JOIN pr_calculation pr ON pr.country = UPPER(lcbank.TWODIGIT)
             LEFT JOIN pr_calculation prpro ON UPPER(prpro.country) = UPPER(lcprofile.TWODIGIT)
             LEFT JOIN carlito_savings cs ON UPPER(cs.country) = UPPER(lcbank.TWODIGIT)
        AND ras.INVOICE_VALUE_GBP BETWEEN amount_lo AND amount_up
        AND ras.INVOICE_VALUE_GBP
    where 1 = 1
      AND ras.PRODUCT_TYPE = 'SENDMONEY'
      AND ras.ACTION_COMPLETION_TIME::DATE BETWEEN '2019-04-01' AND '2020-03-31'
      AND FLAG_FOR_AGGREGATIONS = 1
      AND ras.SOURCE_CURRENCY != ras.TARGET_CURRENCY
    group by 1, 2, 3, 4
);

CREATE OR REPLACE TABLE ANALYST_SANDBOX.year_savings_summary AS (
    SELECT SOURCE_CURRENCY,
           count(distinct REQUEST_ID)                                                        transfers_considered,
           count(distinct
                 IFF(COALESCE(AMOUNT_SAVED_GBP_BY_ROUTE, comp_savings) is not null, request_id_savings, NULL)) /
           transfers_considered                                                              percent_transfers_with_savings,
           sum(INVOICE_VALUE_GBP)                                                            volume_considered,
           ZEROIFNULL(SUM(invoice_value_gbp_savings) / volume_considered)                                percent_volume_with_savings,
           ZEROIFNULL(sum(COALESCE(AMOUNT_SAVED_GBP_BY_ROUTE, comp_savings)))                            savings,
           savings +
           ((volume_considered - (volume_considered * ZEROIFNULL(percent_volume_with_savings))) * 0.025) extra_savings_with_default
    FROM (
             select ras.REQUEST_ID,
                    ras.SOURCE_CURRENCY,
                    ys.REQUEST_ID                                                        request_id_savings,
                    ras.INVOICE_VALUE_GBP,
                    IFF(COALESCE(AMOUNT_SAVED_GBP_BY_ROUTE,
                                 comp_savings) is not null, ras.INVOICE_VALUE_GBP, NULL) invoice_value_gbp_savings,
                    AMOUNT_SAVED_GBP_BY_ROUTE,
                    comp_savings
             from REPORTS.REPORT_ACTION_STEP ras
                      LEFT JOIN ANALYST_SANDBOX.year_savings ys ON ys.REQUEST_ID = ras.REQUEST_ID
             WHERE 1 = 1
               AND ras.PRODUCT_TYPE = 'SENDMONEY'
               AND ras.ACTION_COMPLETION_TIME::DATE BETWEEN '2019-04-01' AND '2020-03-31'
               AND FLAG_FOR_AGGREGATIONS = 1
               AND ras.SOURCE_CURRENCY != ras.TARGET_CURRENCY
             GROUP BY 1, 2, 3, 4, 5, 6, 7) sub
    group by 1
);

grant select ON TABLE ANALYST_SANDBOX.year_savings to role GRP_STATS;
grant select ON TABLE ANALYST_SANDBOX.year_savings_summary to role GRP_STATS;

select * from ANALYST_SANDBOX.year_savings_summary;