WITH customers AS (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_timestamp DESC) AS customer_index
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__customer
  WHERE
    NOT email IS NULL /* nonsensical to include any null emails here */
), customer_tags AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__customer_tag
), rollup_customers AS (
  SELECT
    LOWER(customers.email) AS email, /* fields to group by */
    customers.source_relation,
    LISTAGG(DISTINCT CAST(customers.customer_id AS TEXT), ', ') AS customer_ids, /* fields to string agg together */
    LISTAGG(DISTINCT CAST(customers.phone AS TEXT), ', ') AS phone_numbers,
    LISTAGG(DISTINCT CAST(customer_tags.value AS TEXT), ', ') AS customer_tags,
    MIN(customers.created_timestamp) AS first_account_created_at, /* fields to take aggregates of */
    MAX(customers.created_timestamp) AS last_account_created_at,
    MAX(customers.updated_timestamp) AS last_updated_at,
    MAX(customers.marketing_consent_updated_at) AS marketing_consent_updated_at,
    MAX(customers._fivetran_synced) AS last_fivetran_synced,
    MAX(
      CASE WHEN customers.customer_index = 1 THEN customers.is_tax_exempt ELSE NULL END
    ) AS is_tax_exempt, /* take true if ever given for boolean fields */ /* since this changes every year */
    MAX(customers.is_verified_email) AS is_verified_email
  /* for all other fields, just take the latest value */
  FROM customers
  LEFT JOIN customer_tags
    ON customers.customer_id = customer_tags.customer_id
    AND customers.source_relation = customer_tags.source_relation
  GROUP BY
    1,
    2
)
SELECT
  *
FROM rollup_customers

WITH abandoned_checkout AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__abandoned_checkout
  /* "deleted" abandoned checkouts do not appear to have any data tying them to customers, */ /* discounts, or products (and should therefore not get joined in) but let's filter them out here */
  WHERE
    NOT COALESCE(is_deleted, FALSE)
), abandoned_checkout_aggregates AS (
  SELECT
    source_relation,
    CAST(DATE_TRUNC('DAY', created_at) AS DATE) AS date_day,
    COUNT(DISTINCT checkout_id) AS count_abandoned_checkouts,
    COUNT(DISTINCT customer_id) AS count_customers_abandoned_checkout,
    COUNT(DISTINCT email) AS count_customer_emails_abandoned_checkout
  FROM abandoned_checkout
  GROUP BY
    1,
    2
)
SELECT
  *
FROM abandoned_checkout_aggregates

