WITH first_order_info as (
    SELECT
          T.*,  O.sales_user_id as first_sales_user_id, U.name as first_sales_person, GAT.date as ga_date, GAT.source AS first_source, GAT.medium AS first_medium, GAT.campaign AS first_campaign , CLR.lifetime AS customer_lifetime_revenue, COF.days_as_customer

      from( select O.customer_id , count(O.customer_id) as cont, MIN(O.created_at) as date_first_order, MAX(O.created_at) as date_last_order
        from datalake."order" O

            where O.status_id <> 2 AND O.status_id <> 7 AND O.status_id IS NOT NULL
            group by O.customer_id
            order by O.customer_id
          ) AS T


             LEFT JOIN datalake."order" O ON T.customer_id = O.customer_id AND T.date_first_order = O.created_at
            LEFT JOIN datalake."google_analytics_transactions" GAT ON O.id = GAT.transactionid
            LEFT JOIN datalake."user" U ON O.sales_user_id = U.id
            LEFT JOIN (SELECT
                        O.id as cid,
                        SUM(OI.total_price) as lifetime

                        FROM
                        datalake.customer O

                      LEFT JOIN datalake."order" OI
                                ON O.id = OI.customer_id
                                GROUP BY 1)

                                CLR on T.customer_id =  CLR.cid

           LEFT JOIN
            (SELECT
                    O.customer_id AS customer_id,
                    COUNT(O.id) AS lifetime_orders,
                    COUNT(OI.id) AS lifetime_order_items,
                    MIN(O.created_at) AS first_order,
                    MAX(O.created_at) AS latest_order,
                    DATE_DIFF('DAY', MIN(O.created_at),now()) AS days_as_customer

              FROM
                    datalake."order" O
                    LEFT JOIN datalake.order_item OI
                    ON O.id = OI.order_id
                    GROUP BY customer_id) COF on T.customer_id = COF.customer_id




  group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12
  )





  SELECT first_order_info.*

  from first_order_info