Beispiel #1
0
def update_user_rank_cutoffs(cur: MySQLdb.cursors.BaseCursor,
                             scores: Sequence[float]) -> None:
    '''Updates the user ranking cutoff table.'''

    cur.execute('DELETE FROM `User_Rank_Cutoffs`;')
    logging.info('Updating ranking cutoffs...')
    cutoffs = [
        Cutoff(.01, 'user-rank-international-master'),
        Cutoff(.09, 'user-rank-master'),
        Cutoff(.15, 'user-rank-expert'),
        Cutoff(.35, 'user-rank-specialist'),
        Cutoff(.40, 'user-rank-beginner'),
    ]
    if not scores:
        return
    for cutoff in cutoffs:
        # Scores are already in descending order. That will also bias the
        # cutoffs towards higher scores.
        cur.execute(
            '''
                    INSERT INTO
                        `User_Rank_Cutoffs` (`score`, `percentile`,
                                             `classname`)
                    VALUES(%s, %s, %s);''',
            (scores[int(len(scores) * cutoff.percentile)], cutoff.percentile,
             cutoff.classname))
Beispiel #2
0
def update_school_rank(cur: MySQLdb.cursors.BaseCursor) -> None:
    '''Updates the school rank'''

    logging.info('Updating school rank...')
    cur.execute('''
        SELECT
            `s`.`school_id`,
            SUM(ROUND(100 / LOG(2, `distinct_school_problems`.accepted+1), 0))
            AS `score`
        FROM
            `Schools` AS `s`
        INNER JOIN
            (
                SELECT
                    `su`.`school_id`,
                    `p`.accepted,
                    MIN(`su`.time)
                FROM
                    `Submissions` AS `su`
                INNER JOIN
                    `Runs` AS `r` ON `r`.run_id = `su`.current_run_id
                INNER JOIN
                    `Problems` AS `p` ON `p`.`problem_id` = `su`.`problem_id`
                WHERE
                    `r`.verdict = "AC"
                    AND `p`.visibility >= 1
                    AND `su`.`school_id` IS NOT NULL
                GROUP BY
                    `su`.`school_id`,
                    `su`.`problem_id`
            ) AS `distinct_school_problems`
        ON
            `distinct_school_problems`.`school_id` = `s`.`school_id`
        GROUP BY
            `s`.`school_id`
        ORDER BY
            `score` DESC;
    ''')
    prev_score = None
    rank = 0

    for index, row in enumerate(cur):
        if row['score'] != prev_score:
            rank = index + 1
        prev_score = row['score']
        cur.execute(
            '''
                        UPDATE
                            `Schools` AS `s`
                        SET
                            `s`.`score` = %s,
                            `s`.`ranking` = %s
                        WHERE
                            `s`.`school_id` = %s;
                    ''', (row['score'], rank, row['school_id']))
Beispiel #3
0
def update_schools_solved_problems(cur: MySQLdb.cursors.BaseCursor) -> None:
    '''Updates the solved problems count by each school the last 6 months'''

    logging.info('Updating schools solved problems...')

    months = 6  # in case this parameter requires adjustments
    cur.execute('DELETE FROM `Schools_Problems_Solved_Per_Month`')
    cur.execute(
        '''
        INSERT INTO
            `Schools_Problems_Solved_Per_Month` (
                `school_id`,
                `time`,
                `problems_solved`
            )
        SELECT
            `sc`.`school_id`,
            STR_TO_DATE(
                CONCAT (
                    YEAR(`su`.`time`), '-', MONTH(`su`.`time`), '-01'
                ),
                "%%Y-%%m-%%d"
            ) AS `time`,
            COUNT(DISTINCT `su`.`problem_id`) AS `problems_solved`
        FROM
            `Submissions` AS `su`
        INNER JOIN
            `Schools` AS `sc` ON `sc`.`school_id` = `su`.`school_id`
        INNER JOIN
            `Runs` AS `r` ON `r`.`run_id` = `su`.`current_run_id`
        INNER JOIN
            `Problems` AS `p` ON `p`.`problem_id` = `su`.`problem_id`
        WHERE
            `su`.`time` >= CURDATE() - INTERVAL %s MONTH
            AND `r`.`verdict` = "AC" AND `p`.`visibility` >= 1
            AND NOT EXISTS (
                SELECT
                    *
                FROM
                    `Submissions` AS `sub`
                INNER JOIN
                    `Runs` AS `ru` ON `ru`.`run_id` = `sub`.`current_run_id`
                WHERE
                    `sub`.`problem_id` = `su`.`problem_id`
                    AND `sub`.`identity_id` = `su`.`identity_id`
                    AND `ru`.`verdict` = "AC"
                    AND `sub`.`time` < `su`.`time`
            )
        GROUP BY
            `sc`.`school_id`,
            `time`
        ORDER BY
            `time` ASC;
    ''', (months, ))