WITH orders AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__orders
  WHERE
    NOT COALESCE(is_deleted, FALSE)
), order_lines AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__order_lines
), order_aggregates AS (
  SELECT
    source_relation,
    CAST(DATE_TRUNC('DAY', created_timestamp) AS DATE) AS date_day,
    COUNT(DISTINCT order_id) AS count_orders,
    SUM(line_item_count) AS count_line_items,
    AVG(line_item_count) AS avg_line_item_count,
    COUNT(DISTINCT customer_id) AS count_customers,
    COUNT(DISTINCT email) AS count_customer_emails,
    SUM(order_adjusted_total) AS order_adjusted_total,
    AVG(order_adjusted_total) AS avg_order_value,
    SUM(shipping_cost) AS shipping_cost,
    SUM(order_adjustment_amount) AS order_adjustment_amount,
    SUM(order_adjustment_tax_amount) AS order_adjustment_tax_amount,
    SUM(refund_subtotal) AS refund_subtotal,
    SUM(refund_total_tax) AS refund_total_tax,
    SUM(total_discounts) AS total_discounts,
    AVG(total_discounts) AS avg_discount,
    SUM(shipping_discount_amount) AS shipping_discount_amount,
    AVG(shipping_discount_amount) AS avg_shipping_discount_amount,
    SUM(percentage_calc_discount_amount) AS percentage_calc_discount_amount,
    AVG(percentage_calc_discount_amount) AS avg_percentage_calc_discount_amount,
    SUM(fixed_amount_discount_amount) AS fixed_amount_discount_amount,
    AVG(fixed_amount_discount_amount) AS avg_fixed_amount_discount_amount,
    SUM(count_discount_codes_applied) AS count_discount_codes_applied,
    COUNT(DISTINCT location_id) AS count_locations_ordered_from,
    SUM(CASE WHEN count_discount_codes_applied > 0 THEN 1 ELSE 0 END) AS count_orders_with_discounts,
    SUM(CASE WHEN refund_subtotal > 0 THEN 1 ELSE 0 END) AS count_orders_with_refunds,
    MIN(created_timestamp) AS first_order_timestamp,
    MAX(created_timestamp) AS last_order_timestamp
  FROM orders
  GROUP BY
    1,
    2
), order_line_aggregates AS (
  SELECT
    order_lines.source_relation,
    CAST(DATE_TRUNC('DAY', orders.created_timestamp) AS DATE) AS date_day,
    SUM(order_lines.quantity) AS quantity_sold,
    SUM(order_lines.refunded_quantity) AS quantity_refunded,
    SUM(order_lines.quantity_net_refunds) AS quantity_net,
    SUM(order_lines.quantity) / COUNT(DISTINCT order_lines.order_id) AS avg_quantity_sold,
    SUM(order_lines.quantity_net_refunds) / COUNT(DISTINCT order_lines.order_id) AS avg_quantity_net,
    COUNT(DISTINCT order_lines.variant_id) AS count_variants_sold,
    COUNT(DISTINCT order_lines.product_id) AS count_products_sold,
    SUM(
      CASE WHEN order_lines.is_gift_card THEN order_lines.quantity_net_refunds ELSE 0 END
    ) AS quantity_gift_cards_sold,
    SUM(
      CASE
        WHEN order_lines.is_shipping_required
        THEN order_lines.quantity_net_refunds
        ELSE 0
      END
    ) AS quantity_requiring_shipping
  FROM order_lines
  LEFT JOIN orders /* just joining with order to get the created_timestamp */
    ON order_lines.order_id = orders.order_id
    AND order_lines.source_relation = orders.source_relation
  GROUP BY
    1,
    2
), final AS (
  SELECT
    order_aggregates.*,
    order_line_aggregates.quantity_sold,
    order_line_aggregates.quantity_refunded,
    order_line_aggregates.quantity_net,
    order_line_aggregates.count_variants_sold,
    order_line_aggregates.count_products_sold,
    order_line_aggregates.quantity_gift_cards_sold,
    order_line_aggregates.quantity_requiring_shipping,
    order_line_aggregates.avg_quantity_sold,
    order_line_aggregates.avg_quantity_net
  FROM order_aggregates
  LEFT JOIN order_line_aggregates
    ON order_aggregates.date_day = order_line_aggregates.date_day
    AND order_aggregates.source_relation = order_line_aggregates.source_relation
)
SELECT
  *
FROM final

