typeorm#createQueryBuilder TypeScript Examples

// IDs where they are the first for each year starting from 2007

function createUserQuery (year, modeId, i) : SelectQueryBuilder<User> {
    let query = User
        .createQueryBuilder("user")
        .innerJoin("user.beatmapsets", "beatmapset","beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
        .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId });
    if (i === yearIDthresholds.length - 1)
        query = query
            .andWhere(`user.osuUserid >= ${yearIDthresholds[i]}`);
    else
        query = query
            .andWhere(`user.osuUserid >= ${yearIDthresholds[i]} and user.osuUserid < ${yearIDthresholds[i + 1]}`);
    return query;
}

/**
   * Create Sub Query.
   */
  private createSubQuery(isMain: boolean = false) {
    const queryBuilder: SelectQueryBuilder<T> = this.qb
      .createQueryBuilder()
      .subQuery()
    
    if (isMain) {
      queryBuilder.from(this.entityType, this.qb.alias);
    }

    const query = new QueryBuilder<T, T>(
      this.entityType,
      this.queryObject,
      this.qb.alias,
    );

    query.qb = queryBuilder;

    return query;
  }

constructor(
    private entityType: Constructor<T> | string | Function,
    entityAlias: string,
    private queryObject?,
  ) {
    if (queryObject && queryObject.page && isNum(queryObject.page)) {
      this.page = Number(queryObject.page);
    }
    if (queryObject && queryObject.limit && isNum(queryObject.limit)) {
      this.limit = Number(queryObject.limit);
    }
    if (entityAlias) {
      this.qb = createQueryBuilder(entityType, entityAlias);
    }
  }

TotalStudents: InsightObject = {
  displayName: 'Total Students',
  description:
    'What is the total number of students that are enrolled in the course?',
  roles: [Role.PROFESSOR],
  component: InsightComponent.SimpleDisplay,
  size: 'small' as const,
  async compute(filters): Promise<SimpleDisplayOutputType> {
    return await addFilters({
      query: createQueryBuilder(UserCourseModel).where("role = 'student'"),
      modelName: UserCourseModel.name,
      allowedFilters: ['courseId', 'role'],
      filters,
    }).getCount();
  },
}

TotalQuestionsAsked: InsightObject = {
  displayName: 'Total Questions',
  description: 'How many questions have been asked in total?',
  roles: [Role.PROFESSOR],
  component: InsightComponent.SimpleDisplay,
  size: 'small' as const,
  async compute(filters): Promise<SimpleDisplayOutputType> {
    return await addFilters({
      query: createQueryBuilder(QuestionModel).select(),
      modelName: QuestionModel.name,
      allowedFilters: ['courseId', 'timeframe'],
      filters,
    }).getCount();
  },
}

MostActiveStudents: InsightObject = {
  displayName: 'Most Active Students',
  description:
    'Who are the students who have asked the most questions in Office Hours?',
  roles: [Role.PROFESSOR],
  component: InsightComponent.SimpleTable,
  size: 'default' as const,
  async compute(filters, cacheManager: Cache): Promise<SimpleTableOutputType> {
    const dataSource = await getCachedActiveStudents(cacheManager, filters);
    const totalStudents: number = await addFilters({
      query: createQueryBuilder(UserCourseModel).where("role = 'student'"),
      modelName: UserCourseModel.name,
      allowedFilters: ['courseId', 'role'],
      filters,
    }).getCount();
    return {
      columns: [
        {
          title: 'Name',
          dataIndex: 'name',
          key: 'name',
        },
        {
          title: 'Questions Asked',
          dataIndex: 'questionsAsked',
          key: 'questionsAsked',
        },
      ],
      dataSource,
      totalStudents,
    };
  },
}

getActiveStudents = async (filters: Filter[]): Promise<any[]> => {
  const activeStudents = await addFilters({
    query: createQueryBuilder()
      .select('"QuestionModel"."creatorId"', 'studentId')
      .addSelect(
        'concat("UserModel"."firstName", \' \',"UserModel"."lastName")',
        'name',
      )
      .addSelect('"UserModel"."email"', 'email')
      .addSelect('COUNT(*)', 'questionsAsked')
      .from(QuestionModel, 'QuestionModel')
      .where('"QuestionModel"."questionType" IS NOT NULL'),
    modelName: QuestionModel.name,
    allowedFilters: ['courseId', 'timeframe'],
    filters,
  })
    .innerJoin(
      UserModel,
      'UserModel',
      '"UserModel".id = "QuestionModel"."creatorId"',
    )
    .groupBy('"QuestionModel"."creatorId"')
    .addGroupBy('name')
    .addGroupBy('"UserModel".email')
    .orderBy('4', 'DESC')
    .getRawMany();
  return activeStudents;
}

