foo.sql

DELETE
FROM score_employees;


INSERT INTO score_employees ( "employeeId", "questionId", "closesAt", TYPE, "companyId", "segmentIds", SCALE, "answeredAt")
  ( SELECT "employeeId",
           "questionId",
           "closesAt",
           TYPE,
           "companyId",
           "segmentIds",
           SCALE,
           "answeredAt"
   FROM
     ( SELECT DISTINCT ON ( dr."closesAt",
                            responses."employeeId",
                            questions."questionId") dr."closesAt",
                          responses."employeeId",
                          questions."questionId",
                          dr.type::VARCHAR::enum_score_employees_type,
                          dr."companyId",
                          responses."segmentIds",
                          answers.scale,
                          responses."answeredAt",
                          max(rounds."closesAt") OVER (PARTITION BY dr."closesAt",
                                                                    responses."employeeId",
                                                                    questions.driver) AS "lastDriverClosesAt"
      FROM
        ( SELECT r."companyId",
                 r.type,
                 r."closesAt"
         FROM rounds r
         GROUP BY 1,
                  2,
                  3) AS dr
      INNER JOIN rounds ON ( rounds."companyId" = dr."companyId"
                            AND rounds.type = dr.type
                            AND rounds."closesAt" <= dr."closesAt"
                            AND rounds."closesAt" > dr."closesAt" - interval '52 weeks')
      INNER JOIN responses ON responses."roundId" = rounds."roundId"
      INNER JOIN employee ON ( employee."employeeId" = responses."employeeId")
      INNER JOIN answers ON ( answers."responseId" = responses."responseId")
      INNER JOIN questions ON ( questions."questionId" = answers."questionId")
      WHERE ( responses."deletedAt" IS NULL)
        AND ( responses."answeredAt" IS NOT NULL)
        AND ( questions.type IN ('scale',
                                 'value'))
        AND ( answers.scale IS NOT NULL)
        AND ( employee."deletedAt" IS NULL
             OR employee."deletedAt" >= rounds."closesAt" - interval '12 weeks')
      ORDER BY dr."closesAt" ASC, responses."employeeId" ASC, questions."questionId" ASC, responses."answeredAt" DESC) AS i
   WHERE ( "closesAt" >= "lastDriverClosesAt" - interval '12 weeks'))