WITH abandoned_checkout AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__abandoned_checkout
  /* "deleted" abandoned checkouts do not appear to have any data tying them to customers, */ /* discounts, or products (and should therefore not get joined in) but let's filter them out here */
  WHERE
    NOT COALESCE(is_deleted, FALSE)
), abandoned_checkout_discount_code AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__abandoned_checkout_discount_code
  /* we need the TYPE of discount (shipping, percentage, fixed_amount) to avoid fanning out of joins */ /* so filter out records that have this */
  WHERE
    COALESCE(type, '') <> ''
), abandoned_checkout_shipping_line AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__abandoned_checkout_shipping_line
), roll_up_shipping_line AS (
  SELECT
    checkout_id,
    source_relation,
    SUM(price) AS price
  FROM abandoned_checkout_shipping_line
  GROUP BY
    1,
    2
), abandoned_checkouts_aggregated AS (
  SELECT
    abandoned_checkout_discount_code.code,
    abandoned_checkout_discount_code.type,
    abandoned_checkout_discount_code.source_relation,
    SUM(abandoned_checkout_discount_code.amount) AS total_abandoned_checkout_discount_amount,
    SUM(COALESCE(abandoned_checkout.total_line_items_price, 0)) AS total_abandoned_checkout_line_items_price,
    SUM(COALESCE(roll_up_shipping_line.price, 0)) AS total_abandoned_checkout_shipping_price,
    COUNT(DISTINCT customer_id) AS count_abandoned_checkout_customers,
    COUNT(DISTINCT email) AS count_abandoned_checkout_customer_emails,
    COUNT(DISTINCT abandoned_checkout.checkout_id) AS count_abandoned_checkouts
  FROM abandoned_checkout_discount_code
  LEFT JOIN abandoned_checkout
    ON abandoned_checkout_discount_code.checkout_id = abandoned_checkout.checkout_id
    AND abandoned_checkout_discount_code.source_relation = abandoned_checkout.source_relation
  LEFT JOIN roll_up_shipping_line
    ON roll_up_shipping_line.checkout_id = abandoned_checkout_discount_code.checkout_id
    AND roll_up_shipping_line.source_relation = abandoned_checkout_discount_code.source_relation
  GROUP BY
    1,
    2,
    3
)
SELECT
  *
FROM abandoned_checkouts_aggregated

WITH order_discount_code AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__order_discount_code
), orders AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__orders
), orders_aggregated AS (
  SELECT
    order_discount_code.code,
    order_discount_code.type,
    order_discount_code.source_relation,
    AVG(order_discount_code.amount) AS avg_order_discount_amount,
    SUM(order_discount_code.amount) AS total_order_discount_amount,
    MAX(orders.total_line_items_price) AS total_order_line_items_price, /* summing would multiply the total by the # of discount codes applied to an order */
    MAX(orders.shipping_cost) AS total_order_shipping_cost, /* summing would multiply the total by the # of discount codes applied to an order */
    MAX(orders.refund_subtotal + orders.refund_total_tax) AS total_order_refund_amount, /* summing would multiply the total by the # of discount codes applied to an order */
    COUNT(DISTINCT customer_id) AS count_customers,
    COUNT(DISTINCT email) AS count_customer_emails,
    COUNT(DISTINCT order_discount_code.order_id) AS count_orders
  FROM order_discount_code
  JOIN orders
    ON order_discount_code.order_id = orders.order_id
    AND order_discount_code.source_relation = orders.source_relation
  GROUP BY
    1,
    2,
    3
)
SELECT
  *
FROM orders_aggregated

