Mysql join query improvement and GROUP_CONCAT on GROUP BY
Generally, you should avoid JOIN ( SELECT ... ).  In the past it was notoriously inefficient.  In later versions it is sometimes somewhat slower than alternatives.

Instead, let's get rid of the JOIN some of the time:

...
    Ifnull(a.transfered, b.transfered)   AS transfer,
    ...
FROM   cdr_temp a
   LEFT OUTER JOIN (SELECT USER,
                           Substring(number_dialed, 18, 11) AS transfered,
                           uniqueid
                    FROM   transfertable)
                   b
                ON a.uniqueid = b.uniqueid
WHERE  ...
GROUP  BY a.lead_id,
          b.uniqueid


-->

...
    Ifnull(a.transfered, 
             ( SELECT substring... FROM transfertable
                          WHERE uniqueid = a.uniqueid
             )
          )   AS transfer,
    ...
FROM   cdr_temp a
   -- gone:  LEFT OUTER JOIN
WHERE  ...
GROUP  BY a.lead_id,
          a.uniqueid  -- Note "a."


It is "improper" to GROUP BY only some of the non-aggregate columns; you will get random values for the others.  Is a "skill" associated with a "lead"?

It would be more informative to have SHOW CREATE TABLE for each table.  What are the PRIMARY KEYs of the tables?  Are there any other UNIQUE columns?