Beispiel #4
0
def update_author_rank(cur: MySQLdb.cursors.BaseCursor) -> None:
    '''Updates the author's ranking'''
    logging.info('Updating authors ranking...')
    cur.execute('''
        SELECT
            `u`.`user_id`,
            `i`.`username`,
            `i`.`name`,
            `i`.`country_id`,
            `i`.`state_id`,
            `isc`.`school_id`,
            SUM(`p`.`quality`) AS `author_score`
        FROM
            `Problems` AS `p`
        INNER JOIN
            `ACLs` AS `a` ON `a`.`acl_id` = `p`.`acl_id`
        INNER JOIN
            `Users` AS `u` ON `u`.`user_id` = `a`.`owner_id`
        INNER JOIN
            `Identities` AS `i` ON `i`.`identity_id` = `u`.`main_identity_id`
        LEFT JOIN
            `Identities_Schools` AS `isc`
        ON
            `isc`.`identity_school_id` = `i`.`current_identity_school_id`
        WHERE
            `p`.`quality` IS NOT NULL
        GROUP BY
            `u`.`user_id`
        ORDER BY
            `author_score` DESC
    ''')

    prev_score = None
    rank = 0
    for index, row in enumerate(cur):
        if row['author_score'] != prev_score:
            rank = index + 1
        prev_score = row['author_score']
        cur.execute(
            '''
                    INSERT INTO
                        `User_Rank` (`user_id`, `username`, `author_score`,
                                     `author_ranking`, `name`, `country_id`,
                                     `state_id`, `school_id`)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                    ON DUPLICATE KEY
                        UPDATE
                            author_ranking = %s,
                            author_score = %s;''',
            (row['user_id'], row['username'], row['author_score'], rank,
             row['name'], row['country_id'], row['state_id'], row['school_id'],
             rank, row['author_score']))
Beispiel #5
0
def update_problem_accepted_stats(cur: MySQLdb.cursors.BaseCursor) -> None:
    '''Updates the problem accepted stats'''

    logging.info('Updating accepted stats for problems...')
    cur.execute('''
        UPDATE
            `Problems` AS `p`
        SET
            `p`.accepted = (
                SELECT
                    COUNT(DISTINCT `s`.`identity_id`)
                FROM
                    `Submissions` AS `s`
                INNER JOIN
                    `Runs` AS `r`
                ON
                    `r`.run_id = `s`.current_run_id
                INNER JOIN
                    `Identities` AS `i`
                ON
                    `i`.`identity_id` = `s`.`identity_id`
                INNER JOIN
                    `Users` AS `u`
                ON
                    `u`.`user_id` = `i`.`user_id`
                WHERE
                    `s`.`problem_id` = `p`.`problem_id` AND `r`.verdict = 'AC'
                    AND NOT EXISTS (
                        SELECT
                            `pf`.`problem_id`, `pf`.`user_id`
                        FROM
                            `Problems_Forfeited` AS `pf`
                        WHERE
                            `pf`.`problem_id` = `p`.`problem_id` AND
                            `pf`.`user_id` = `u`.`user_id`
                    )
                    AND NOT EXISTS (
                        SELECT
                            `a`.`acl_id`
                        FROM
                            `ACLs` AS `a`
                        WHERE
                            `a`.`acl_id` = `p`.`acl_id` AND
                            `a`.`owner_id` = `u`.`user_id`
                    )
            );
    ''')
