Num. chats where shortlisted candidate texts first per applicant

gistfile1.txt

SELECT
  percentile_cont(chatsStarted,
    0.25) OVER()
FROM (
  SELECT
    idUserCandidate,
    COUNT(idUserCandidate) chatsStarted
  FROM (
    SELECT
      DISTINCT t1.*
    FROM (
      SELECT
        idUserCandidate,
        idJob
      FROM
        `merlin_events.Event`
      WHERE
        eventName = "Shortlist"
        AND idUserCandidate IS NOT NULL
        AND idJob IS NOT NULL) AS t1
    INNER JOIN (
      SELECT
        idCandidate,
        idJob
      FROM
        `merlin_analysis_v2.Chat`
      WHERE
        initiator = idCandidate
        AND type = "text"
      GROUP BY
        idCandidate,
        idJob) AS t2
    ON
      t1.idUserCandidate = t2.idCandidate
      AND t1.idJob = t2.idJob)
  GROUP BY
    idUserCandidate)