calc pvq total

calc_pvq.sql

SELECT *
        FROM (
            SELECT
            o.distributor_id,
            ui.firstname || ' ' ||ui.lastname distributor_name,
            ui.phone,
            ui.email,
            to_char(o.state_date, 'YYYY-MM') qualified_date,
            sum(o.pvq) pvq_total
            FROM user_infos ui
            LEFT JOIN (
                (
                SELECT d.id distributor_id,
                cv.order_id,
                cv.state_date,
                cv.pvq,
                o.total
                FROM data_management.commission_volume cv
                JOIN distributors d ON d.user_id = cv.user_id
                JOIN roles_users ru ON ru.user_id = d.user_id
                JOIN roles r ON r.id = ru.role_id AND r.role_code IN ('D')
                JOIN orders o ON o.id = cv.order_id
                WHERE o.distributor_id = d.id
                and cv.state_date::date between '2024-04-01' and '2024-04-30'
                ) UNION (
                SELECT d.personal_sponsor_distributor_id distributor_id,
                cv.order_id,
                cv.state_date,
                cv.pvq,
                o.total
                FROM data_management.commission_volume cv
                JOIN distributors d ON d.user_id = cv.user_id
                JOIN roles_users ru ON ru.user_id = d.user_id
                JOIN roles r ON r.id = ru.role_id AND r.role_code IN ('P','R')
                JOIN orders o ON o.id = cv.order_id
                JOIN distributors parent_d ON parent_d.id = d.personal_sponsor_distributor_id
                WHERE o.distributor_id = d.id
                and cv.state_date::date between '2024-04-01' and '2024-04-30'
                ) UNION (                             -- remove enrollment pack volume from Accelerate & Regen bonus
                SELECT d.id distributor_id,
                cv.order_id,
                cv.state_date,
                (case when cv.order_commission_state::text ilike 'reverse%' then 1 else -1 end) * li.pvq,
		o.total
                FROM data_management.commission_volume cv
                JOIN distributors d ON d.user_id = cv.user_id
                JOIN (select t0.order_id, sum(q_volume) as pvq from line_items t0, variants t1, catalog_products t2, catalogs t3 where t0.q_volume > 0 and t0.variant_id = t1.id and t1.product_id = t2.product_id and t2.role_id = 2 and  t2.catalog_id = t3.id and t3.name = 'Renewal' group by t0.order_id having sum(q_volume) >0) li ON li.order_id = cv.order_id
                JOIN orders o ON o.id = cv.order_id
                WHERE o.distributor_id = d.id
                and cv.state_date::date between '2024-04-01' and '2024-04-30'
                )
            ) o ON o.distributor_id = ui.distributor_id
            WHERE
            ui.role_code IN ('D')
            AND ui.entry_date::date <= o.state_date::date
            AND o.distributor_id = 10919401
            GROUP BY
            o.distributor_id,
            to_char(o.state_date, 'YYYY-MM'),
            ui.currency_symbol,
            ui.firstname,
            ui.lastname,
            ui.phone,
            ui.email
        ) sql
        ORDER BY pvq_total desc, distributor_id desc LIMIT 100;