PedroMartinSteenstrup

Keybase proof

I hereby claim:

• I am pedromartinsteenstrup on github.
• I am pedrofief (https://keybase.io/pedrofief) on keybase.
• I have a public key ASDTw4Mbe8ooXi8UsqJoB4ejbNyKYpTiy7AO1O_MEdT8Owo

To claim this, I am signing this object:

PedroMartinSteenstrup

Ansible playbook to setup HTTPS using Let's encrypt on nginx.

The Ansible playbook installs everything needed to serve static files from a nginx server over HTTPS.
The server pass A rating on [SSL Labs](https://www.ssllabs.com/).

To use:
 1. Install [Ansible](https://www.ansible.com/)
 2. Setup an Ubuntu 16.04 server accessible over ssh
 3. Create `/etc/ansible/hosts` according to template below and change example.com to your domain
 4. Copy the rest of the files to an empty directory (`playbook.yml` in the root of that folder and the rest in the `templates` subfolder)

PedroMartinSteenstrup

import boto3

from analytics_utils.config import config_2


class EMR:

    def __init__(self, **kwargs):

        self.config_object = getattr(config_2, 'EMRUtils')(**kwargs)

PedroMartinSteenstrup

########## HomeBrew ##########
## Check if exists

command -v brew >/dev/null 2>&1 || { echo "Installing Homebrew.."
  /bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
  } >&2;
echo "Homebrew successfully installed"

########## SSH ##########

PedroMartinSteenstrup

# --------------------------------------
#       Snowflake OAuth
# --------------------------------------

SNOWFLAKE_ACCOUNT = os.getenv('SNOWFLAKE_ACCOUNT') or None

if SNOWFLAKE_ACCOUNT:
    SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_ID = os.getenv('SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_ID')
    SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_SECRET = os.getenv('SNOWFLAKE_SECURITY_INTEGRATION_CLIENT_SECRET')
    SNOWFLAKE_OAUTH_AUTHORIZATION_ENDPOINT = f'https://{SNOWFLAKE_ACCOUNT}.snowflakecomputing.com/oauth/authorize'

PedroMartinSteenstrup

CREATE OR REPLACE TABLE DBUSERS (
NAME VARCHAR,
CREATED_ON TIMESTAMP_LTZ,
LOGIN_NAME VARCHAR,
DISPLAY_NAME VARCHAR,
FIRST_NAME VARCHAR,
LAST_NAME VARCHAR,
EMAIL VARCHAR,
MINS_TO_UNLOCK VARCHAR,
DAYS_TO_EXPIRY VARCHAR,

PedroMartinSteenstrup

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'

PedroMartinSteenstrup

USE ROLE AP_TOOLS;

-- CAVEAT Nr 1: Snowflake only gets the history_id from Looker passed as context, so we can link to a user efficiently.
-- -- -- -- -- -- This is then linked to workday's data
-- CAVEAT Nr 2: Looker's history_id can have several Snowflake queries linked to, who each have a cost associated to.
-- -- -- -- -- -- therefore there is a danger of double counting. History is augmented by Looker data.
-- CAVEAT Nr 3: Workday's data seems to not systematically have the cost center input, though it makes little sense
-- -- -- -- -- -- some departments might be under-represented until we figure out why it's missing
-- CAVEAT Nr 4: Snowflake credits seem to be off compared to the rest of the figures
-- -- -- -- -- -- might have to tweak the calculation

PedroMartinSteenstrup

WITH viralit AS (
    Select host_id,
           count(distinct IFF(DATE_SIGNED_UP is not null, GUEST_ID, NULL))   count_signups,
           count(distinct IFF(DATE_TRANSFERRED is not null, GUEST_ID, NULL)) count_mnus
    from virality.referral
    GROUP BY 1),
     nps AS (SELECT bnps.user_id,
                    bnps.nps_score AS                                                       nps_score,
                    bnps.respond_date,
                    rank() over (partition by bnps.USER_ID order by bnps.RESPOND_DATE desc) rank