bassemawhoob

-- Based on: https://medium.com/quick-code/how-to-write-sql-to-calculate-user-cohort-retention-a1b3b57c7a2f
-- Uses DATE_PART instead of DATE_DIFF as it is not supported by PostgreSQL

-- (branch_id, scheduled_for, total_cents)
WITH activities AS (
  SELECT branch_id, scheduled_for, total_cents
  FROM orders
  WHERE orders.status = 'complete'
),
-- (branch_id, cohort_month): cohort month is the first order date

yancya

#standardsql

  WITH a AS (
SELECT * FROM UNNEST([1,2,3,4]) AS n

    ), b AS (
SELECT * FROM UNNEST([4,5,6,7]) AS n)

SELECT * FROM a