Beispiel #6
0
def update_user_rank(cur: MySQLdb.cursors.BaseCursor) -> Sequence[float]:
    '''Updates the user ranking.'''

    cur.execute('DELETE FROM `User_Rank`;')
    logging.info('Updating user rank...')
    cur.execute('''
        SELECT
            `i`.`username`,
            `i`.`name`,
            `i`.`country_id`,
            `i`.`state_id`,
            `isc`.`school_id`,
            `up`.`identity_id`,
            `i`.`user_id`,
            COUNT(`p`.`problem_id`) AS `problems_solved_count`,
            SUM(ROUND(100 / LOG(2, `p`.`accepted` + 1) , 0)) AS `score`
        FROM
        (
            SELECT DISTINCT
                `s`.`identity_id`,
                `s`.`problem_id`
            FROM
                `Submissions` AS `s`
            INNER JOIN
                `Runs` AS `r`
            ON
                `r`.run_id = `s`.current_run_id
            WHERE
                `r`.verdict = 'AC' AND `s`.type = 'normal'
        ) AS up
        INNER JOIN
            `Problems` AS `p`
        ON `p`.`problem_id` = up.`problem_id` AND `p`.visibility > 0
        INNER JOIN
            `Identities` AS `i` ON `i`.`identity_id` = up.`identity_id`
        LEFT JOIN
            `Identities_Schools` AS `isc`
        ON
            `isc`.`identity_school_id` = `i`.`current_identity_school_id`
        INNER JOIN
            `Users` AS `u` ON `u`.`user_id` = `i`.`user_id`
        WHERE
            `u`.`is_private` = 0
            AND NOT EXISTS (
                SELECT
                    `pf`.`problem_id`, `pf`.`user_id`
                FROM
                    `Problems_Forfeited` AS `pf`
                WHERE
                    `pf`.`problem_id` = `p`.`problem_id` AND
                    `pf`.`user_id` = `u`.`user_id`
            )
            AND NOT EXISTS (
                SELECT
                    `a`.`acl_id`
                FROM
                    `ACLs` AS `a`
                WHERE
                    `a`.`acl_id` = `p`.`acl_id` AND
                    `a`.`owner_id` = `u`.`user_id`
            )
        GROUP BY
            `identity_id`
        ORDER BY
            `score` DESC;
    ''')
    prev_score = None
    rank = 0
    # MySQL has no good way of obtaining percentiles, so we'll store the sorted
    # list of scores in order to calculate the cutoff scores later.
    scores = []
    for index, row in enumerate(cur):
        if row['score'] != prev_score:
            rank = index + 1
        scores.append(row['score'])
        prev_score = row['score']
        cur.execute(
            '''
                    INSERT INTO
                        `User_Rank` (`user_id`, `ranking`,
                                     `problems_solved_count`, `score`,
                                     `username`, `name`, `country_id`,
                                     `state_id`, `school_id`)
                    VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s);''',
            (row['user_id'], rank, row['problems_solved_count'], row['score'],
             row['username'], row['name'], row['country_id'], row['state_id'],
             row['school_id']))
    return scores
Beispiel #7
0
def update_coder_of_the_month_candidates(
        cur: MySQLdb.cursors.BaseCursor,
        first_day_of_current_month: datetime.date, category: str) -> None:
    '''Updates the list of candidates to coder of the current month'''

    logging.info('Updating the candidates to coder of the month...')
    if first_day_of_current_month.month == 12:
        first_day_of_next_month = datetime.date(
            first_day_of_current_month.year + 1, 1, 1)
    else:
        first_day_of_next_month = datetime.date(
            first_day_of_current_month.year,
            first_day_of_current_month.month + 1, 1)

        # First make sure there are not already selected coder of the month
        cur.execute(
            '''
                SELECT
                    COUNT(*) AS `count`
                FROM
                    `Coder_Of_The_Month`
                WHERE
                    `time` = %s AND
                    `selected_by` IS NOT NULL AND
                    `category` = %s;
                ''', (first_day_of_next_month, category))
    for row in cur:
        if row['count'] > 0:
            logging.info('Skipping because already exist selected coder')
            return
    cur.execute(
        '''
                DELETE FROM
                    `Coder_Of_The_Month`
                WHERE
                    `time` = %s AND
                    `category` = %s;
                ''', (first_day_of_next_month, category))
    if category == 'female':
        gender_clause = " AND i.gender = 'female'"
    else:
        gender_clause = ""

    sql = f'''
         SELECT DISTINCT
            IFNULL(i.user_id, 0) AS user_id,
            i.username,
            IFNULL(i.country_id, 'xx') AS country_id,
            isc.school_id,
            COUNT(ps.problem_id) ProblemsSolved,
            IFNULL(SUM(ROUND(100 / LOG(2, ps.accepted+1) , 0)), 0) AS score,
            IFNULL(
                (
                    SELECT urc.classname FROM
                        User_Rank_Cutoffs urc
                    WHERE
                        urc.score <= (
                                SELECT
                                    ur.score
                                FROM
                                    User_Rank ur
                                WHERE
                                    ur.user_id = i.user_id
                            )
                    ORDER BY
                        urc.percentile ASC
                    LIMIT
                        1
                ),
                'user-rank-unranked'
            ) AS classname
          FROM
            (
              SELECT DISTINCT
                s.identity_id, s.problem_id
              FROM
                Submissions s
              INNER JOIN
                Runs r
              ON
                r.run_id = s.current_run_id
              WHERE
                r.verdict = 'AC' AND s.type= 'normal' AND
                s.time >= %s AND s.time <= %s
            ) AS up
          INNER JOIN
            Problems ps ON ps.problem_id = up.problem_id and ps.visibility >= 1
          INNER JOIN
            Identities i ON i.identity_id = up.identity_id
          LEFT JOIN
            Identities_Schools isc ON isc.identity_school_id =
            i.current_identity_school_id
          LEFT JOIN
            (
              SELECT
                user_id,
                MAX(time) latest_time,
                selected_by
              FROM
                Coder_Of_The_Month
              WHERE
                category = %s
              GROUP BY
                user_id,
                selected_by
            ) AS cm on i.user_id = cm.user_id
          WHERE
            (cm.user_id IS NULL OR
            DATE_ADD(cm.latest_time, INTERVAL 1 YEAR) < %s) AND
            i.user_id IS NOT NULL
            {gender_clause}
          GROUP BY
            up.identity_id
          ORDER BY
            score DESC,
            ProblemsSolved DESC
          LIMIT 100;
        '''
    cur.execute(sql, (
        first_day_of_current_month,
        first_day_of_next_month,
        category,
        first_day_of_next_month,
    ))

    for index, row in enumerate(cur):
        cur.execute(
            '''
                    INSERT INTO
                        `Coder_Of_The_Month` (
                            `user_id`,
                            `time`,
                            `ranking`,
                            `school_id`,
                            `category`,
                            `score`,
                            `problems_solved`
                        )
                    VALUES (
                        %s,
                        %s,
                        %s,
                        %s,
                        %s,
                        %s,
                        %s
                    );
                    ''',
            (row['user_id'], first_day_of_next_month, index + 1,
             row['school_id'], category, row['score'], row['ProblemsSolved']))
