Mongo aggregate command that calculates total cost on openai calls from all user’s suggestions, based on the model being used, as well as the total number of words generated, then sorts by cost.

users-by-cost.ts

await AiSuggestion.aggregate([
  {
    $group: {
      _id: "$user",
      models: {
        $push: {
          model: "$request.model",
          totalTokens: { $sum: "$response.usage.total_tokens" },
        },
      },
    },
  },
  {
    $unwind: "$models",
  },
  {
    $group: {
      _id: { user: "$_id", model: "$models.model" },
      totalTokens: { $sum: "$models.totalTokens" },
      choices: { $push: "$response.choices" },
    },
  },
  {
    $group: {
      _id: "$_id.user",
      models: {
        $push: {
          model: "$_id.model",
          totalTokens: "$totalTokens",
          cost: {
            $multiply: [
              "$totalTokens",
              {
                $cond: {
                  if: { $eq: ["$_id.model", MODELS.davinci] },
                  then: COSTS[MODELS.davinci] / 1000,
                  else: {
                    $cond: {
                      if: { $eq: ["$_id.model", MODELS.babbage] },
                      then: COSTS[MODELS.babbage] / 1000,
                      else: {
                        $cond: {
                          if: { $eq: ["$_id.model", MODELS.curie] },
                          then: COSTS[MODELS.curie] / 1000,
                          else: 0,
                        },
                      },
                    },
                  },
                },
              },
            ],
          },
          wordCount: {
            $sum: {
              $map: {
                input: "$choices",
                as: "choice",
                in: { $size: { $split: ["$$choice.text", " "] } },
              },
            },
          },
        },
      },
    },
  },
  {
    $addFields: {
      totalCost: { $sum: "$models.cost" },
    },
  },
  {
    $sort: {
      totalCost: -1,
    },
  },
  {
    $lookup: {
      from: "users",
      localField: "_id",
      foreignField: "_id",
      as: "user",
    },
  },
  {
    $unwind: "$user",
  },
  {
    $addFields: {
      name: "$user.name",
      email: "$user.email",
    },
  },
  {
    $project: {
      user: 0,
    },
  },
]);