WITH orders AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__order
), order_aggregates AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__orders__order_line_aggregates
), transactions AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__transactions
  WHERE
    LOWER(status) = 'success'
    AND NOT LOWER(kind) IN ('authorization', 'void')
    AND LOWER(gateway) <> 'gift_card' /* redeeming a giftcard does not introduce new revenue */
), transaction_aggregates AS (
  /* this is necessary as customers can pay via multiple payment gateways */
  SELECT
    order_id,
    source_relation,
    LOWER(kind) AS kind,
    SUM(currency_exchange_calculated_amount) AS currency_exchange_calculated_amount
  FROM transactions
  GROUP BY
    1,
    2,
    3
), customer_emails AS (
  /* in case any orders records don't have the customer email attached yet */
  SELECT
    customer_id,
    source_relation,
    email
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__customer
  WHERE
    NOT email IS NULL
  GROUP BY
    1,
    2,
    3
), aggregated AS (
  SELECT
    LOWER(customer_emails.email) AS email,
    orders.source_relation,
    MIN(orders.created_timestamp) AS first_order_timestamp,
    MAX(orders.created_timestamp) AS most_recent_order_timestamp,
    AVG(transaction_aggregates.currency_exchange_calculated_amount) AS avg_order_value,
    SUM(transaction_aggregates.currency_exchange_calculated_amount) AS lifetime_total_spent,
    SUM(refunds.currency_exchange_calculated_amount) AS lifetime_total_refunded,
    COUNT(DISTINCT orders.order_id) AS lifetime_count_orders,
    AVG(order_aggregates.order_total_quantity) AS avg_quantity_per_order,
    SUM(order_aggregates.order_total_tax) AS lifetime_total_tax,
    AVG(order_aggregates.order_total_tax) AS avg_tax_per_order,
    SUM(order_aggregates.order_total_discount) AS lifetime_total_discount,
    AVG(order_aggregates.order_total_discount) AS avg_discount_per_order,
    SUM(order_aggregates.order_total_shipping) AS lifetime_total_shipping,
    AVG(order_aggregates.order_total_shipping) AS avg_shipping_per_order,
    SUM(order_aggregates.order_total_shipping_with_discounts) AS lifetime_total_shipping_with_discounts,
    AVG(order_aggregates.order_total_shipping_with_discounts) AS avg_shipping_with_discounts_per_order,
    SUM(order_aggregates.order_total_shipping_tax) AS lifetime_total_shipping_tax,
    AVG(order_aggregates.order_total_shipping_tax) AS avg_shipping_tax_per_order
  FROM orders
  JOIN customer_emails
    ON orders.customer_id = customer_emails.customer_id
    AND orders.source_relation = customer_emails.source_relation
  LEFT JOIN transaction_aggregates
    ON orders.order_id = transaction_aggregates.order_id
    AND orders.source_relation = transaction_aggregates.source_relation
    AND transaction_aggregates.kind IN ('sale', 'capture')
  LEFT JOIN transaction_aggregates AS refunds
    ON orders.order_id = refunds.order_id
    AND orders.source_relation = refunds.source_relation
    AND refunds.kind = 'refund'
  LEFT JOIN order_aggregates
    ON orders.order_id = order_aggregates.order_id
    AND orders.source_relation = order_aggregates.source_relation
  GROUP BY
    1,
    2
)
SELECT
  *
FROM aggregated

