superfuji57

library(stringi)
library(tidyverse)

# parameters for dataset
number_of_users <- 50000
max_number_of_visits <- 5
conversion_rate <- .073

set.seed(57)    

superfuji57

WITH first_order AS (
SELECT
    deliveries.user_id
    , MIN(deliveries.ship_date) AS first_ship_date
FROM freshly_backend_stitch.deliveries
INNER JOIN freshly_backend_stitch.weekly_orders ON deliveries.user_id = weekly_orders.user_id
    AND weekly_orders.status = 'paid'
WHERE deliveries.status in ('shipped', 'in_production')
GROUP BY 1
)

superfuji57

SELECT 
  weekly_orders.marked_paid_on
  , promo_codes.code  AS promo_code
  , weekly_orders.user_id  
  , weekly_orders.subscription_id
  , weekly_orders.id AS weekly_order_id
  , deliveries.id AS delivery_id
  , weekly_orders.start_date AS order_week
  , deliveries.ship_date AS ship_date
  , deliveries.target_date AS target_date