Last active
July 2, 2020 15:08
-
-
Save PedroMartinSteenstrup/8be2a238e908c879c4f7674d7cbddb8a to your computer and use it in GitHub Desktop.
get the savings of a specific year for an estimation of a TransferWise saving
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment