wix_subscriptions

Purpose: This attempts to emulate the pro_subscriptions table. Currently not in use, but will hopefully be more relevant in the years to come Fields:

 user_id: id of the user
 app_type: app purchased by user
 instance_id:  wix_instance_id
 created_at: when the subscription was created
 downgraded_at: time when subscription is downgraded
 cancelled_at: time when subscription was cancelled by the user
 active: if subscription is still active
 price: price of subscription in cents
 subscription_type: premium, pro, enterprise
 level: monthly, yearly
 to_downgrade_at: when the subscription should be downgraded
 cancel_reason: reason for cancel

Notes:

There is a LOT going on in this materialized view. If you spot anything weird, there might be a problem with the query

CREATE MATERIALIZED VIEW wix_subscriptions AS
  WITH base AS (
SELECT
  id,
  user_id,
  app_type,
  instance_id,
  COALESCE(wix_created_at, created_at) as created_at,
  event,
  cancel_reason,
  LOWER(current_plan) AS current_plan,
  LOWER(current_level) AS current_level,
  RANK() OVER(PARTITION BY instance_id, current_plan, current_level ORDER BY COALESCE(wix_created_at, created_at))
FROM
  wix_webhooks
  ), tabulation AS (
SELECT
  base.instance_id,
  base.user_id,
  base.app_type,
  base.event,
  base.created_at,
  base.rank,
  base.current_plan,
  base.current_level,
  base.cancel_reason,
  ranks_of_purchase.rank_of_purchase,
  counts_of_purchases.count_of_purchases,
  FIRST_VALUE(ranks_of_purchase.rank_of_purchase) OVER(w) AS ranks_of_purchase_expanded,
  LAST_VALUE(last_events.event) OVER(w) AS last_event,
  STRING_AGG(cancel_requested.created_at::TEXT, ',') OVER(w)::TIMESTAMP AS cancelled_at
FROM base
  JOIN (SELECT instance_id, COUNT(rank) AS count_of_purchases FROM base WHERE rank = 1 GROUP BY instance_id) counts_of_purchases ON counts_of_purchases.instance_id = base.instance_id
  LEFT JOIN (SELECT id, RANK() OVER(PARTITION BY base.instance_id, event ORDER BY created_at) AS rank_of_purchase FROM base where base.event = 'PURCHASE_IMMEDIATE') ranks_of_purchase on ranks_of_purchase.id = base.id
  LEFT JOIN (SELECT id, event, RANK() OVER(PARTITION BY base.instance_id, current_plan, current_level ORDER BY created_at DESC) AS rank_of_events FROM base) last_events ON last_events.id = base.id AND last_events.rank_of_events = 1
  LEFT JOIN (SELECT id, created_at, event, RANK() OVER(PARTITION BY base.instance_id, current_plan, current_level ORDER BY created_at) AS cancel_rank FROM base WHERE event IN ('CANCEL_REQUESTED', 'CANCEL_IMMEDIATE')) cancel_requested ON cancel_requested.id = base.id AND cancel_requested.cancel_rank = 1
WINDOW w AS (PARTITION BY base.instance_id, base.current_plan, base.current_level)
  ), plan_comparer AS (
SELECT *
FROM (VALUES
  ('premium', 1),
  ('pro', 2),
  ('enterprise', 99)
) as f(plan, id)
  ), level_comparer AS (
SELECT *
FROM (VALUES
  ('monthly', 1),
  ('yearly', 12)
) AS f(level, id)
  )
  SELECT
