pgsql

SELECT main.login, main.timestamp, main.partner_id, main.currency, main.ref_camp, main.subtype,
       deposit, payout, main.income, dep_players_count, fd_count, bonus_amount, bonus_currency,
COUNT(reg.login) AS regs
FROM (SELECT p2.login, tr.timestamp::date, p2.partner_id, tr.currency, p1.ref_camp, tr.subtype,
SUM(tr.amount) FILTER(WHERE tr.type = 'deposit') AS deposit,
SUM(tr.amount) FILTER(WHERE tr.type = 'payout') AS payout,
SUM(tr.amount) FILTER(WHERE tr.type = 'deposit') -
SUM(tr.amount) FILTER(WHERE tr.type = 'payout') as income,
COUNT(DISTINCT tr.login) FILTER(WHERE tr.type = 'deposit') AS dep_players_count,
COUNT(tr.amount)
    FILTER (WHERE tr.type = 'deposit' AND tr.subtype = 'first') AS fd_count
    FROM transactions_paysys tr
INNER JOIN player p1 ON p1.login = tr.login
INNER JOIN player p2 ON p2.partner_id = p1.ref_id::integer
    WHERE p1.ref_id IS NOT NULL
    AND tr.status = 'success'
    AND tr.timestamp::date = CURRENT_DATE - INTERVAL '4' DAY
GROUP BY tr.timestamp::date, p2.partner_id, tr.currency, p2.login, p1.ref_camp, tr.subtype
ORDER BY tr.timestamp::date) main
INNER JOIN anon_user_transitions visit
    ON visit.ref_id::integer = main.partner_id AND visit.timestamp::date = main.timestamp::date
INNER JOIN player reg ON reg.ref_id::integer = main.partner_id AND reg.registration_date::date = main.timestamp::date
INNER JOIN (
    SELECT bonus.time_end::date, p2.partner_id bonus_partner_id, p2.login bonus_partner_login, bonus.currency bonus_currency,
    SUM(bonus.amount) FILTER(WHERE type = 'unlocked' AND status != 'interrupted') AS bonus_amount
        FROM bonus
    INNER JOIN player p1 ON p1.login = bonus.login
    INNER JOIN player p2 ON p2.partner_id = p1.ref_id::integer
    GROUP BY p2.partner_id, p2.login, bonus.currency, bonus.time_end::date
    ) AS bonus ON (bonus.bonus_partner_id = main.partner_id AND bonus.time_end::date = main.timestamp::date)
GROUP BY main.timestamp::date, main.partner_id, main.currency, main.login, main.ref_camp, main.subtype,
         main.deposit, main.payout, main.income, main.dep_players_count, main.fd_count,
         bonus_currency, bonus_amount
ORDER BY main.timestamp::date;