Final_Assignment_AB_Testing.sql

--We are running an experiment at an item-level, which means all users who visit will see the same page, but the layout of different item pages may differ.
--Compare this table to the assignment events we captured for user_level_testing.
--Does this table have everything you need to compute metrics like 30-day view-binary?
SELECT
  *
FROM
  dsv1069.final_assignments_qa

--Reformat the final_assignments_qa to look like the final_assignments table, filling in any missing values with a placeholder of the appropriate data type.

SELECT
  item_id,
  test_a AS test_assignment,
  (
    CASE
      WHEN test_a IS NOT NULL THEN 'test_a'
      ELSE NULL
    END
  ) AS test_number,
  (
    CASE
      WHEN test_a IS NOT NULL THEN '2013-01-05 00:00:00'
      ELSE NULL
    END
  ) AS date
FROM
  dsv1069.final_assignments_qa
UNION
SELECT
  item_id,
  test_b AS test_assignment,
  (
    CASE
      WHEN test_b IS NOT NULL THEN 'test_b'
      ELSE NULL
    END
  ) AS test_number,
  (
    CASE
      WHEN test_b IS NOT NULL THEN '2013-01-05 00:00:00'
      ELSE NULL
    END
  ) AS date
FROM
  dsv1069.final_assignments_qa
UNION
SELECT
  item_id,
  test_c AS test_assignment,
  (
    CASE
      WHEN test_c IS NOT NULL THEN 'test_c'
      ELSE NULL
    END
  ) AS test_number,
  (
    CASE
      WHEN test_c IS NOT NULL THEN '2013-01-05 00:00:00'
      ELSE NULL
    END
  ) AS date
FROM
  dsv1069.final_assignments_qa
UNION
SELECT
  item_id,
  test_d AS test_assignment,
  (
    CASE
      WHEN test_d IS NOT NULL THEN 'test_d'
      ELSE NULL
    END
  ) AS test_number,
  (
    CASE
      WHEN test_d IS NOT NULL THEN '2013-01-05 00:00:00'
      ELSE NULL
    END
  ) AS date
FROM
  dsv1069.final_assignments_qa
UNION
SELECT
  item_id,
  test_e AS test_assignment,
  (
    CASE
      WHEN test_e IS NOT NULL THEN 'test_e'
      ELSE NULL
    END
  ) AS test_number,
  (
    CASE
      WHEN test_e IS NOT NULL THEN '2013-01-05 00:00:00'
      ELSE NULL
    END
  ) AS date
FROM
  dsv1069.final_assignments_qa
UNION
SELECT
  item_id,
  test_f AS test_assignment,
  (
    CASE
      WHEN test_f IS NOT NULL THEN 'test_f'
      ELSE NULL
    END
  ) AS test_number,
  (
    CASE
      WHEN test_f IS NOT NULL THEN '2013-01-05 00:00:00'
      ELSE NULL
    END
  ) AS date
FROM
  dsv1069.final_assignments_qa
ORDER BY
  test_number
LIMIT
  100;

-- compute order_binary for the 30 day window after the test_start_date
-- for the test named item_test_2
SELECT
  test_assignment,
  COUNT(DISTINCT item_id) AS number_of_items,
  SUM(order_binary) AS items_ordered_30d
FROM
  (
    SELECT
      item_test_2_order.item_id,
      item_test_2_order.test_assignment,
      item_test_2_order.test_number,
      item_test_2_order.test_start_date,
      item_test_2_order.created_at,
      MAX(
        CASE
          WHEN (
            created_at > test_start_date
            AND DATE_PART('day', created_at - test_start_date) <= 30
          ) THEN 1
          ELSE 0
        END
      ) AS order_binary
    FROM
      (
        SELECT
          final_assignments.*,
          DATE(orders.created_at) AS created_at
        FROM
          dsv1069.final_assignments AS final_assignments
          LEFT JOIN dsv1069.orders AS orders ON final_assignments.item_id = orders.item_id
        WHERE
          test_number = 'item_test_2'
      ) AS item_test_2_order
    GROUP BY
      item_test_2_order.item_id,
      item_test_2_order.test_assignment,
      item_test_2_order.test_number,
      item_test_2_order.test_start_date,
      item_test_2_order.created_at
  ) AS order_binary
GROUP BY
  test_assignment;

-- Use the final_assignments table to calculate the view binary, and
-- average views for the 30 day window after the test assignment for
-- item_test_2. (You may include the day the test started)

SELECT item_test_2.item_id,
       item_test_2.test_assignment,
       item_test_2.test_number,
       MAX(CASE
               WHEN (view_date > test_start_date
                     AND DATE_PART('day', view_date - test_start_date) <= 30) THEN 1
               ELSE 0
           END) AS view_binary
FROM
  (SELECT final_assignments.*,
          DATE(events.event_time) AS view_date
   FROM dsv1069.final_assignments AS final_assignments
   LEFT JOIN
       (SELECT event_time,
               CASE
                   WHEN parameter_name = 'item_id' THEN CAST(parameter_value AS NUMERIC)
                   ELSE NULL
               END AS item_id
      FROM dsv1069.events
      WHERE event_name = 'view_item') AS events
     ON final_assignments.item_id = events.item_id
   WHERE test_number = 'item_test_2') AS item_test_2
GROUP BY item_test_2.item_id,
         item_test_2.test_assignment,
         item_test_2.test_number
LIMIT 100;

--Use the https://thumbtack.github.io/abba/demo/abba.html to compute the lifts in metrics and the p-values for the binary metrics ( 30 day order binary and 30 day view binary) using a interval 95% confidence. 
SELECT
  test_assignment,
  test_number,
  COUNT(DISTINCT item) AS number_of_items,
  SUM(view_binary_30d) AS view_binary_30d
FROM
  (
    SELECT
      final_assignments.item_id AS item,
      test_assignment,
      test_number,
      test_start_date,
      MAX(
        (
          CASE
            WHEN date(event_time) - date(test_start_date) BETWEEN 0
            AND 30 THEN 1
            ELSE 0
          END
        )
      ) AS view_binary_30d
    FROM
      dsv1069.final_assignments
      LEFT JOIN dsv1069.view_item_events ON final_assignments.item_id = view_item_events.item_id
    WHERE
      test_number = 'item_test_2'
    GROUP BY
      final_assignments.item_id,
      test_assignment,
      test_number,
      test_start_date
  ) AS view_binary
GROUP BY
  test_assignment,
  test_number,
  test_start_date;