Compute which (MIT student) users passed the class

[n8kim1]

We store the info for it, we just don't actually do it as far as I can tell.

[j-mao]

In case it's helpful (it's probably not, but anyways)- here's the raw sql that was used in the past. An ugly monolith that should be converted into actually-readable python code.

/*
Checks the given list of MIT students to determine who has passed the class.
Passing condition: win >= 8 scrims out of any consecutive 10 against Teh Devs.

Usage:
- In this script, update the list of MIT emails
- In this script, update the team ID of the reference player
- In this script, update the timestamp of when the reference player was uploaded
- Then run this query

Shell usage: psql -U battlecode -h $DATABASE_IP -c "`cat passed_students.sql`" --csv > results.csv
*/

SELECT
  api_user.first_name,
  api_user.last_name,
  api_user.email,
  collated_results.max_wins_out_of_10,
  (collated_results.max_wins_out_of_10 >= 8) AS passed
FROM
  api_user
INNER JOIN
  api_team_users
ON
  api_user.id = api_team_users.user_id
LEFT JOIN (
  SELECT
    team_id,
    MAX(wins_out_of_10) AS max_wins_out_of_10
  FROM (
    SELECT
      (CASE WHEN s0 THEN 1 ELSE 0 END) +
        (CASE WHEN s1 THEN 1 ELSE 0 END) +
        (CASE WHEN s2 THEN 1 ELSE 0 END) +
        (CASE WHEN s3 THEN 1 ELSE 0 END) +
        (CASE WHEN s4 THEN 1 ELSE 0 END) +
        (CASE WHEN s5 THEN 1 ELSE 0 END) +
        (CASE WHEN s6 THEN 1 ELSE 0 END) +
        (CASE WHEN s7 THEN 1 ELSE 0 END) +
        (CASE WHEN s8 THEN 1 ELSE 0 END) +
        (CASE WHEN s9 THEN 1 ELSE 0 END) AS wins_out_of_10,
      team_id
    FROM (
      SELECT
                                                                        /* bluewon is 4                                                                         redwon is 5 */
        ((LAG(red_team_id, 0) OVER w = consts.ref_team_id AND LAG(status, 0) OVER w = 4) OR (LAG(blue_team_id, 0) OVER w = consts.ref_team_id AND LAG(status, 0) OVER w = 5)) AS s0,
        ((LAG(red_team_id, 1) OVER w = consts.ref_team_id AND LAG(status, 1) OVER w = 4) OR (LAG(blue_team_id, 1) OVER w = consts.ref_team_id AND LAG(status, 1) OVER w = 5)) AS s1,
        ((LAG(red_team_id, 2) OVER w = consts.ref_team_id AND LAG(status, 2) OVER w = 4) OR (LAG(blue_team_id, 2) OVER w = consts.ref_team_id AND LAG(status, 2) OVER w = 5)) AS s2,
        ((LAG(red_team_id, 3) OVER w = consts.ref_team_id AND LAG(status, 3) OVER w = 4) OR (LAG(blue_team_id, 3) OVER w = consts.ref_team_id AND LAG(status, 3) OVER w = 5)) AS s3,
        ((LAG(red_team_id, 4) OVER w = consts.ref_team_id AND LAG(status, 4) OVER w = 4) OR (LAG(blue_team_id, 4) OVER w = consts.ref_team_id AND LAG(status, 4) OVER w = 5)) AS s4,
        ((LAG(red_team_id, 5) OVER w = consts.ref_team_id AND LAG(status, 5) OVER w = 4) OR (LAG(blue_team_id, 5) OVER w = consts.ref_team_id AND LAG(status, 5) OVER w = 5)) AS s5,
        ((LAG(red_team_id, 6) OVER w = consts.ref_team_id AND LAG(status, 6) OVER w = 4) OR (LAG(blue_team_id, 6) OVER w = consts.ref_team_id AND LAG(status, 6) OVER w = 5)) AS s6,
        ((LAG(red_team_id, 7) OVER w = consts.ref_team_id AND LAG(status, 7) OVER w = 4) OR (LAG(blue_team_id, 7) OVER w = consts.ref_team_id AND LAG(status, 7) OVER w = 5)) AS s7,
        ((LAG(red_team_id, 8) OVER w = consts.ref_team_id AND LAG(status, 8) OVER w = 4) OR (LAG(blue_team_id, 8) OVER w = consts.ref_team_id AND LAG(status, 8) OVER w = 5)) AS s8,
        ((LAG(red_team_id, 9) OVER w = consts.ref_team_id AND LAG(status, 9) OVER w = 4) OR (LAG(blue_team_id, 9) OVER w = consts.ref_team_id AND LAG(status, 9) OVER w = 5)) AS s9,
        LAG(requested_at, 9) OVER w AS oldest_scrim,
        (CASE WHEN red_team_id = consts.ref_team_id THEN blue_team_id ELSE red_team_id END) AS team_id
      FROM
        api_scrimmage
      CROSS JOIN (
        VALUES (2663)  /* team id of reference player */
      ) AS consts(ref_team_id)
      WHERE
        red_team_id = consts.ref_team_id OR blue_team_id = consts.ref_team_id
      WINDOW
        w
      AS (
        PARTITION BY (
          CASE WHEN red_team_id = consts.ref_team_id THEN blue_team_id ELSE red_team_id END
        )
        ORDER BY
          id
      )
    ) AS last_10
    CROSS JOIN (
      VALUES (CAST ('2021-01-25 08:54:31.889726+00' AS TIMESTAMP))  /* time when reference player became active */
    ) AS consts(ref_timestamp)
    WHERE
      oldest_scrim >= consts.ref_timestamp OR oldest_scrim IS NULL
  ) AS result_dump
  GROUP BY
    team_id
) AS collated_results
ON
  collated_results.team_id = api_team_users.team_id
LEFT JOIN
  api_team
ON
  api_team_users.team_id = api_team.id
WHERE
  (api_user.email ILIKE '%mit.edu' AND NOT api_team.staff_team )
ORDER BY
  api_user.last_name,
  api_user.email

[j-mao]

Closed by #615