View significance.sql
create or replace function
public.significance(control_size integer,
control_conversion integer,
experiment_size integer,
experiment_conversion integer)
returns float
stable as $$
from scipy.stats import norm
def standard_error(sample_size, successes):
View rollup.sql
daily AS (
SELECT
experiment_arm.day,
experiment_arm.experiment_test,
experiment_arm.cumulative_exposures :: INT experiment_cumulative_exposures,
experiment_arm.cumulative_order_placed_1 :: INT experiment_cumulative_conversions,
control_arm.cumulative_exposures :: INT control_cumulative_exposures,
control_arm.cumulative_order_placed_1 :: INT control_cumulative_conversions
View cumulative results.sql
cumulative AS (
SELECT
DAY,
experiment_test,
experiment_result,
exposures,
sum(exposures)
OVER (
PARTITION BY experiment_test, experiment_result
ORDER BY DAY
View daily results.sql
results AS (
SELECT
event,
experiment_test,
experiment_result,
day,
count(DISTINCT first_exposures.tracking_id) exposures,
count(DISTINCT conversions_order_placed_1.tracking_id) order_placed_1,
count(DISTINCT conversions_order_placed_1.tracking_id) /
count(DISTINCT first_exposures.tracking_id) :: FLOAT conversion_rate
View exposure and conversion.sql
WITH first_exposures AS (
SELECT
tracking_id,
created_at,
event,
experiment_test,
experiment_result,
date_trunc('day', created_at) AS day
FROM warehouse.events
WHERE category = 'experiment'
View example_event_schema.sql
CREATE TABLE events (
event_guid CHARACTER VARYING(128),
tracking_id CHARACTER VARYING(128),
category CHARACTER VARYING(128) encode TEXT255,
sub_category CHARACTER VARYING(128),
event CHARACTER VARYING(128) encode TEXT255,
event_name CHARACTER VARYING(128) encode TEXT255,
value_decimal NUMERIC(38, 6),
value_string CHARACTER VARYING(255),
created_at TIMESTAMP WITHOUT TIME ZONE,
View churn.sql
WITH monthly_usage AS (
SELECT
user_id,
date_part('month', age(created_at, '1970-01-01')) +
12 * date_part('year', age(created_at, '1970-01-01')) AS time_period
FROM orders
WHERE order_state = 'completed'
GROUP BY 1, 2
ORDER BY 1, 2)
View activation funnel.sql
SELECT
date_trunc('day', page.timestamp)
count(distinct page.anonymous_id),
count(distinct conversions.user_id)
FROM pages page
JOIN identifies ids
LEFT JOIN conversions ON conversions.user_id = identifies.user_id
WHERE
ids.anonymous_id = page.anonymous_id
AND completes.user_id = ids.user_id
View create.sql
create table events (
user_id int
date date_time
event text
)
View 4.sql
calculated as (select time_period,
case when lag is null then 'NEW'
when lag_size = 1 then 'ACTIVE'
when lag_size > 1 then 'RETURN'
end as this_month_value,
case when (lead_size > 1 OR lead_size IS NULL) then 'CHURN'
else NULL
end as next_month_churn,