SELECT 
itens.order_id AS orderid,

TIMESTAMP(orders.date) AS data_pedido,

CASE
    WHEN status like "%CANCELAR%" THEN "Cancelado"
    WHEN status like "%CANCELADO%" THEN "Cancelado"
    WHEN status like "%FINALIZADO%" THEN "Faturado"
    WHEN status like "%PEDIDO RECEBIDO%" THEN "Faturado"
    WHEN status like "%PENDENTE%" THEN "Pagamento Pendente"
    WHEN status like "%AGUARDANDO%" THEN "Pagamento Pendente"
    WHEN status like "%A ENVIAR%" THEN "Faturado"
    WHEN status like "%A ENVIAR VISA%" THEN "Faturado"
    WHEN status like "%A ENVIAR MASTER%" THEN "Faturado"
    WHEN status like "%ENVIADO%" THEN "Faturado"
    ELSE status
END as status_item,

itens.name AS name,
itens.price AS sellingprice, 
itens.original_price AS price, 
itens.quantity AS quantity,
itens.brand AS additionalinfo_brandname 

FROM `yooper.[conta_bigquery].tray_productssold` AS itens
LEFT JOIN `yooper.[conta_bigquery].tray_orders` AS orders
ON itens.order_id = orders.id