WITH __dbt__cte__shopify__orders__order_refunds AS (
  WITH refunds AS (
    SELECT
      *
    FROM TEST.PUBLIC_stg_shopify.stg_shopify__refund
  ), order_line_refunds AS (
    SELECT
      *
    FROM TEST.PUBLIC_stg_shopify.stg_shopify__order_line_refund
  ), refund_join AS (
    SELECT
      refunds.refund_id,
      refunds.created_at,
      refunds.order_id,
      refunds.user_id,
      refunds.source_relation,
      order_line_refunds.order_line_refund_id,
      order_line_refunds.order_line_id,
      order_line_refunds.restock_type,
      order_line_refunds.quantity,
      order_line_refunds.subtotal,
      order_line_refunds.total_tax
    FROM refunds
    LEFT JOIN order_line_refunds
      ON refunds.refund_id = order_line_refunds.refund_id
      AND refunds.source_relation = order_line_refunds.source_relation
  )
  SELECT
    *
  FROM refund_join
), order_lines AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__order_line
), fulfillment AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__fulfillment
), orders AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__order
  WHERE
    NOT COALESCE(is_deleted, FALSE)
), refunds AS (
  SELECT
    *
  FROM __dbt__cte__shopify__orders__order_refunds
), refunds_aggregated AS (
  SELECT
    order_line_id,
    source_relation,
    SUM(quantity) AS quantity,
    SUM(COALESCE(subtotal, 0)) AS subtotal
  FROM refunds
  GROUP BY
    1,
    2
), joined AS (
  SELECT
    order_lines.order_line_id,
    order_lines.variant_id,
    order_lines.source_relation,
    fulfillment.location_id, /* location id is stored in fulfillment rather than order */
    orders.order_id,
    orders.customer_id,
    fulfillment.fulfillment_id,
    LOWER(orders.email) AS email,
    order_lines.pre_tax_price,
    order_lines.quantity,
    orders.created_timestamp AS order_created_timestamp,
    fulfillment.status AS fulfillment_status,
    refunds_aggregated.subtotal AS subtotal_sold_refunds,
    refunds_aggregated.quantity AS quantity_sold_refunds
  FROM order_lines
  JOIN orders
    ON order_lines.order_id = orders.order_id
    AND order_lines.source_relation = orders.source_relation
  JOIN fulfillment
    ON orders.order_id = fulfillment.order_id
    AND orders.source_relation = fulfillment.source_relation
  LEFT JOIN refunds_aggregated
    ON refunds_aggregated.order_line_id = order_lines.order_line_id
    AND refunds_aggregated.source_relation = order_lines.source_relation
), aggregated AS (
  SELECT
    variant_id,
    location_id,
    source_relation,
    SUM(pre_tax_price) AS subtotal_sold,
    SUM(quantity) AS quantity_sold,
    COUNT(DISTINCT order_id) AS count_distinct_orders,
    COUNT(DISTINCT customer_id) AS count_distinct_customers,
    COUNT(DISTINCT email) AS count_distinct_customer_emails,
    MIN(order_created_timestamp) AS first_order_timestamp,
    MAX(order_created_timestamp) AS last_order_timestamp,
    COUNT(DISTINCT CASE WHEN fulfillment_status = 'pending' THEN fulfillment_id END) AS count_fulfillment_pending,
    COUNT(DISTINCT CASE WHEN fulfillment_status = 'open' THEN fulfillment_id END) AS count_fulfillment_open,
    COUNT(DISTINCT CASE WHEN fulfillment_status = 'success' THEN fulfillment_id END) AS count_fulfillment_success,
    COUNT(DISTINCT CASE WHEN fulfillment_status = 'cancelled' THEN fulfillment_id END) AS count_fulfillment_cancelled,
    COUNT(DISTINCT CASE WHEN fulfillment_status = 'error' THEN fulfillment_id END) AS count_fulfillment_error,
    COUNT(DISTINCT CASE WHEN fulfillment_status = 'failure' THEN fulfillment_id END) AS count_fulfillment_failure,
    SUM(COALESCE(subtotal_sold_refunds, 0)) AS subtotal_sold_refunds,
    SUM(COALESCE(quantity_sold_refunds, 0)) AS quantity_sold_refunds
  FROM joined
  GROUP BY
    1,
    2,
    3
)
SELECT
  *
FROM aggregated

WITH order_shipping_line AS (
  SELECT
    order_id,
    source_relation,
    order_shipping_line_id,
    SUM(price) AS shipping_price,
    SUM(discounted_price) AS discounted_shipping_price
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__order_shipping_line
  GROUP BY
    1,
    2,
    3
), order_shipping_tax_line AS (
  SELECT
    order_shipping_line_id,
    source_relation,
    SUM(price) AS shipping_tax
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__order_shipping_tax_line
  GROUP BY
    1,
    2
), aggregated AS (
  SELECT
    order_shipping_line.order_id,
    order_shipping_line.source_relation,
    SUM(order_shipping_line.shipping_price) AS shipping_price,
    SUM(order_shipping_line.discounted_shipping_price) AS discounted_shipping_price,
    SUM(order_shipping_tax_line.shipping_tax) AS shipping_tax
  FROM order_shipping_line
  LEFT JOIN order_shipping_tax_line
    ON order_shipping_line.order_shipping_line_id = order_shipping_tax_line.order_shipping_line_id
    AND order_shipping_line.source_relation = order_shipping_tax_line.source_relation
  GROUP BY
    1,
    2
)
SELECT
  *
FROM aggregated

