sven-wisner-121125-v1.sql

SELECT
    vo.status,
    DATE_FORMAT(vo.status_updated_at, '%Y-%m-%d') AS status_updated_at,
    DATE_FORMAT(vo.created_at, '%Y-%m-%d') AS created_at,
    CONCAT(vo.customer_data_contact_ndc, vo.customer_data_contact_serial) AS customer_data_contact_phone_number,
    vo.type,
    vo.channel,
    u.reference_id AS user_reference_id,
    vbp.segment AS product_segment,
    vbp.external_id AS product_external_id,
    vbp.name AS product_name,
    vbp.reference_id AS product_reference_id,
    vmd.reference_id AS device_reference_id,
    vmd.name AS device_name,
    vmd.external_id AS device_external_id,
    vmd.external_id_of_provider AS device_external_id_of_provider,
    DATE_FORMAT(vos.submitted_at, '%Y-%m-%d') AS submission_submitted_at,
    vos.external_order_id,
    vos.external_order_status,
    vos.external_order_status_updated_at,
    p.reference_id AS principal_reference_id,
    1 AS count
FROM vodafone_orders vo
         LEFT JOIN user u ON vo.user_reference_id = u.reference_id
         LEFT JOIN cart c ON vo.cart_reference_id = c.reference_id
         LEFT JOIN vodafone_bookable_products vbp ON c.tariff_reference_id = vbp.reference_id
         LEFT JOIN vodafone_mobile_devices vmd ON c.device_reference_id = vmd.reference_id
         LEFT JOIN vodafone_order_submission vos ON vo.reference_id = vos.order_reference_id
         LEFT JOIN principal p ON vo.principal_reference_id = p.reference_id
WHERE
    vo.created_at >= '2025-11-01'
  AND vo.created_at < DATE_ADD('2025-11-10', INTERVAL 1 DAY);