Beispiel #8
0
def update_school_of_the_month_candidates(
        cur: MySQLdb.cursors.BaseCursor,
        first_day_of_current_month: datetime.date) -> None:
    '''Updates the list of candidates to school of the current month'''

    logging.info('Updating the candidates to school of the month...')
    if first_day_of_current_month.month == 12:
        first_day_of_next_month = datetime.date(
            first_day_of_current_month.year + 1, 1, 1)
    else:
        first_day_of_next_month = datetime.date(
            first_day_of_current_month.year,
            first_day_of_current_month.month + 1, 1)

    # First make sure there are not already selected schools of the month
    cur.execute(
        '''
                SELECT
                    COUNT(*) AS `count`
                FROM
                    `School_Of_The_Month`
                WHERE
                    `time` = %s AND
                    `selected_by` IS NOT NULL;
                ''', (first_day_of_next_month, ))

    for row in cur:
        if row['count'] > 0:
            logging.info('Skipping because already exist selected schools.')
            return

    cur.execute(
        '''
                DELETE FROM
                    `School_Of_The_Month`
                WHERE
                    `time` = %s;
                ''', (first_day_of_next_month, ))

    cur.execute(
        '''
        SELECT
            `s`.`school_id`,
            IFNULL(
                SUM(
                    ROUND(
                        100 / LOG(2, `distinct_school_problems`.`accepted`+1),
                        0
                    )
                ),
                0.0
            ) AS `score`
        FROM
            `Schools` AS `s`
        INNER JOIN
            (
                SELECT
                    `su`.`school_id`,
                    `p`.`accepted`,
                    MIN(`su`.`time`) AS `first_ac_time`
                FROM
                    `Submissions` AS `su`
                INNER JOIN
                    `Runs` AS `r` ON `r`.`run_id` = `su`.`current_run_id`
                INNER JOIN
                    `Problems` AS `p` ON `p`.`problem_id` = `su`.`problem_id`
                WHERE
                    `r`.`verdict` = "AC"
                    AND `p`.`visibility` >= 1
                    AND `su`.`school_id` IS NOT NULL
                GROUP BY
                    `su`.`school_id`,
                    `su`.`problem_id`
                HAVING
                    `first_ac_time` BETWEEN %s AND %s
            ) AS `distinct_school_problems`
        ON
            `distinct_school_problems`.`school_id` = `s`.`school_id`
        WHERE
            NOT EXISTS (
                SELECT
                    `sotm`.`school_id`,
                    MAX(`time`) AS `latest_time`
                FROM
                    `School_Of_The_Month` AS `sotm`
                WHERE
                    `sotm`.`school_id` = `s`.`school_id`
                    AND (
                        `sotm`.`selected_by` IS NOT NULL OR
                        `sotm`.`ranking` = 1
                    )
                GROUP BY
                    `sotm`.`school_id`
                HAVING
                    DATE_ADD(`latest_time`, INTERVAL 1 YEAR) >= %s
            )
        GROUP BY
            `s`.`school_id`
        ORDER BY
            `score` DESC
        LIMIT 100;
        ''', (first_day_of_current_month, first_day_of_next_month,
              first_day_of_next_month))

    for index, row in enumerate(cur):
        cur.execute(
            '''
                    INSERT INTO
                        `School_Of_The_Month` (
                            `school_id`,
                            `time`,
                            `ranking`,
                            `score`
                        )
                    VALUES (
                        %s,
                        %s,
                        %s,
                        %s
                    );
                    ''', (row['school_id'], first_day_of_next_month, index + 1,
                          row['score']))
