FluentAffiliate - SQL query to export affiliate data

affiliates-query.sql

SELECT
    -- Affiliate info
    a.id                  AS affiliate_id,
    a.user_id             AS affiliate_user_id,
    a.status              AS affiliate_status,
    a.rate                AS commission_rate,
    a.rate_type,
    a.total_earnings,
    a.unpaid_earnings,
    a.referrals           AS total_referral_count,
    a.visits              AS total_visits,
    a.payment_email,
    a.created_at          AS affiliate_joined,

    -- Referral info
    r.id                  AS referral_id,
    r.status              AS referral_status,
    r.amount              AS commission_amount,
    r.order_total,
    r.currency,
    r.description         AS referral_description,
    r.provider,
    r.provider_id         AS order_id,
    r.type                AS referral_type,
    r.created_at          AS referral_date,

    -- Customer info
    c.id                  AS customer_id,
    c.user_id             AS customer_user_id,
    c.email               AS customer_email,
    c.first_name,
    c.last_name,
    c.created_at          AS customer_created

FROM wp_fa_affiliates a

LEFT JOIN wp_fa_referrals r
    ON r.affiliate_id = a.id

LEFT JOIN wp_fa_customers c
    ON c.id = r.customer_id

ORDER BY
    a.id ASC,
    r.created_at DESC;