SELECT orderid, totalvalue, totalitems,
CASE
    WHEN statusdescription = 'Cancelar' THEN 'Cancelado'
    WHEN statusdescription = 'Cancelado' THEN 'Cancelado'
    WHEN statusdescription = 'Pronto para o manuseio' THEN 'Faturado'
    WHEN statusdescription = 'Preparando Entrega' THEN 'Faturado'
    ELSE statusdescription
END as status_pedido, 
CASE
    WHEN paymentnames LIKE '%Boleto%' THEN 'Boleto' 
    WHEN paymentnames LIKE '%Pix%' THEN 'Pix' 
    ELSE 'Cartão'
END as forma_pagamento,

CASE
    WHEN hostname LIKE '%[variavel_nome_da_conta]%' THEN '[variavel_cliente]'
END AS conta,

DATE_ADD(creationdate, INTERVAL -3 HOUR) AS data_pedido,

clientname, 
CASE
    WHEN origin LIKE '%Marketplace' THEN 'Loja'
    ELSE 'Marketplace'
END AS origin, shippingestimateddatemax, shippingestimateddate, hostname, orderiscomplete, authorizeddate, shippingestimateddatemin, currencycode, creationdate

FROM `[variavel_cliente].vtex_orders`