Beispiel #9
0
def update_user_rank(cur: MySQLdb.cursors.BaseCursor) -> Sequence[float]:
    '''Updates the user ranking.'''

    cur.execute('DELETE FROM `User_Rank`;')
    logging.info('Updating accepted stats for problems...')
    cur.execute('''
        UPDATE
            Problems p
        SET
            p.accepted = (
                SELECT
                    COUNT(DISTINCT s.identity_id)
                FROM
                    Submissions s
                INNER JOIN
                    Runs r
                ON
                    r.run_id = s.current_run_id
                INNER JOIN
                    Identities i
                ON
                    i.identity_id = s.identity_id
                INNER JOIN
                    Users u
                ON
                    u.user_id = i.user_id
                WHERE
                    s.problem_id = p.problem_id AND r.verdict = 'AC'
                    AND NOT EXISTS (
                        SELECT
                            pf.problem_id, pf.user_id
                        FROM
                            Problems_Forfeited pf
                        WHERE
                            pf.problem_id = p.problem_id AND
                            pf.user_id = u.user_id
                    )
                    AND NOT EXISTS (
                        SELECT
                            a.acl_id
                        FROM
                            ACLs a
                        WHERE
                            a.acl_id = p.acl_id AND
                            a.owner_id = u.user_id
                    )
            );
    ''')
    logging.info('Updating user rank...')
    cur.execute('''
        SELECT
            i.username,
            i.name,
            i.country_id,
            i.state_id,
            i.school_id,
            up.identity_id,
            i.user_id,
            COUNT(p.problem_id) problems_solved_count,
            SUM(ROUND(100 / LOG(2, p.accepted+1) , 0)) score
        FROM
        (
            SELECT DISTINCT
                s.identity_id,
                s.problem_id
            FROM
                Submissions s
            INNER JOIN
                Runs r
            ON
                r.run_id = s.current_run_id
            WHERE
                r.verdict = 'AC' AND s.type = 'normal'
        ) AS up
        INNER JOIN
            Problems p ON p.problem_id = up.problem_id AND p.visibility > 0
        INNER JOIN
            Identities i ON i.identity_id = up.identity_id
        INNER JOIN
            Users u ON u.user_id = i.user_id
        WHERE
            u.is_private = 0
            AND NOT EXISTS (
                SELECT
                    pf.problem_id, pf.user_id
                FROM
                    Problems_Forfeited pf
                WHERE
                    pf.problem_id = p.problem_id AND pf.user_id = u.user_id
            )
            AND NOT EXISTS (
                SELECT
                    a.acl_id
                FROM
                    ACLs a
                WHERE
                    a.acl_id = p.acl_id AND
                    a.owner_id = u.user_id
            )
        GROUP BY
            identity_id
        ORDER BY
            score DESC;
    ''')
    rank = 0
    prev_score = None
    # MySQL has no good way of obtaining percentiles, so we'll store the sorted
    # list of scores in order to calculate the cutoff scores later.
    scores = []
    for row in cur:
        if row['score'] != prev_score:
            rank += 1
        scores.append(row['score'])
        prev_score = row['score']
        cur.execute(
            '''
                    INSERT INTO
                        User_Rank (user_id, rank, problems_solved_count, score,
                                   username, name, country_id, state_id,
                                   school_id)
                    VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s);''',
            (row['user_id'], rank, row['problems_solved_count'], row['score'],
             row['username'], row['name'], row['country_id'], row['state_id'],
             row['school_id']))
    return scores