Cacher is the code snippet organizer for pro developers

SELECT
  name,
  CAST( orders_id AS integer) AS orders_id,
  timeplaced,
  CAST( client_id AS integer) AS client_id,
  amount
FROM
  [ZARABQ2.datos_cliente_v3_ALL]
WHERE
  name IN ('ZARA_US',
    'ZARA_UK',
    'ZARA_JP')
  AND ( (order_status<>'X'
      AND unique_client_id IS NOT NULL)
    OR (order_status='X'
      AND (email_client_domain<>'oats.noresponse.inditex.com'
        AND email_client_domain<>'inditex.com' )))
        LIMIT 100

SELECT
  client_id,
  COUNT(distinct timeplaced) - 1 as frequency,
  date_diff(MIN(timeplaced), MAX(timeplaced), DAY) as recency,
  date_diff(CURRENT_DATE, MIN(timeplaced),DAY) as T,
  sum(amount) as monetary_value
FROM (
SELECT
  name,
  CAST( orders_id AS INT64) AS orders_id,
  timeplaced,
  CAST( client_id AS INT64) AS client_id,
  amount
FROM
  `zara-bq-2.ZARABQ2.datos_cliente_v3_2018`
WHERE
  name IN ('ZARA_US',
    'ZARA_UK',
    'ZARA_JP')
  AND ( (order_status<>'X'
      AND unique_client_id IS NOT NULL)
    OR (order_status='X'
      AND (email_client_domain<>'oats.noresponse.inditex.com'
        AND email_client_domain<>'inditex.com' )))
        )
GROUP BY client_id, name