WITH order_lines AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__order_lines
), orders AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__orders
), product_aggregated AS (
  SELECT
    order_lines.product_id,
    order_lines.source_relation,
    SUM(order_lines.quantity) AS quantity_sold, /* moved over from shopify__products */
    SUM(order_lines.pre_tax_price) AS subtotal_sold,
    SUM(order_lines.quantity_net_refunds) AS quantity_sold_net_refunds,
    SUM(order_lines.subtotal_net_refunds) AS subtotal_sold_net_refunds,
    MIN(orders.created_timestamp) AS first_order_timestamp,
    MAX(orders.created_timestamp) AS most_recent_order_timestamp,
    SUM(order_lines.total_discount) AS product_total_discount, /* new columns */
    SUM(order_lines.order_line_tax) AS product_total_tax,
    AVG(order_lines.quantity) AS avg_quantity_per_order_line,
    AVG(order_lines.total_discount) AS product_avg_discount_per_order_line,
    AVG(order_lines.order_line_tax) AS product_avg_tax_per_order_line
  FROM order_lines
  LEFT JOIN orders
    ON order_lines.order_id = orders.order_id
    AND order_lines.source_relation = orders.source_relation
  GROUP BY
    1,
    2
)
SELECT
  *
FROM product_aggregated

WITH products AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__product
), collection_product AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__collection_product
), collection AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__collection
  WHERE
    NOT COALESCE(is_deleted, FALSE) /* limit to only active collections */
), product_tag AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__product_tag
), product_variant AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__product_variant
), product_image AS (
  SELECT
    *
  FROM TEST.PUBLIC_stg_shopify.stg_shopify__product_image
), collections_aggregated AS (
  SELECT
    collection_product.product_id,
    collection_product.source_relation,
    LISTAGG(collection.title, ', ') AS collections
  FROM collection_product
  JOIN collection
    ON collection_product.collection_id = collection.collection_id
    AND collection_product.source_relation = collection.source_relation
  GROUP BY
    1,
    2
), tags_aggregated AS (
  SELECT
    product_id,
    source_relation,
    LISTAGG(value, ', ') AS tags
  FROM product_tag
  GROUP BY
    1,
    2
), variants_aggregated AS (
  SELECT
    product_id,
    source_relation,
    COUNT(variant_id) AS count_variants
  FROM product_variant
  GROUP BY
    1,
    2
), images_aggregated AS (
  SELECT
    product_id,
    source_relation,
    COUNT(*) AS count_images
  FROM product_image
  GROUP BY
    1,
    2
), joined AS (
  SELECT
    products.*,
    collections_aggregated.collections,
    tags_aggregated.tags,
    variants_aggregated.count_variants,
    COALESCE(images_aggregated.count_images, 0) > 0 AS has_product_image
  FROM products
  LEFT JOIN collections_aggregated
    ON products.product_id = collections_aggregated.product_id
    AND products.source_relation = collections_aggregated.source_relation
  LEFT JOIN tags_aggregated
    ON products.product_id = tags_aggregated.product_id
    AND products.source_relation = tags_aggregated.source_relation
  LEFT JOIN variants_aggregated
    ON products.product_id = variants_aggregated.product_id
    AND products.source_relation = variants_aggregated.source_relation
  LEFT JOIN images_aggregated
    ON products.product_id = images_aggregated.product_id
    AND products.source_relation = images_aggregated.source_relation
)
SELECT
  *
FROM joined

