HPOS-compatible SQL query to export active subscriptions in WooCommerce including start date, next payment date and last order date

export-active-subscriptions.sql

SELECT
    o.id AS subscription_id,
    o.status AS subscription_status,
    o.date_created_gmt AS date_created,
    a.first_name,
    a.last_name,
    a.email,
    o.total_amount,
    o.currency,
    last_order.meta_value AS last_order_date_created,
    schedule_start.meta_value AS schedule_start,
    schedule_next.meta_value AS schedule_next_payment
FROM
    wp_wc_orders o
INNER JOIN
    wp_wc_order_addresses a 
        ON o.id = a.order_id
LEFT JOIN
    wp_wc_orders_meta last_order 
        ON o.id = last_order.order_id 
        AND last_order.meta_key = '_last_order_date_created'
LEFT JOIN
    wp_wc_orders_meta schedule_start 
        ON o.id = schedule_start.order_id 
        AND schedule_start.meta_key = '_schedule_start'
LEFT JOIN
    wp_wc_orders_meta schedule_next 
        ON o.id = schedule_next.order_id 
        AND schedule_next.meta_key = '_schedule_next_payment'
WHERE
    o.type = 'shop_subscription'
    AND o.status IN ('wc-active')
    AND a.address_type = 'billing'
ORDER BY
    o.id DESC;