MedianWaitTime: InsightObject = {
  displayName: 'Median Wait Time',
  description:
    'What is the median wait time for a student to get help in the queue?',
  roles: [Role.PROFESSOR],
  component: InsightComponent.SimpleDisplay,
  size: 'small' as const,
  async compute(filters): Promise<SimpleDisplayOutputType> {
    const questions = await addFilters({
      query: createQueryBuilder(QuestionModel)
        .select()
        .where('QuestionModel.firstHelpedAt IS NOT NULL'),
      modelName: QuestionModel.name,
      allowedFilters: ['courseId', 'timeframe'],
      filters,
    }).getMany();

    if (questions.length === 0) {
      return `0 min`;
    }

    const waitTimes = questions.map(
      (question) =>
        Math.floor(
          (question.firstHelpedAt.getTime() - question.createdAt.getTime()) /
            1000,
        ) / 60,
    );

    return `${Math.floor(Math.round(median(waitTimes)))} min`;
  },
}

MedianHelpingTime: InsightObject = {
  displayName: 'Median Helping Time',
  description:
    'What is the median duration that a TA helps a student on a call?',
  roles: [Role.PROFESSOR],
  component: InsightComponent.SimpleDisplay,
  size: 'small' as const,

  async compute(filters): Promise<SimpleDisplayOutputType> {
    const questions = await addFilters({
      query: createQueryBuilder(QuestionModel)
        .select()
        .where(
          'QuestionModel.helpedAt IS NOT NULL AND QuestionModel.closedAt IS NOT NULL',
        ),
      modelName: QuestionModel.name,
      allowedFilters: ['courseId', 'timeframe'],
      filters,
    }).getMany();

    if (questions.length === 0) {
      return `0 min`;
    }

    const helpTimes = questions.map(
      (question) =>
        Math.floor(
          (question.closedAt.getTime() - question.helpedAt.getTime()) / 1000,
        ) / 60,
    );

    return `${Math.round(median(helpTimes))} min`;
  },
}

QuestionTypeBreakdown: InsightObject = {
  displayName: 'Question Type Breakdown',
  description:
    'What is the distribution of student-selected question-types on the question form?',
  roles: [Role.PROFESSOR],
  component: InsightComponent.BarChart,
  size: 'default' as const,
  async compute(filters): Promise<BarChartOutputType> {
    const info = await addFilters({
      query: createQueryBuilder(QuestionModel)
        .select('"QuestionModel"."questionType"', 'questionType')
        .addSelect('COUNT(*)', 'totalQuestions')
        .andWhere('"QuestionModel"."questionType" IS NOT NULL'),
      modelName: QuestionModel.name,
      allowedFilters: ['courseId', 'timeframe'],
      filters,
    })
      .groupBy('"QuestionModel"."questionType"')
      .having('"QuestionModel"."questionType" IS NOT NULL')
      .getRawMany();

    const typesFromInfo = info.map((obj) => obj['questionType']);

    info.forEach((pair) => {
      pair['totalQuestions'] = Number.parseInt(pair['totalQuestions']);
    });

    Object.values(QuestionType).forEach((v) => {
      if (!typesFromInfo.includes(v)) {
        info.push({ questionType: v, totalQuestions: 0 });
      }
    });

    const insightObj = {
      data: info.sort((a, b) =>
        a.questionType === b.questionType
          ? 0
          : a.questionType > b.questionType
          ? 1
          : -1,
      ),
      xField: 'totalQuestions',
      yField: 'questionType',
      seriesField: 'questionType',
      xAxisName: 'totalQuestions',
      yAxisName: 'questionType',
    };
    return insightObj;
  },
}