WITH rawdata AS (
  WITH p AS (
    SELECT
      0 AS generated_number
    UNION ALL
    SELECT
      1
  ), unioned AS (
    SELECT
      p0.generated_number * POWER(2, 0) + p1.generated_number * POWER(2, 1) + p2.generated_number * POWER(2, 2) + p3.generated_number * POWER(2, 3) + p4.generated_number * POWER(2, 4) + p5.generated_number * POWER(2, 5) + p6.generated_number * POWER(2, 6) + p7.generated_number * POWER(2, 7) + p8.generated_number * POWER(2, 8) + p9.generated_number * POWER(2, 9) + p10.generated_number * POWER(2, 10) + 1 AS generated_number
    FROM p AS p0
    CROSS JOIN p AS p1
    CROSS JOIN p AS p2
    CROSS JOIN p AS p3
    CROSS JOIN p AS p4
    CROSS JOIN p AS p5
    CROSS JOIN p AS p6
    CROSS JOIN p AS p7
    CROSS JOIN p AS p8
    CROSS JOIN p AS p9
    CROSS JOIN p AS p10
  )
  SELECT
    *
  FROM unioned
  WHERE
    generated_number <= 2041
  ORDER BY
    generated_number
), all_periods AS (
  SELECT
    (
      DATEADD(day, ROW_NUMBER() OVER (ORDER BY 1) - 1, CAST('2019-01-01' AS DATE))
    ) AS date_day
  FROM rawdata
), filtered AS (
  SELECT
    *
  FROM all_periods
  WHERE
    date_day <= CURRENT_DATE
)
SELECT
  *
FROM filtered

WITH calendar AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__calendar
  WHERE
    CAST(DATE_TRUNC('MONTH', date_day) AS DATE) = date_day
), customers AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__customers
), orders AS (
  SELECT
    *
  FROM TEST.PUBLIC_shopify.shopify__orders
), customer_calendar AS (
  SELECT
    CAST(calendar.date_day AS DATE) AS date_month,
    customers.customer_id,
    customers.first_order_timestamp,
    customers.source_relation,
    CAST(DATE_TRUNC('MONTH', first_order_timestamp) AS DATE) AS cohort_month
  FROM calendar
  INNER JOIN customers
    ON CAST(DATE_TRUNC('MONTH', first_order_timestamp) AS DATE) <= calendar.date_day
), orders_joined AS (
  SELECT
    customer_calendar.date_month,
    customer_calendar.customer_id,
    customer_calendar.first_order_timestamp,
    customer_calendar.cohort_month,
    customer_calendar.source_relation,
    COALESCE(COUNT(DISTINCT orders.order_id), 0) AS order_count_in_month,
    COALESCE(SUM(orders.order_adjusted_total), 0) AS total_price_in_month,
    COALESCE(SUM(orders.line_item_count), 0) AS line_item_count_in_month
  FROM customer_calendar
  LEFT JOIN orders
    ON customer_calendar.customer_id = orders.customer_id
    AND customer_calendar.source_relation = orders.source_relation
    AND customer_calendar.date_month = CAST(DATE_TRUNC('MONTH', created_timestamp) AS DATE)
  GROUP BY
    1,
    2,
    3,
    4,
    5
), windows AS (
  SELECT
    *,
    SUM(total_price_in_month) OVER (PARTITION BY customer_id ORDER BY date_month rows BETWEEN UNBOUNDED preceding AND CURRENT ROW) AS total_price_lifetime,
    SUM(order_count_in_month) OVER (PARTITION BY customer_id ORDER BY date_month rows BETWEEN UNBOUNDED preceding AND CURRENT ROW) AS order_count_lifetime,
    SUM(line_item_count_in_month) OVER (PARTITION BY customer_id ORDER BY date_month rows BETWEEN UNBOUNDED preceding AND CURRENT ROW) AS line_item_count_lifetime,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date_month ASC) AS cohort_month_number
  FROM orders_joined
), final AS (
  SELECT
    *,
    MD5(
      CAST(COALESCE(CAST(date_month AS TEXT), '_dbt_utils_surrogate_key_null_') || '-' || COALESCE(CAST(customer_id AS TEXT), '_dbt_utils_surrogate_key_null_') || '-' || COALESCE(CAST(source_relation AS TEXT), '_dbt_utils_surrogate_key_null_') AS TEXT)
    ) AS customer_cohort_id
  FROM windows
)
SELECT
  *
FROM final