user_id,
final.app_type,
instance_id,
created_at,
COALESCE(downgraded_at, cancelled_at) AS downgraded_at,
cancelled_at,
active,
wp.price,
current_plan AS subscription_type,
current_level AS level,
to_downgrade_at,
COALESCE(udbc, cancel_reason) AS cancel_reason
  FROM (
SELECT
  instance_id,
  user_id,
  app_type,
  event,
  created_at,
  current_plan,
  current_level,
  last_event,
  ARRAY_TO_STRING(ARRAY(SELECT DISTINCT UNNEST(cancel_reason_agg)),',') AS cancel_reason,
  udbc,
  CASE
    WHEN count_of_purchases = 1 AND count_of_purchase_expanded = 1 --only purchased
      THEN NULL
    WHEN count_of_purchases = 1 AND count_of_purchase_expanded > 1 -- purchase and cancels
      THEN
        CASE
          WHEN last_event IN ('CANCEL_REQUESTED', 'CANCEL_RESCINDED')
            THEN NULL
          ELSE
            MAX(created_at) OVER(PARTITION BY instance_id)
        END
    WHEN count_of_purchases > 1 AND count_of_purchase_expanded = 1
      THEN downgraded_at
    WHEN count_of_purchases > 1 AND count_of_purchase_expanded > 1
      THEN
        CASE
          WHEN last_event IN ('CANCEL_REQUESTED', 'CANCEL_RESCINDED')
            THEN NULL
          ELSE
            MAX(created_at) OVER(PARTITION BY instance_id, ranks_of_purchase_expanded)
        END
  END AS downgraded_at,
  CASE
    WHEN count_of_purchases = 1 AND count_of_purchase_expanded = 1 --only one purchased, not cancelled
      THEN NULL
    WHEN count_of_purchases = 1 AND count_of_purchase_expanded > 1 -- one purchase and potential cancel
      THEN
        CASE
          WHEN last_event = 'CANCEL_RESCINDED'
            THEN NULL
          ELSE
            cancelled_at
        END
    WHEN count_of_purchases > 1 AND count_of_purchase_expanded = 1 -- they upgraded so there is no official cancel event
      THEN
        CASE
          WHEN rank_of_purchase < count_of_purchases -- make sure last purchase is not cancelled
            THEN downgraded_at
        END
    WHEN count_of_purchases > 1 AND count_of_purchase_expanded > 1 --
      THEN
        CASE
          WHEN rank_of_purchase < count_of_purchases -- If not last purchase, then should be cancelled
            THEN
              CASE
                WHEN last_event = 'CANCEL_RESCINDED' THEN downgraded_at
                WHEN last_event = 'CANCEL_REQUESTED' THEN cancelled_at
              END
          ELSE
            CASE
              WHEN last_event ='CANCEL_RESCINDED'
                THEN NULL
              ELSE
                cancelled_at
            END
        END
  END AS cancelled_at,
  CASE
    WHEN count_of_purchases = 1 AND count_of_purchase_expanded = 1 --only one purchased, not cancelled
      THEN true
    WHEN count_of_purchases = 1 AND count_of_purchase_expanded > 1 -- one purchase and potential cancel
      THEN
        CASE
          WHEN last_event = 'CANCEL_IMMEDIATE'
            THEN false
          ELSE
            true
        END
    WHEN count_of_purchases > 1 AND count_of_purchase_expanded = 1 -- they upgraded so there is no official cancel event
      THEN
        CASE
          WHEN rank_of_purchase < count_of_purchases -- If not last purchase, then should be cancelled
            THEN false
          ELSE
            true
        END
    WHEN count_of_purchases > 1 AND count_of_purchase_expanded > 1 --
      THEN
        CASE
          WHEN rank_of_purchase < count_of_purchases -- If not last purchase, then should be cancelled
            THEN false
          ELSE
            CASE
              WHEN last_event = 'CANCEL_IMMEDIATE'
                THEN false
              ELSE
                true
            END
        END
  END AS active,
  CASE
    WHEN last_event = 'CANCEL_REQUESTED' AND ((count_of_purchases = 1 AND count_of_purchase_expanded > 1) OR ((count_of_purchases > 1 AND count_of_purchase_expanded > 1) AND (rank_of_purchase = count_of_purchases)))
      THEN CASE WHEN current_level = 'yearly' THEN created_at + INTERVAL '1 year' ELSE created_at + INTERVAL '1 month' END
  END AS to_downgrade_at
FROM
  ( SELECT
      tabulation.*,
      COUNT(tabulation.ranks_of_purchase_expanded) OVER(PARTITION BY tabulation.instance_id, tabulation.ranks_of_purchase_expanded) AS count_of_purchase_expanded,
      tabulation_2.created_at AS downgraded_at,
      ARRAY_AGG(tabulation.cancel_reason) OVER(PARTITION BY tabulation.instance_id, tabulation.ranks_of_purchase_expanded) AS cancel_reason_agg,
      CASE
        WHEN pc2.id < pc.id OR pc2.id > pc.id THEN pc.plan || ' to ' || pc2.plan
        WHEN lc2.id < lc.id OR lc2.id > lc.id THEN lc.level || ' to ' || lc2.level
      END AS udbc
    FROM
      tabulation
      JOIN plan_comparer pc ON pc.plan = tabulation.current_plan
      JOIN level_comparer lc ON lc.level = tabulation.current_level
      LEFT JOIN tabulation tabulation_2 ON tabulation.instance_id = tabulation_2.instance_id AND tabulation.rank_of_purchase + 1 = tabulation_2.rank_of_purchase
      LEFT JOIN plan_comparer pc2 ON pc2.plan = tabulation_2.current_plan
      LEFT JOIN level_comparer lc2 ON lc2.level = tabulation_2.current_level
  ) AS logic
  ) AS final
  LEFT JOIN wix_pricing wp ON wp.app_type = final.app_type AND wp.level = final.current_level AND wp.plan = final.current_plan
  WHERE
event = 'PURCHASE_IMMEDIATE'
  ORDER BY user_id, instance_id, created_at;