recordsRouter.get("/mappers", async (ctx) => {
    if (await ctx.cashed())
        return;

    const year = parseInt(parseQueryParam(ctx.query.year) || "") || new Date().getUTCFullYear();
    const modeString: string = parseQueryParam(ctx.query.mode) || "standard";
    const modeId = ModeDivisionType[modeString];

    const [
        mostRanked,
        mostDiffs,
        mostFavs,
        mostFavsExclHybrid,
        mostPlayed,
        highestAvgSr,
        lowestAvgSr,
    ] = await Promise.all([
        // Most Ranked
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmapset.ID", "beatmapsetId")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapsetId");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("COUNT(sub.beatmapsetID)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "DESC")
            .limit(3)
            .getRawMany(),
        
        // Most Total Difficulties Ranked
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmap.ID", "beatmapId")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapId");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("COUNT(sub.beatmapId)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "DESC")
            .limit(3)
            .getRawMany(),

        // Most Favourited
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmapset.ID", "beatmapsetId")
                    .addSelect("beatmapset.favourites", "favourites")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapset.ID")
                    .addGroupBy("beatmapset.favourites");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("SUM(sub.favourites)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "DESC")
            .limit(3)
            .getRawMany(),

        // Most Favourited (excl. Hybrids)
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .andWhere((qb) => {
                        const subQuery = qb.subQuery()
                            .from(Beatmap, "refMap")
                            .where("refMap.beatmapsetID = beatmapset.ID")
                            .andWhere("refMap.mode != :mode", { mode: modeId })
                            .getQuery();
        
                        return "NOT EXISTS " + subQuery;
                    })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmapset.ID", "beatmapsetId")
                    .addSelect("beatmapset.favourites", "favourites")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapset.ID")
                    .addGroupBy("beatmapset.favourites");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("SUM(sub.favourites)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "DESC")
            .limit(3)
            .getRawMany(),

        // Most Played
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmapset.ID", "beatmapsetId")
                    .addSelect("beatmap.playCount", "playCount")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapset.ID")
                    .addGroupBy("beatmap.playCount");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("SUM(sub.playCount)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "DESC")
            .limit(3)
            .getRawMany(),

        // Highest Avg SR
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmapset.ID", "beatmapsetId")
                    .addSelect("beatmap.totalSR", "totalSR")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapset.ID")
                    .addGroupBy("beatmap.totalSR");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("AVG(sub.totalSR)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "DESC")
            .limit(3)
            .getRawMany(),

        // Lowest Avg SR
        createQueryBuilder()
            .from(sub => {
                return sub
                    .from("beatmapset", "beatmapset")
                    .innerJoin("beatmapset.creator", "creator")
                    .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                    .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                    .select("creator.osuUsername", "username")
                    .addSelect("creator.osuUserid", "osuId")
                    .addSelect("beatmapset.ID", "beatmapsetId")
                    .addSelect("beatmap.totalSR", "totalSR")
                    .groupBy("creator.osuUsername")
                    .addGroupBy("creator.osuUserid")
                    .addGroupBy("beatmapset.ID")
                    .addGroupBy("beatmap.totalSR");
            }, "sub")
            .select("sub.username", "username")
            .addSelect("sub.osuId", "osuId")
            .addSelect("AVG(sub.totalSR)", "value")
            .groupBy("sub.username")
            .addGroupBy("sub.osuId")
            .orderBy("value", "ASC")
            .limit(3)
            .getRawMany(),
    ]);

    const records: Record<string, MapperRecord[]> = {
        mostRanked,
        mostDiffs,
        mostPlayed,
        mostFavs,
        mostFavsExclHybrid,
        highestAvgSr: highestAvgSr.map(o => valueToFixed(o)),
        lowestAvgSr: lowestAvgSr.map(o => valueToFixed(o)),
    };

    ctx.body = records;
});

statisticsRouter.get("/beatmapsets", async (ctx) => {
    if (await ctx.cashed())
        return;

    const year = parseInt(parseQueryParam(ctx.query.year) || "") || new Date().getUTCFullYear();
    const modeString: string = parseQueryParam(ctx.query.mode) || "standard";
    const modeId = ModeDivisionType[modeString];

    // Create loops for AR/OD/CS/HP/SR stats
    const [CSq, ARq, ODq, HPq, SRq]: [Promise<any>[], Promise<any>[], Promise<any>[], Promise<any>[], Promise<any>[]] = [[], [], [], [], []];
    for (let i = 0; i < 11; i++) {
        if (modeId === ModeDivisionType.mania) {
            if (i > 3 && i < 10)
                CSq.push(Beatmapset
                    .queryStatistic(year, modeId)
                    .andWhere(`beatmap.circleSize = ${i}`)
                    .select("COUNT(beatmap.circleSize)", "value")
                    .addSelect(`'${i} Keys'`, "constraint")
                    .orderBy("value", "DESC")
                    .getRawOne()
                );
        } else {
            CSq.push(Beatmapset
                .queryStatistic(year, modeId)
                .andWhere(`beatmap.circleSize between ${i} and ${i + 0.9}`)
                .select("COUNT(beatmap.circleSize)", "value")
                .addSelect(`'CS ${i} ${i !== 10 ? "- " + (i + 0.9) : ""}'`, "constraint")
                .orderBy("value", "DESC")
                .getRawOne()
            );
        }

        ARq.push(Beatmapset
            .queryStatistic(year, modeId)
            .andWhere(`beatmap.approachRate between ${i} and ${i + 0.9}`)
            .select("COUNT(beatmap.approachRate)", "value")
            .addSelect(`'AR ${i} ${i !== 10 ? "- " + (i + 0.9) : ""}'`, "constraint")
            .orderBy("value", "DESC")
            .getRawOne()
        );
        ODq.push(Beatmapset
            .queryStatistic(year, modeId)
            .andWhere(`beatmap.overallDifficulty between ${i} and ${i + 0.9}`)
            .select("COUNT(beatmap.overallDifficulty)", "value")
            .addSelect(`'OD ${i} ${i !== 10 ? "- " + (i + 0.9) : ""}'`, "constraint")
            .orderBy("value", "DESC")
            .getRawOne()
        );
        HPq.push(Beatmapset
            .queryStatistic(year, modeId)
            .andWhere(`beatmap.hpDrain between ${i} and ${i + 0.9}`)
            .select("COUNT(beatmap.hpDrain)", "value")
            .addSelect(`'HP ${i} ${i !== 10 ? "- " + (i + 0.9) : ""}'`, "constraint")
            .orderBy("value", "DESC")
            .getRawOne()
        );
        if (i === 10)
            SRq.push(Beatmapset
                .queryStatistic(year, modeId)
                .andWhere(`beatmap.totalSR >= 10`)
                .select("COUNT(beatmap.totalSR)", "value")
                .addSelect(`'10+ SR'`, "constraint")
                .orderBy("value", "DESC")
                .getRawOne()
            );
        else
            SRq.push(Beatmapset
                .queryStatistic(year, modeId)
                .andWhere(`beatmap.totalSR between ${i} and ${i + 0.9}`)
                .select("COUNT(beatmap.totalSR)", "value")
                .addSelect(`'${i} - ${i + 0.9} SR'`, "constraint")
                .orderBy("value", "DESC")
                .getRawOne()
            );
    }

    const [yearQ, mapsQ]: [Promise<any>[], Promise<any>[]] = [[], []];
    for (let i = 0; i < yearIDthresholds.length; i++) {
        if (i + 2007 > year)
            break;
        yearQ.push(Beatmapset
            .queryStatistic(year, modeId)
            .andWhere(`year(beatmapset.submitDate) = ${i + 2007}`)
            .select("count(distinct beatmapset.submitDate)", "value")
            .addSelect(`'Maps Submitted in ${i + 2007}'`, "constraint")
            .getRawOne()
        );

        let query = User
            .createQueryBuilder("user")
            .innerJoin("user.beatmapsets", "beatmapset","beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
            .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId });
        if (i === yearIDthresholds.length - 1)
            query = query
                .andWhere(`user.osuUserid >= ${yearIDthresholds[i]}`);
        else
            query = query
                .andWhere(`user.osuUserid >= ${yearIDthresholds[i]} and user.osuUserid < ${yearIDthresholds[i + 1]}`);

        mapsQ.push(query
            .select("count(distinct beatmapset.ID)", "value")
            .addSelect(`'Maps Ranked by ${i + 2007} Users'`, "constraint")
            .getRawOne()
        );
    }


    const query = createQueryBuilder()
        .from(sub => {
            return sub
                .from("beatmapset", "beatmapset")
                .innerJoin("beatmapset.creator", "creator")
                .innerJoin("beatmapset.beatmaps", "beatmap", "beatmap.mode = :mode", { mode: modeId })
                .where("beatmapset.approvedDate BETWEEN :start AND :end", { start: new Date(year, 0, 1), end: new Date(year + 1, 0, 1) })
                .select("beatmapset.ID", "beatmapsetID")
                .addSelect("beatmap.ID", "beatmapID");
        }, "sub");
    const [
        totalSets,
        totalDiffs,

        // Difficulties
        totalEasies,
        totalMediums,
        totalHards,
        totalInsanes,
        totalExtras,
        totalExpertPlus,

        years,

        // CS AR OD HP SR
        CS,
        AR,
        OD,
        HP,
        SR,

        // Total sliders/circles
        totalSliders,
        avgSlidersPerMapset,
        avgSlidersPerDiff,

        totalCircles,
        avgCirclesPerMapset,
        avgCirclesPerDiff,

        setsToDifficulties,

        avgSR,

    ] = await Promise.all([
        // Total ranked
        query
            .select("COUNT(distinct sub.beatmapsetID)", "value")
            .addSelect("'Ranked Sets'", "constraint")
            .getRawOne(),
        query
            .select("COUNT(distinct sub.beatmapID)", "value")
            .addSelect("'Ranked Difficulties'", "constraint")
            .getRawOne(),

        // Difficulties
        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere("beatmap.totalSR < 2")
            .select("COUNT(beatmap.totalSR)", "value")
            .addSelect("'Easy Difficulty Icons'", "constraint")
            .orderBy("value", "DESC")
            .getRawOne(),
        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere("beatmap.totalSR >= 2 and beatmap.totalSR < 2.7")
            .select("COUNT(beatmap.totalSR)", "value")
            .addSelect("'Normal Difficulty Icons'", "constraint")
            .orderBy("value", "DESC")
            .getRawOne(),
        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere("beatmap.totalSR >= 2.7 and beatmap.totalSR < 4")
            .select("COUNT(beatmap.totalSR)", "value")
            .addSelect("'Hard Difficulty Icons'", "constraint")
            .orderBy("value", "DESC")
            .getRawOne(),
        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere("beatmap.totalSR >= 4 and beatmap.totalSR < 5.3")
            .select("COUNT(beatmap.totalSR)", "value")
            .addSelect("'Insane Difficulty Icons'", "constraint")
            .orderBy("value", "DESC")
            .getRawOne(),
        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere("beatmap.totalSR >= 5.3 and beatmap.totalSR < 6.5")
            .select("COUNT(beatmap.totalSR)", "value")
            .addSelect("'Extra Difficulty Icons'", "constraint")
            .orderBy("value", "DESC")
            .getRawOne(),
        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere("beatmap.totalSR >= 6.5")
            .select("COUNT(beatmap.totalSR)", "value")
            .addSelect("'Extra+ Difficulty Icons'", "constraint")
            .orderBy("value", "DESC")
            .getRawOne(),

        Promise.all(yearQ),

        // CS AR OD HP
        Promise.all(CSq),
        Promise.all(ARq),
        Promise.all(ODq),
        Promise.all(HPq),
        Promise.all(SRq),


        // Total Sliders
        Beatmapset
            .queryStatistic(year, modeId)
            .select("SUM(beatmap.sliders)", "value")
            .addSelect("'Sliders Ranked'", "constraint")
            .getRawOne(),

        // Avg Sliders per Mapset
        Beatmapset
            .queryStatistic(year, modeId)
            .select("SUM(beatmap.sliders)/COUNT(DISTINCT beatmap.beatmapsetID)", "value")
            .addSelect("'Sliders per Set'", "constraint")
            .getRawOne(),

        // Avg Sliders per Diff
        Beatmapset
            .queryStatistic(year, modeId)
            .select("SUM(beatmap.sliders)/COUNT(beatmap.ID)", "value")
            .addSelect("'Sliders per Diff'", "constraint")
            .getRawOne(),


        // Total Circles
        Beatmapset
            .queryStatistic(year, modeId)
            .select("SUM(beatmap.circles)", "value")
            .addSelect("'Circles Ranked'", "constraint")
            .getRawOne(),

        // Avg Circles per Mapset
        Beatmapset
            .queryStatistic(year, modeId)
            .select("SUM(beatmap.circles)/COUNT(DISTINCT beatmap.beatmapsetID)", "value")
            .addSelect("'Circles per Set'", "constraint")
            .getRawOne(),

        // Avg Circles per Diff
        Beatmapset
            .queryStatistic(year, modeId)
            .select("SUM(beatmap.circles)/COUNT(beatmap.ID)", "value")
            .addSelect("'Circles per Diff'", "constraint")
            .getRawOne(),

        // Ratio of Sets to Difficulties
        Beatmapset
            .queryStatistic(year, modeId)
            .select("COUNT(beatmap.ID)/COUNT(DISTINCT beatmap.beatmapsetID)", "value")
            .addSelect("'Diffs per Set'", "constraint")
            .getRawOne(),

        // Average SR
        Beatmapset
            .queryStatistic(year, modeId)
            .select("ROUND(AVG(totalSR), 2)", "value")
            .addSelect("'SR Ranked'", "constraint")
            .getRawOne(),
    ]);

    const statistics: Record<string, Statistic[]> = {
        totalRanked: [
            totalSets,
            totalDiffs,
        ],
        sliders: [totalSliders, valueToFixed(avgSlidersPerMapset), valueToFixed(avgSlidersPerDiff)],
        circles: [totalCircles, valueToFixed(avgCirclesPerMapset), valueToFixed(avgCirclesPerDiff)],
        difficulties: [
            valueToFixed(setsToDifficulties),
            totalEasies,
            totalMediums,
            totalHards,
            totalInsanes,
            totalExtras,
            totalExpertPlus,
        ],
        submitDates: years,
        starRatings: [valueToFixed(avgSR), ...SR],
        approachRate: AR,
        overallDifficulty: OD,
        hpDrain: HP,
    };

    if (modeId === ModeDivisionType.fruits || ModeDivisionType.standard)
        statistics.circleSize = CS;
    else if (modeId === ModeDivisionType.mania)
        statistics.keys = CS;

    ctx.body = statistics;
});

statisticsRouter.get("/mappers", async (ctx) => {
    if (await ctx.cashed())
        return;

    const year = parseInt(parseQueryParam(ctx.query.year) || "") || new Date().getUTCFullYear();
    const modeString: string = parseQueryParam(ctx.query.mode) || "standard";
    const modeId = ModeDivisionType[modeString];

    const [yearQ, newyearQ, mapsQ]: [Promise<any>[], Promise<any>[], Promise<any>[]] = [[], [], []];
    for (let i = 0; i < yearIDthresholds.length; i++) {
        if (i + 2007 > year)
            break;

        yearQ.push(createUserQuery(year, modeId, i)
            .select("count(distinct user.osuUserid)", "value")
            .addSelect(`'${i + 2007} Users Ranking Sets'`, "constraint")
            .getRawOne()
        );

        newyearQ.push(createUserQuery(year, modeId, i)
            .andWhere(() => {
                const subQuery = Beatmapset
                    .createQueryBuilder("beatmapset")
                    .where(`year(beatmapset.approvedDate) < ${year}`)
                    .select("beatmapset.creatorID");
                return "user.ID not in (" + subQuery.getQuery() + ")";
            })
            .select("count(distinct user.osuUserid)", "value")
            .addSelect(`'${i + 2007} Users Ranking First Set'`, "constraint")
            .getRawOne()
        );

        // Maps Ranked by <YEAR> Users
        mapsQ.push(createUserQuery(year, modeId, i)
            .select("count(distinct beatmapset.ID)", "value")
            .addSelect(`'Maps Ranked by ${i + 2007} Users'`, "constraint")
            .getRawOne()
        );
    }

    const [
        uniqueMappers,
        newMappers,

        years,
        newYears,

        mapYears,
    ] = await Promise.all([
        Beatmapset
            .queryStatistic(year, modeId)
            .select("count(distinct creator.id)", "value")
            .addSelect("'Total Mappers Ranking Sets'", "constraint")
            .getRawOne(),

        Beatmapset
            .queryStatistic(year, modeId)
            .andWhere(qb => {
                let subQuery = qb
                    .subQuery()
                    .from(Beatmapset, "sub");
                subQuery = Beatmapset
                    .createQueryBuilder("beatmapset")
                    .where(`year(beatmapset.approvedDate) < ${year}`)
                    .select("beatmapset.creatorID");
                return "creator.ID not in (" + subQuery.getQuery() + ")";
            })
            .select("count(distinct creator.id)", "value")
            .addSelect("'Total Mappers Ranking First Set'", "constraint")
            .getRawOne(),

        Promise.all(yearQ),
        Promise.all(newyearQ),

        Promise.all(mapsQ),
    ]);

    const statistics: Record<string, Statistic[]> = {
        mappers: [
            uniqueMappers,
            newMappers,
            {
                constraint: "Percent of Mappers Ranking First Set",
                value: (newMappers.value / uniqueMappers.value * 100).toFixed(2) + "%",
            },
        ],
        bng: [
            getHistoryStat(year, modeString, "bns", "joined"),
            getHistoryStat(year, modeString, "bns", "left"),
        ],
        nat: [
            getHistoryStat(year, modeString, "nat", "joined"),
            getHistoryStat(year, modeString, "nat", "left"),
        ],
        newMapperAges: newYears,
        mapperAges: years,
        mapsPerMapperAges: mapYears,
    };

    ctx.body = statistics;
});