challenge.sql

      SELECT
        c.id,
        count(cr.id) as count
      FROM challenges c
      LEFT OUTER JOIN exam_challenges ec ON (c.id = ec.challenge_id)
      INNER JOIN exams e ON (ec.exam_id = e.id)
      LEFT OUTER JOIN exam_delivers ed ON (ed.exam_id = e.id)
      LEFT OUTER JOIN challenge_results cr ON (c.id = cr.challenge_id AND cr.deliver_id = ed.id)
      WHERE c.user_id in (1)
      GROUP BY c.id
      ORDER BY c.id desc