Query with two LEFT JOIN's is not working as expected
Let's assume your tables are defined:

CREATE TABLE affiliate_guests
(
    user_id serial not null primary key,
    code text
    -- etc.
) ;

CREATE TABLE transfers
(
    transfer_id serial not null primary key,
    user_id integer not null references affiliate_guests(user_id),
    amount numeric not null
    -- etc.
) ;

CREATE TABLE deposits
(
    deposit_id serial not null primary key,
    user_id integer not null references affiliate_guests(user_id),
    amount numeric not null,
    type text
    -- etc.
) ;


... and we populate them with some sample data:

INSERT INTO 
    affiliate_guests (user_id, code) 
VALUES
    (1, 'code') ;
INSERT INTO 
    transfers (user_id, amount) 
VALUES
    (1, 1000.00),
    (1, 2000.00),
    (1, 1234.00) ;

INSERT INTO deposits (user_id, amount, type) 
VALUES
    (1, 1000.00, 'deposit'),
    (1, 2345.00, 'ignoreit'),
    (1, 3456.00, 'ignoreit as well'),
    (1, 9999.00, 'deposit') ;


In order to understand why your query is not working as you expect, check first the result of executing your same query, but without GROUP BY, and changing your aggregates by just values (I've also added some extra ids for ease of interpretation):

SELECT 
    aff.user_id, transfer_id, t.amount AS t_amount, deposit_id, d.amount AS d_amount
FROM 
    affiliate_guests aff
    LEFT JOIN transfers t ON aff.user_id = t.user_id
    LEFT JOIN deposits d ON aff.user_id = d.user_id AND d.type = 'deposit'
WHERE
    aff.code = 'code' ;


This is what you'll get:

|---------+-------------+----------+------------+----------|
| user_id | transfer_id | t_amount | deposit_id | d_amount |
|---------+-------------+----------+------------+----------|
|    1    |      1      | 1000.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      1      | 1000.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|
|    1    |      2      | 2000.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      2      | 2000.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|
|    1    |      3      | 1234.00  |     1      | 1000.00  |
|---------+-------------+----------+------------+----------|
|    1    |      3      | 1234.00  |     4      | 9999.00  |
|---------+-------------+----------+------------+----------|


As there are not any relationship between your tables t and d, what you actually get is the cartesian product of both of them. You clearly see it because you have all possible combinations of transfer_id and deposit_id. 

You need to make this by using subquerys, instead:

SELECT 
    aff.user_id, 
    coalesce( (SELECT sum(amount) 
                 FROM transfers t 
                WHERE t.user_id = aff.user_id), 0) AS total_bet,
    coalesce( (SELECT sum(amount) 
                 FROM deposits d 
                WHERE d.user_id = aff.user_id AND d.type='deposit'), 0) AS total_deposit
FROM 
    affiliate_guests aff
WHERE
    aff.code = 'code' ;


That will get you what you expect:

|---------+-----------+-----------|
| user_id | total_bet | total_bet |
|---------+-----------+-----------|
|    1    |  4234.00  | 10999.00  |
|---------+-----------+-----------|


See Identifying and Eliminating the Dreaded Cartesian Product