FBS popoulation.sql

WITH fulfillment_method_classification AS (
    SELECT
      f._shop_key,
      f."order id",
      f."shop shipping country",
      f."fulfillment id",
      CASE
      WHEN ofsd."is shopify shipping fulfillment" = 'Fulfilled via Shopify Shipping'
        THEN 'Shopify Shipping'
      WHEN _api_client_key = -2
        THEN 'Manual'
      WHEN lower(name) = 'unknown name' AND "is deleted" = 'Is Deleted'
        THEN 'Unknown Name Deleted'
      WHEN lower(name) LIKE '%boom fulfillment app%'
        THEN '3PL'
      WHEN lower(name) LIKE '%rakuten%'
        THEN '3PL'
      WHEN lower(name) LIKE '%quiet logistics%'
        THEN '3PL'
      WHEN lower(name) LIKE '%readyshipper%'
        THEN '3PL'
      WHEN lower(name) LIKE '%shipbob%'
        THEN '3PL'
      WHEN lower(name) LIKE '%shiphero%'
        THEN '3PL'
      WHEN lower(name) LIKE '%teelaunch%'
        THEN '3PL'
      WHEN lower(name) LIKE '%threadfully2%'
        THEN '3PL'
      WHEN lower(name) LIKE '%whiplash fulfillment%'
        THEN '3PL'
      WHEN lower(name) LIKE '%bergen%'
        THEN '3PL'
      WHEN lower(name) LIKE '%radial integration%'
        THEN '3PL'
      WHEN lower(name) LIKE '%easypost%'
        THEN '3PL'
      WHEN lower(name) LIKE '%think logistics%'
        THEN '3PL'
      WHEN lower(name) LIKE '%shipmonk%'
        THEN '3PL'
      WHEN lower(name) LIKE '%whiplash%'
        THEN '3PL'
      WHEN lower(name) LIKE '%flexe%'
        THEN '3PL'

      WHEN lower(name) LIKE '%printful%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%customcat%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%dropstream%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%duoplane%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%hublogix%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%oberlo%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%orderlyprint%'
        THEN 'Dropshipping'
      WHEN lower(name) LIKE '%shopified%'
        THEN 'Dropshipping'

      WHEN lower(name) LIKE 'stamps.com%'
        THEN 'App'
      WHEN lower(name) = 'shipstation'
        THEN 'App'
      WHEN lower(name) = 'shippingeasy'
        THEN 'App'
      WHEN lower(name) = 'ordoro'
        THEN 'App'
      WHEN lower(name) = 'shipworks'
        THEN 'App'
      WHEN lower(name) = 'trueship'
        THEN 'App'
      WHEN lower(name) = 'shippo'
        THEN 'App'
      WHEN lower(name) = 'shipcaddie'
        THEN 'App'
      WHEN lower(name) LIKE '%shiprush%'
        THEN 'App'
      WHEN lower(name) = 'canada post'
        THEN 'App'
      WHEN lower(name) LIKE '%danish shipping options%'
        THEN 'App'
      WHEN lower(name) = 'newgistics'
        THEN 'App'
      WHEN lower(name) = 'ordercup'
        THEN 'App'
      WHEN lower(name) = 'shipit'
        THEN 'App'
      WHEN lower(name) = 'shippit%'
        THEN 'App'
      WHEN lower(name) = 'shiprobot%'
        THEN 'App'

      ELSE 'Other'
      END                                      AS "Fulfillment Method",
      SUM(f."net change in fulfillment count") AS "net change in fulfillment count"

    FROM finance.fulfillment_facts f
      JOIN finance.api_client_dimension acd ON f._fulfillment_api_client_key = acd._api_client_key
      JOIN finance.order_fulfillment_shipping_dimension ofsd USING (_order_fulfillment_shipping_key)

    WHERE "event timestamp" >= '2017-01-01' AND "event timestamp" < '2018-01-01'
          AND "name" NOT IN ('Unknown App Title', 'Shopify Mobile For Iphone', 'Shopify Mobile For iPhone',
                             'Shopify Mobile for iPhone', 'Shopify Mobile for Android', 'Digital Downloads')
          AND "is pos fulfillment" = 'Is Not POS Fulfillment'
          AND f."shop shipping country" = 'United States'
    GROUP BY 1, 2, 3, 4, 5
    HAVING SUM(f."net change in fulfillment count") = 1
), orders_with_more_than_one_fulfillment as (
    select fmc."order id",
        count(distinct("Fulfillment Method"))
    FROM fulfillment_method_classification fmc
    GROUP BY 1
    having count(distinct("Fulfillment Method")) > 1
),
    fulfillments_per_order AS (
    SELECT
      fmc."_shop_key",
      fmc."order id",
      fmc."Fulfillment Method",
      sum("net change in fulfillment count") fulfillments_per_order
    FROM fulfillment_method_classification fmc
    where fmc."order id" not in (select "order id" from orders_with_more_than_one_fulfillment)
    GROUP BY 1, 2, 3
), gmv_per_order AS (
    SELECT
      gaf."reported gmv inclusion status",
      gaf."order id",
      SUM(gaf."gmv adjustment (usd)") "gmv by order"
    FROM finance.gmv_adjustment_facts gaf
    GROUP BY 1, 2
), shop_and_order_details as ( --this is the main table and reflects all attributes on an order level
    select
     "order id",
     "_shop_key",
     "shop country name",
     "current merchant deal type",
     case when "reported gmv inclusion status" is null then 'Not included in GMV at all'
     else "reported gmv inclusion status" end "reported gmv inclusion status",
     "Fulfillment Method",
     fulfillments_per_order,
    coalesce("gmv by order",0) as "gmv by order"
    from fulfillments_per_order
    left join gmv_per_order using ("order id")
    left join finance.shop_dimension using (_shop_key)
    --where "reported gmv inclusion status" = 'Included in Reported GMV'
), shop_categorization as (
    SELECT
        _shop_key,
        CASE
        WHEN SUM(fulfillments_per_order) / 365 >= 50
            THEN '50+'
        WHEN SUM(fulfillments_per_order) / 365 > 5
            THEN '5 - 50'
        ELSE '0 - 5'
        END AS "average fulfillments per day"
    FROM shop_and_order_details ff
    GROUP BY 1
), aggregations as (
   select
    sc."average fulfillments per day",
    "Fulfillment Method",
    "reported gmv inclusion status",
    count(distinct(sod._shop_key)) number_of_shops,
    avg("gmv by order"),
    sum("gmv by order") gmv,
    sum(fulfillments_per_order) number_of_fulfillments
from shop_and_order_details sod
join shop_categorization as sc using (_shop_key)
GROUP BY 1,2,3
order by 1,2,3
), medians as (
select
    sc."average fulfillments per day",
    "Fulfillment Method",
    "reported gmv inclusion status",
    median("gmv by order")
from shop_and_order_details sod
join shop_categorization as sc using (_shop_key)
group by 1,2,3
)
select *
from aggregations
join medians using ("average fulfillments per day", "Fulfillment Method", "reported gmv inclusion status")